pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedimport error
4+ messages / 3 participants
[nested] [flat]
* import error
@ 2003-01-28 13:26 Oliver Vecernik <vecernik@aon.at>
0 siblings, 2 replies; 4+ messages in thread
From: Oliver Vecernik @ 2003-01-28 13:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: import error
@ 2003-01-28 15:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oliver Vecernik <vecernik@aon.at>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-01-28 15:28 UTC (permalink / raw)
To: Oliver Vecernik <vecernik@aon.at>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: import error
@ 2003-01-28 17:43 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Oliver Vecernik <vecernik@aon.at>
1 sibling, 1 reply; 4+ messages in thread
From: Achilleus Mantzios @ 2003-01-28 17:43 UTC (permalink / raw)
To: Oliver Vecernik <vecernik@aon.at>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: import error
@ 2003-01-28 17:47 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Achilleus Mantzios @ 2003-01-28 17:47 UTC (permalink / raw)
To: Oliver Vecernik <vecernik@aon.at>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-01-28 17:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-28 13:26 import error Oliver Vecernik <vecernik@aon.at>
2003-01-28 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-28 17:43 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-01-28 17:47 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox