Postgres Pro
Downloads
Home   >   mailing lists

Thread: Importing CSV File

Importing CSV File

From
David Blomstrom
Date:
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?

Thanks.

Re: Importing CSV File

From
Adrian Klaver
Date:
On 10/26/2015 02:45 PM, David Blomstrom 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?

Is it really /r/n not \r\n?

>
> Thanks.


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Importing CSV File

From
Gavin Flower
Date:
On 27/10/15 10:45, David Blomstrom 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?
>
> Thanks.
You can import the CSV into a spreadsheet (such as LibreOffice Calc),
and manually delete the extraneous lines.

Re: Importing CSV File

From
Adrian Klaver
Date:
On 10/26/2015 02:45 PM, David Blomstrom 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?

No Postgres will not stop at /r/n. The specifics of what goes on can be
found here:

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

Short version, you can import a CSV file with fewer fields then the
table you are importing to but not the other way around. There has to be
  a place for all the CSV fields in the table.

>
> Thanks.


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Importing CSV File

From
David Blomstrom
Date:
Wow, I must be getting dyxlexic; yes, it is 

\r\n


I guess it would be easy enough to just copy the stuff I want to import into a separate spreadsheet and save it as a CSV. I've done that before with really big spreadsheets, actually. Thanks.

Re: Importing CSV File

From
Rajeev Bhatta
Date:
You could do it simpler by creating a temp table with all columns in the CSV and then doing either creating another table of the temp table or building a view on top of the table.

The decision depends on how frequently you would be importing data from the CSV. If only one time then you are better off doing it through a spreadsheet.

Thanks
Rajeev

On October 27, 2015 4:49:22 AM GMT+05:30, David Blomstrom <david.blomstrom@gmail.com> wrote:
Wow, I must be getting dyxlexic; yes, it is 

\r\n


I guess it would be easy enough to just copy the stuff I want to import into a separate spreadsheet and save it as a CSV. I've done that before with really big spreadsheets, actually. Thanks.

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: Importing CSV File

From
Jeff Janes
Date:
On Mon, Oct 26, 2015 at 2:45 PM, David Blomstrom <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?

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

Re: Importing CSV File

From
Adrian Klaver
Date:
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


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Importing CSV File

From
Rob Sargent
Date:
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


Re: Importing CSV File

From
Melvin Davidson
Date:
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.

Re: Importing CSV File

From
"David G. Johnston"
Date:
On Tue, Oct 27, 2015 at 12:36 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

contents of /tmp/junk.data:
-------------------------------------------------------------------
1,'junk1'
2,'junk1'
3,'junk1'

​Good info but I resonably certain the format in question is more like:

--------------------
1,'junk1',\r\n,'blah','blah2'​
2,'junk2',\r\n,'blah'
3,'junk3',\r\n,'blah','blah2','blah3'

​and the OP wants to only import the common columns while ignoring the fact that extra columns exist.

I guess line 4 could read:

4,'junk4','important',\r\n,'blah','blah2'

so that even within the main structure there would be a variable number of columns...

David J.​

Re: Importing CSV File

From
Adrian Klaver
Date:
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@gmail.com
> <mailto: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>
>>>     <mailto: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.


--
Adrian Klaver
adrian.klaver@aklaver.com