Postgres Pro
Downloads
Home   >   mailing lists

Re: Importing CSV File - Mailing list pgsql-general

From Melvin Davidson
Subject Re: Importing CSV File
Date
Msg-id CANu8Fixew1K4hfxCZRUKUT3Ec27cec__ZqJ8=TQG2wUHrEB6pw@mail.gmail.com
Whole thread Raw
In response to Re: Importing CSV File  (Rob Sargent <robjsargent@gmail.com>)
Responses Re: Importing CSV File
Re: Importing CSV File
List pgsql-general
Tree view
The trick is to tell postgres where the data ends with \.

From http://www.postgresql.org/docs/9.3/interactive/sql-copy.html

End of data can be represented by a single line containing just backslash-period (\.). An end-of-data marker is not necessary when reading from a file, since the end of file serves perfectly well; it is needed only when copying data to or from client applications using pre-3.0 client protocol.

So if you can somehow add the \. to the end of your data before the extraneous \r\n 's , it will solve your problem.

The following works as a test:

CREATE TABLE junk
(
pkey  integer,
jdata varchar(10),
CONSTRAINT junk_pk PRIMARY KEY (pkey)
);

TRUNCATE TABLE junk;
COPY junk FROM '/tmp/junk.data' WITH CSV;

contents of /tmp/junk.data:
-------------------------------------------------------------------
1,'junk1'
2,'junk1'
3,'junk1'
\.
garbage data1
more garbage
....
blah
enough alread


 

On Tue, Oct 27, 2015 at 12:23 PM, Rob Sargent <robjsargent@gmail.com> wrote:
On 10/27/2015 10:04 AM, Adrian Klaver wrote:
On 10/27/2015 08:44 AM, Jeff Janes wrote:
On Mon, Oct 26, 2015 at 2:45 PM, David Blomstrom
<david.blomstrom@gmail.com <mailto:david.blomstrom@gmail.com>> wrote:

    I tried to import a CSV file into a PostgreSQL table using pgAdmin
    III. I got an error message: "extra data after last column."


    All my spreadsheets have an "end of data" column that has /r/n in
    each cell. When I import a CSV file into a MySQL table, everything
    beyond /r/n is ignored. Is there some way to tell PostgreSQL to stop
    at /r/n?


How does it know when to stop ignoring and start the next record?

I wondered about that also. I did find this:

http://dev.mysql.com/doc/refman/5.7/en/load-data.html

LINES TERMINATED BY


You could write a little awk or perl script to give the PROGRAM option
of copy, but you can't do that within pgAdmin.

Cheers,

Jeff


Whence the csv file?  If it starts out in spreadsheet, can you not export only the columns you want in the database?

Google "postgres import tool" finds several options





--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

pgsql-general by date:

Previous
From: Rob Sargent
Date:
Subject: Re: Importing CSV File
Next
From: "David G. Johnston"
Date:
Subject: Re: Importing CSV File