Importing CSV File

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.

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

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

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

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

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

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.

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.

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

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

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

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

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>
<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.

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.​

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

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