Re: Importing CSV File

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Tue, 27 Oct 2015 09:47:50 -0700

On 10/27/2015 09:36 AM, Melvin Davidson wrote:
The trick is to tell postgres where the data ends with \.

That is for all the data. What the OP was referring to was designating 
where each row in the data ended.

 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@xxxxxxxxx
<mailto:robjsargent@xxxxxxxxx>> 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@xxxxxxxxx <mailto:david.blomstrom@xxxxxxxxx>
    <mailto:david.blomstrom@xxxxxxxxx>
    <mailto:david.blomstrom@xxxxxxxxx>> 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.

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general