Re: CR/LF conversion (was: 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

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

Oliver Vecernik <vecernik(at)aon(dot)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(at)aon(dot)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

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(at)postgresql(dot)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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)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(at)postgresql(dot)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(at)matrix(dot)gatewaynet(dot)com
> mantzios(at)softlab(dot)ece(dot)ntua(dot)gr
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

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(at)postgresql(dot)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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

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(at)aon(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073