Thread: Optional Exporting or copying result with (,) as decimal mark
Hi,
in pgsql it's not possible to define, that numeric values are returned in localized numeric format. In our case (Austria) we use comma as decimal mark and not period.
When opening a pgadmin-exported CSV-File, we have troubles in Excel, because it doesn't recognize numeric values properly. We would have to change the locale settings in excel so that this works properly.
This patch enables an option in the query tool tab, where you can decide if you like to use comma as decimal mark. It only applies on numeric values.
It also works, if you copy and paste from the result grid into excel.
I've done this work to make my people's life at work easier. If it's not suitable for a release, please ignore it. In this case I'm going to build "our own" version of pgadmin on each release.
Regards,
Jasmin
Attachment
On Thu, Jan 6, 2011 at 11:43 PM, Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> wrote: > Hi, > in pgsql it's not possible to define, that numeric values are returned in > localized numeric format. In our case (Austria) we use comma as decimal mark > and not period. > When opening a pgadmin-exported CSV-File, we have troubles in Excel, because > it doesn't recognize numeric values properly. We would have to change the > locale settings in excel so that this works properly. > This patch enables an option in the query tool tab, where you can decide if > you like to use comma as decimal mark. It only applies on numeric values. > It also works, if you copy and paste from the result grid into excel. > I've done this work to make my people's life at work easier. If it's not > suitable for a release, please ignore it. In this case I'm going to build > "our own" version of pgadmin on each release. It seems to me that the correct way to deal with this is to display the numerics in a locale-aware fashion, and then copy/paste and export etc. should just work, *and* the display will be properly localised. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
This is absolutely the correct way, but I think pgAdmin is the wrong location to handle this. In my opinion the database should handle locales. The COPY command for example also doesn't support other decimal marks. AND it's not always wanted, that the input is locale aware. There has to be an optional parameter or a session based setting. But that's a topic for an other mailing list ;)
I know my patch is just a workaround, but it saves us a lot of time in this particular process.
2011/1/7 Dave Page <dpage@pgadmin.org>
It seems to me that the correct way to deal with this is to displayOn Thu, Jan 6, 2011 at 11:43 PM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
> Hi,
> in pgsql it's not possible to define, that numeric values are returned in
> localized numeric format. In our case (Austria) we use comma as decimal mark
> and not period.
> When opening a pgadmin-exported CSV-File, we have troubles in Excel, because
> it doesn't recognize numeric values properly. We would have to change the
> locale settings in excel so that this works properly.
> This patch enables an option in the query tool tab, where you can decide if
> you like to use comma as decimal mark. It only applies on numeric values.
> It also works, if you copy and paste from the result grid into excel.
> I've done this work to make my people's life at work easier. If it's not
> suitable for a release, please ignore it. In this case I'm going to build
> "our own" version of pgadmin on each release.
the numerics in a locale-aware fashion, and then copy/paste and export
etc. should just work, *and* the display will be properly localised.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Fri, Jan 7, 2011 at 11:44 AM, Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> wrote: > This is absolutely the correct way, but I think pgAdmin is the wrong > location to handle this. In my opinion the database should handle locales. > The COPY command for example also doesn't support other decimal marks. AND > it's not always wanted, that the input is locale aware. There has to be an > optional parameter or a session based setting. But that's a topic for an > other mailing list ;) > I know my patch is just a workaround, but it saves us a lot of time in this > particular process. locale is always handled in the client - especially as data may be transferred to/from the server in binary formats where locale is irrelevant. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company