Importing CSV and commas in string values

Go To StackoverFlow.com

3

I use postgres 8.4 and currently trying to import a trivial CSV:

Here is a table:

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

and here is a csv file sample:

"foo","bar, baz"

The query

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

expected throws

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

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

Any ideas?

PS: the csv comes from 3rd party, I cannot change the format.

PPS: yes, I understand I could pre-process it (change the delimiter manually) 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

2012-04-03 21:26
by zerkms


4

Clearly you have a CSV file while you try to import it as text format. Use:

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

The default delimiter for CSV format is the comma (,) anyway. More in the manual. Syntax is for current version 9.1. For PostgreSQL 8.4:

COPY sample FROM '/tmp/sample.csv' CSV;
2012-04-03 21:41
by Erwin Brandstetter
For 8.4 the syntax is slightly different though http://www.postgresql.org/docs/8.4/static/sql-copy.htm - zerkms 2012-04-03 21:43
@zerkms: Right, the syntax is for the current version 9.1. I added the 8.4 variant for documentation - Erwin Brandstetter 2012-04-03 21:46
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 ' ');PJATX 2018-01-29 23:34