The trick is to tell postgres where the data ends with \.
From http://www.postgresql.org/docs/9.3/interactive/sql-copy.htmlEnd 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.
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;
-------------------------------------------------------------------
1,'junk1'
2,'junk1'
3,'junk1'
\.
garbage data1
....
On Tue, Oct 27, 2015 at 12:23 PM, Rob Sargent <robjsargent@xxxxxxxxx> wrote:
On 10/27/2015 10:04 AM, Adrian Klaver wrote:
On 10/27/2015 08:44 AM, Jeff Janes wrote:Whence the csv file? If it starts out in spreadsheet, can you not export only the columns you want in the database?
On Mon, Oct 26, 2015 at 2:45 PM, David Blomstrom
<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
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.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
