Thread: import error

import error

From
Oliver Vecernik
Date:
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




Re: import error

From
Achilleus Mantzios
Date:
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



Re: import error

From
Achilleus Mantzios
Date:
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



Re: CR/LF conversion (was: import error)

From
Oliver Vecernik
Date:
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





Re: CR/LF conversion (was: import error)

From
Achilleus Mantzios
Date:
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



Re: import error

From
Tom Lane
Date:
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


Re: CR/LF conversion (was: import error)

From
Tom Lane
Date:
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


Re: CR/LF conversion (was: import error)

From
Bruce Momjian
Date:
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