Обсуждение: using the "copy from" command

Hi,

I'm a first-time user of the "Copy ... From..." command, and I'm trying
to load a table from a text flat file.

http://www.postgresql.org/docs/7.4/static/sql-copy.html

I'm getting the following error.  Any help will be appreciated.  Thank
you.



[mknepper@barney datafiles]$ psql -U postgres medispan
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

medispan=# copy mmw_ade_com from
'/home/mknepper/medispan/datafiles/mmwadecom.txt' with delimiter '|';
": can't parse "ne 1, pg_atoi: error in "14608
medispan=#


Table schema:

CREATE TABLE mmw_ade_com
(
    gpi                         VARCHAR(14) NOT NULL,
    mcid                        INTEGER NOT NULL,
    restrictionid               INTEGER NOT NULL,
    sequencenumber              SMALLINT NOT NULL,
    textid                      INTEGER,
    PRIMARY KEY (gpi, mcid, restrictionid, sequencenumber)
);



example of data, from the TXT flat file, called mmwadecom.txt:

01100040100310|5|0|10|14608
01100040100310|8|0|10|17377
01100040100310|8|0|20|18061
01100040100310|8|0|30|14608
01100040100310|22|0|10|18025
01100040100310|30|0|10|14608
01100040100310|36|0|10|14608
01100040100310|115|0|10|13937
01100040100310|115|0|20|18041

Knepper, Michelle wrote:
> medispan=# copy mmw_ade_com from
> '/home/mknepper/medispan/datafiles/mmwadecom.txt' with delimiter '|';
> ": can't parse "ne 1, pg_atoi: error in "14608

Looks like bad data in line 14608. What does that line of your input
file look like?

Joe

Joe Conway wrote:
> Knepper, Michelle wrote:
>> medispan=# copy mmw_ade_com from
>> '/home/mknepper/medispan/datafiles/mmwadecom.txt' with delimiter '|';
>> ": can't parse "ne 1, pg_atoi: error in "14608
>
> Looks like bad data in line 14608. What does that line of your input
> file look like?

Strike that -- it actually is a problem in line 1, isn't it (you cut off
the line number in the error message above)? It might be end-of-line
character problem. Was your input file created or edited on Windows by
chance (i.e. ends in \r\n instead of \n)?

Joe

Thanks Joe!
I converted the text file to Unix, using EditPadPro, to get
rid of all the Windows characters.  Got rid of any \r and end-of-line
stuff.
And the copy command worked beautifully.  It entered all of the data
into the table.

Simple thing to do, but new to me.

Ciao. ;-)



-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Monday, March 01, 2004 2:28 PM
To: Knepper, Michelle
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] using the "copy from" command


Joe Conway wrote:
> Knepper, Michelle wrote:
>> medispan=# copy mmw_ade_com from
>> '/home/mknepper/medispan/datafiles/mmwadecom.txt' with delimiter '|';
>> ": can't parse "ne 1, pg_atoi: error in "14608
>
> Looks like bad data in line 14608. What does that line of your input
> file look like?

Strike that -- it actually is a problem in line 1, isn't it (you cut off

the line number in the error message above)? It might be end-of-line
character problem. Was your input file created or edited on Windows by
chance (i.e. ends in \r\n instead of \n)?

Joe

Yes, I tried using this:    tr -d '\r' < datafile
but it didn't get rid of all the other ascii stuff, of course, since I
only indicated '\r'.

Here's another linux command that I used to convert all of my files to
Unix,
instead of, converting them file by file, via EditPadPro:

  find . -name  "*.txt"  | xargs dos2unix

This converted all the .txt files, within the current directory.

Thanks.


-----Original Message-----
From: Karl O. Pinc [mailto:kop@meme.com]
Sent: Tuesday, March 02, 2004 12:44 PM
To: Knepper, Michelle
Subject: Re: [GENERAL] using the "copy from" command


FYI, you can feed the file through sed or tr.  The only wierd
thing is specifying a \r in shell.  I know that $'\r' will do
it in bash.  The commands may have an easier way...

On 2004.03.02 12:11 "Knepper, Michelle" wrote:
> Thanks Joe!
> I converted the text file to Unix, using EditPadPro, to get
> rid of all the Windows characters.  Got rid of any \r and end-of-line
> stuff.
> And the copy command worked beautifully.  It entered all of the data
> into the table.
>
> Simple thing to do, but new to me.
>
> Ciao. ;-)
>
>
>
> -----Original Message-----
> From: Joe Conway [mailto:mail@joeconway.com]
> Sent: Monday, March 01, 2004 2:28 PM
> To: Knepper, Michelle
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] using the "copy from" command
>
>
> Joe Conway wrote:
> > Knepper, Michelle wrote:
> >> medispan=# copy mmw_ade_com from
> >> '/home/mknepper/medispan/datafiles/mmwadecom.txt' with delimiter
> '|';
> >> ": can't parse "ne 1, pg_atoi: error in "14608
> >
> > Looks like bad data in line 14608. What does that line of your input
>
> > file look like?
>
> Strike that -- it actually is a problem in line 1, isn't it (you cut
> off
>
> the line number in the error message above)? It might be end-of-line
> character problem. Was your input file created or edited on Windows by
>
> chance (i.e. ends in \r\n instead of \n)?
>
> Joe
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein