How to escape commas inside CSV values when importing table to PostgreSQL?

Question

I am trying to use the 'copy' command for importing the csv file into my postgres table. Values containing commas have been surrounded by double quotes but I can't seem to find an option to load them into the postgres table without any errors using the 'copy' command.

'COPY' command I am using:

CREATE TABLE candidates (Sno int, name varchar, cases int, case_details varchar, .....);
copy candidates from 'something.csv' with NULL AS ' ' csv ;

Example of an offending csv line:

1, "some name", 2, "(1):IPC Sections -  147, 323, 352, 504, 506 , Other Details - Case no.283A/2000, A.C.J.M-5, Ghumangunj Ellahabad, UP, Dt.12.11.2000", .....

Above the case_details attribute has commas in its value. That is my problem.

any chance your CSV was generated by a Microsoft product? in that case, it may have weird quoting - stackoverflow.com/questions/14573623/… - if so, you can use the ruby method provided to convert to a "normal" CSV and load with postgresql's COPY — Seamus Abshere
@SeamusAbshere No. My csv file has values with commas in it. Please see the updated question details for more info — kb_14
Postgres as I am writing in 2019 should work with comma inside double quotes without any problem. I encountered the same error message while loading a large table with 64 columns, it turned out to be my own problem as always (programmers trying to figure what else was wrong). I left out one column in the table definition. We programmers think we are smart actually we have difficulties counting to number 64. — Kemin Zhou

sega_sai sega_sai · Accepted Answer · 2013-03-19T18:35:56

Works for me (PG 9.2, linux):

$ cat something.csv 
1, "some name", 2, "(1):IPC Sections -  147, 323, 352, 504, 506 , Other Details - Case no.283A/2000, A.C.J.M-5, Ghumangunj Ellahabad, UP, Dt.12.11.2000"

$ psql test
test=> CREATE TABLE candidates (Sno int, name varchar, cases int, case_details varchar);
CREATE TABLE
test=> \copy candidates from 'something.csv' with NULL AS ' ' csv ;
test=> select * from candidates ;
 sno |    name    | cases |                                                             case_details                                                             
-----+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------
   1 |  some name |     2 |  (1):IPC Sections -  147, 323, 352, 504, 506 , Other Details - Case no.283A/2000, A.C.J.M-5, Ghumangunj Ellahabad, UP, Dt.12.11.2000
(1 row)

How to escape commas inside CSV values when importing table to PostgreSQL?

3 Answers