Hi!

I want to import some data from an ascii file using the COPY sql-command.
Unfortunatly the decimal numbers are given in german format, meaning the
decimal point is replaced by a comma (, instead of .). Is there any
possiblility
to switch the clients behaviourr (like 'set datestyle') for
interpretation of decimals?

Thanks!

best regards,
Christian Dittmer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

am Wed, dem 07.05.2008, um 14:25:13 +0200 mailte Jan Christian Dittmer folgendes:
> Hi!
>
> I want to import some data from an ascii file using the COPY sql-command.
> Unfortunatly the decimal numbers are given in german format, meaning the
> decimal point is replaced by a comma (, instead of .). Is there any
> possiblility
> to switch the clients behaviourr (like 'set datestyle') for
> interpretation of decimals?

Don't know, but you can replace the , to . within the ascii-file (sed,
awk, ...).


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I would replace the ',' with something else such as a '#' first then
replace the decimal with the ',' then replace the '#' with a decimal '.'

If you do the ',' with a '.' first then all of them will be '.' and you
wont know which ones to change.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailtogsql-general-owner@postgresql.org] On Behalf Of A. Kretschmer
Sent: Wednesday, May 07, 2008 9:38 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Import German Decimal Numbers

am Wed, dem 07.05.2008, um 14:25:13 +0200 mailte Jan Christian Dittmer
folgendes:
> Hi!
>
> I want to import some data from an ascii file using the COPY
sql-command.
> Unfortunatly the decimal numbers are given in german format, meaning
the
> decimal point is replaced by a comma (, instead of .). Is there any
> possiblility
> to switch the clients behaviourr (like 'set datestyle') for
> interpretation of decimals?

Don't know, but you can replace the , to . within the ascii-file (sed,
awk, ...).


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
This message has been scanned by MailScanner


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you very much!
You have remind me that the our server runs under Linux and not under
Windows as our clients :-)
So indeed I can use a sed-pipe construct to switch '.' and ','.
But wait, there is just another problem then. Our date format is also
german :-( "DD.MM.YY" or
"DD.MM.YYYY". So if I just exchange '.' and ',' the date will be
unreadable for the import :-(
The (current) file is 1.4 GB so it will take ages to let awk chew on it
I guess.

Christian

Ken Allen wrote:
> I would replace the ',' with something else such as a '#' first then
> replace the decimal with the ',' then replace the '#' with a decimal '.'
>
> If you do the ',' with a '.' first then all of them will be '.' and you
> wont know which ones to change.
>

> Don't know, but you can replace the , to . within the ascii-file (sed,
> awk, ...).
>
>
> Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Well if your doing an update, do it column by column and when you do a
date column replace the '.' with '/'

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailtogsql-general-owner@postgresql.org] On Behalf Of Jan Christian
Dittmer
Sent: Wednesday, May 07, 2008 10:16 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Import German Decimal Numbers

Thank you very much!
You have remind me that the our server runs under Linux and not under
Windows as our clients :-)
So indeed I can use a sed-pipe construct to switch '.' and ','.
But wait, there is just another problem then. Our date format is also
german :-( "DD.MM.YY" or
"DD.MM.YYYY". So if I just exchange '.' and ',' the date will be
unreadable for the import :-(
The (current) file is 1.4 GB so it will take ages to let awk chew on it
I guess.

Christian

Ken Allen wrote:
> I would replace the ',' with something else such as a '#' first then
> replace the decimal with the ',' then replace the '#' with a decimal
'.'
>
> If you do the ',' with a '.' first then all of them will be '.' and
you
> wont know which ones to change.
>

> Don't know, but you can replace the , to . within the ascii-file (sed,
> awk, ...).
>
>
> Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
This message has been scanned by MailScanner


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mittwoch, 7. Mai 2008, Jan Christian Dittmer wrote:
| The (current) file is 1.4 GB so it will take ages to let awk chew on it
| I guess.

If you think awk is a bottleneck, I'd recommend using perl instead. It's waaay
faster and should process your file within minutes if not faster.

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

Le 7 mai 08 à 15:57, Tino Wildenhain a écrit :
> There is also http://pgfoundry.org/projects/pgloader/
> and if not already implemented it should be fairly
> easy to implement a data filter within this one.

pgloader indeed support user reformating modules, and comes with a
mysql to pgsql timestamp reformater. Adding a python .py module
containing one function to handle the change should be easy, the
documentation has needed details if you look for "reformat_path" and
"reformat" options.
http://pgloader.projects.postgresql.org/

Plus, pgloader supports setting the DateStyle before running copy,
maybe this will be enough in your case? (didn't read all the thread).

If you need more help than current documentation to setup your
reformating module, please just ask!

Regards,
--
dim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general