Importing CSV with commas in string values

Question

I am trying to import a trivial CSV to Postgres 8.4 database:

Here is a table:

CREATE TABLE public.sample (
  a VARCHAR, 
  b VARCHAR
) WITHOUT OIDS;

Here is a CSV file sample:

"foo","bar, baz"

The query:

COPY sample FROM '/tmp/sample.csv' USING DELIMITERS ',';

Throws an exception:

ERROR:  extra data after last expected column
CONTEXT:  COPY sample, line 1: ""foo","bar, baz""

But, well, CSV parsing is not rocket science and I wonder if it is not possible to solve without reformatting the source CSV file.

Input comes from a 3rd party, I cannot change the format.
(I understand I could pre-process to change the delimiter before I import it.)

Final solution:

COPY sample FROM '/tmp/sample.csv' WITH DELIMITER ',' CSV;

Originally taken from https://stackoverflow.com/a/9682174/251311, and documentation page is http://www.postgresql.org/docs/8.4/static/sql-copy.html

Erwin Brandstetter · Accepted Answer · 2021-03-26 01:10:00Z

8

Clearly, you have a CSV file while you try to import it as text format. For Postgres 9.1 or newer, use:

COPY sample FROM '/tmp/sample.csv' (FORMAT csv);

The default delimiter for CSV format is the comma (,) anyway. More in the manual.
For PostgreSQL 8.4 or older:

COPY sample FROM '/tmp/sample.csv' CSV;

edited Mar 26, 2021 at 1:10

answered Apr 3, 2012 at 21:41

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

zerkms Over a year ago

For 8.4 the syntax is slightly different though postgresql.org/docs/8.4/static/sql-copy.html

Erwin Brandstetter Over a year ago

@zerkms: Right, the syntax is for the current version 9.1. I added the 8.4 variant for documentation.

PJATX Over a year ago

Just a quick comment for those who are now on version 10.x - syntax now is more like COPY sample FROM '/tmp/sample.csv' WITH FORMAT (CSV, DELIMITER ',', NULL ' ');

Collectives™ on Stack Overflow

Importing CSV with commas in string values

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related