Thread: import error
Hi all! I'm trying to import a text file from Suunto Dive Manager. I've got following table structure: CREATE TABLE dives ( id int, dive_number int, dive_date date, time_of_day time, series int, dc_dive_number int, dive_time int, surface_interval int, max_depth float, mean_depth float, dc_type int, dc_serial_number int, dc_personal_data text, dc_sample_rate int, dc_altitude_mode int, dc_personal_mode int, solution_time_adj int, modified int, location text, site text, weather text, water_visibility text, air_temp int, water_temp int, water_temp_at_end int, partner text, dive_master text, boat_name text, cylinder_desc text, cylinder_size float, cylinder_units_code int, cylinder_work_pressure float, cylinder_start_pressure float, cylinder_end_pressure int, sac_rate float, sac_units int, user_field_1 text, user_field_2 text, user_field_3 text, user_field_4 text, user_field_5 text, weight int, oxygen_percent int, olf_percent int, otu_flag int ); Data is structured as follows: 1,1,"07.08.2001","11:35",1,1,2220,0,6.0,0.0,12,0,"",20,0,0,0,1,"Sarigerme","Asche Bucht","wolkenlos",">20m",35,0,30,"","Mehmet Semerkant","","",0.00000,1,0.0000,0.0000,0,0.0000,0,"","","","","",12,0,0,0 My import gives following error: sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; ": can't parse "ne 1, pg_atoi: error in "0 My first thought was that the date and time is wrong (because it's the only "0), but even after removing it for a test results in the same error. How can I find out, which field exactly causes this error? My PostgreSQL version is (on Debian/Woody): sport=# select version(); version --------------------------------------------------------------- PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) Regards, Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik
On Tue, 28 Jan 2003, Oliver Vecernik wrote: > > 1,1,"07.08.2001","11:35",1,1,2220,0,6.0,0.0,12,0,"",20,0,0,0,1,"Sarigerme","Asche > Bucht","wolkenlos",">20m",35,0,30,"","Mehmet > Semerkant","","",0.00000,1,0.0000,0.0000,0,0.0000,0,"","","","","",12,0,0,0 Why dont you get rid of "'s ????? e.g. 1,1,2001-08-07,11:35,1,1,...etc.. (Also see if there exists a DateStyle like yours 07.08.2001 in order to avoid the date format conversion) > > My import gives following error: > > sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; > ": can't parse "ne 1, pg_atoi: error in "0 > > My first thought was that the date and time is wrong (because it's the > only "0), but even after removing it for a test results in the same > error. How can I find out, which field exactly causes this error? > > My PostgreSQL version is (on Debian/Woody): > > sport=# select version(); > version > --------------------------------------------------------------- > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > (1 row) > > Regards, > Oliver > > -- > VECERNIK Datenerfassungssysteme > A-2560 Hernstein, Hofkogelgasse 17 > Tel.: +43 2633 47530, Fax: DW 50 > http://members.aon.at/vecernik > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
On Tue, 28 Jan 2003, Achilleus Mantzios wrote: > On Tue, 28 Jan 2003, Oliver Vecernik wrote: > > > > > 1,1,"07.08.2001","11:35",1,1,2220,0,6.0,0.0,12,0,"",20,0,0,0,1,"Sarigerme","Asche > > Bucht","wolkenlos",">20m",35,0,30,"","Mehmet > > Semerkant","","",0.00000,1,0.0000,0.0000,0,0.0000,0,"","","","","",12,0,0,0 > > Why dont you get rid of "'s ????? > e.g. > 1,1,2001-08-07,11:35,1,1,...etc.. > (Also see if there exists a DateStyle like yours 07.08.2001 > in order to avoid the date format conversion) I think German DateStyle will do the trick. > > > > > My import gives following error: > > > > sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; > > ": can't parse "ne 1, pg_atoi: error in "0 > > > > My first thought was that the date and time is wrong (because it's the > > only "0), but even after removing it for a test results in the same > > error. How can I find out, which field exactly causes this error? > > > > My PostgreSQL version is (on Debian/Woody): > > > > sport=# select version(); > > version > > --------------------------------------------------------------- > > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > > (1 row) > > > > Regards, > > Oliver > > > > -- > > VECERNIK Datenerfassungssysteme > > A-2560 Hernstein, Hofkogelgasse 17 > > Tel.: +43 2633 47530, Fax: DW 50 > > http://members.aon.at/vecernik > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > ================================================================== > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel: +30-10-8981112 > fax: +30-10-8981877 > email: achill@matrix.gatewaynet.com > mantzios@softlab.ece.ntua.gr > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
Oliver Vecernik schrieb: > Hi all! > > I'm trying to import a text file from Suunto Dive Manager. I've got > following table structure: Arghh ... It's always the same problem with CR/LF conversions ... After changing it to just LFs, everthing worked like a charm. Is there an elegant way to handle this automatically? Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik
On Tue, 28 Jan 2003, Oliver Vecernik wrote: > Oliver Vecernik schrieb: > > > Hi all! > > > > I'm trying to import a text file from Suunto Dive Manager. I've got > > following table structure: > > Arghh ... > > It's always the same problem with CR/LF conversions ... > > After changing it to just LFs, everthing worked like a charm. Is there > an elegant way to handle this automatically? If your dates were german in the first place, keep them german during the copy, otherwise you will have wrong dates. > > Oliver > > -- > VECERNIK Datenerfassungssysteme > A-2560 Hernstein, Hofkogelgasse 17 > Tel.: +43 2633 47530, Fax: DW 50 > http://members.aon.at/vecernik > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
Oliver Vecernik <vecernik@aon.at> writes:
> My import gives following error:
> sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ',';
> ": can't parse "ne 1, pg_atoi: error in "0
The weird formatting of the error message suggests that the problem
is DOS-style newlines (CR/LF). COPY only likes Unix-style newlines
(LF). It thinks the CRs are part of the data, and then the datatype
input routines (like pg_atoi) spit up. Since pg_atoi spits back the
text it couldn't parse (including the CR), you get this bizarre-looking
overprinted message.
Solution: save the import file with Unix newlines, or run it through
a newline-fixing filter.
regards, tom lane
Oliver Vecernik <vecernik@aon.at> writes:
> It's always the same problem with CR/LF conversions ...
Ah, you figured it out. (Didn't see your followup right away because of
the changed subject line.)
> After changing it to just LFs, everthing worked like a charm. Is there
> an elegant way to handle this automatically?
There are plans to make COPY treat all common styles of newline alike.
We had to wait a release or two after taking the first step, though,
since it's an incompatible change.
regards, tom lane
We are supporting Win32 in 7.4, so it is very likely COPY will support carriage returns the next release. --------------------------------------------------------------------------- Tom Lane wrote: > Oliver Vecernik <vecernik@aon.at> writes: > > It's always the same problem with CR/LF conversions ... > > Ah, you figured it out. (Didn't see your followup right away because of > the changed subject line.) > > > After changing it to just LFs, everthing worked like a charm. Is there > > an elegant way to handle this automatically? > > There are plans to make COPY treat all common styles of newline alike. > We had to wait a release or two after taking the first step, though, > since it's an incompatible change. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073