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 <robjsarg...@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.blomst...@gmail.com <mailto:david.blomst...@gmail.com> > <david.blomst...@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.
