Re: [GENERAL] Import German Decimal Numbers
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
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
Re: [GENERAL] Import German Decimal Numbers
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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
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.. 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
Re: [GENERAL] Import German Decimal Numbers
Well if your doing an update, do it column by column and when you do a date column replace the '.' with '/' -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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.. 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
Re: [GENERAL] Import German Decimal Numbers
Jan Christian Dittmer wrote: 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.. 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. If all fails you could copy into a another table where you use text columns and then translate afterwards via to_date() and to_number() / replace(). Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Import German Decimal Numbers
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 [EMAIL PROTECTED] 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
Re: [GENERAL] Import German Decimal Numbers
Thomas Pundt wrote: 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. isnt perl just another frontend for awk? ;)) SCNR ;) 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. Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Import German Decimal Numbers
Jan Christian Dittmer wrote: 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.. 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. If all fails you could copy into a another table where you use text columns and then translate afterwards via to_date() and to_number() / replace(). For my tuppence worth, this is the approach I take. Copy into a sandbox table first, as the only reliable thing about external input is it won't be as it's supposed to be. This way you can copy everything into varchar columns, then run any number of regexes over the data to ensure it's perfect, then do: insert into livetable select * from temptable (with the appropriate casts in place). Bit of a tangent, but Is there any possibility of SQL injection via data provided to copy? Will T - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Import German Decimal Numbers
William Temperley wrote: ... Bit of a tangent, but Is there any possibility of SQL injection via data provided to copy? depends on how you call COPY, but usually not :-) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
