Ignore quotation marks when importing a CSV file into PostgreSQL?

Question

I'm trying to import a tab-delimited file into my PostgreSQL database. One of the fields in my file is a "title" field, which occasionally contains actual quotation marks. For example, my tsv might look like:

id    title
5     Hello/Bleah" Foo

(Yeah, there's just that one quotation mark in the title.)

When I try importing the file into my database:

copy articles from 'articles.tsv' with delimiter E'\t' csv header;

I get this error, referencing that line:

ERROR:  unterminated CSV quoted field

How do I fix this? Quotation marks are never used to surround entire fields in the file. I tried copy articles from 'articles.tsv' with delimiter E'\t' escape E'\\' csv header; but I get the same error on the same line.

daxelrod · Accepted Answer · 2011-09-11 04:45:30Z

11

Assuming the file never actually tries to quote its fields:

The option you want is "with quote", see http://www.postgresql.org/docs/8.2/static/sql-copy.html

Unfortunately, I'm not sure how to turn off quote processing altogether, one kludge would be to specify a character that does not appear in your file at all.

answered Sep 11, 2011 at 4:45

daxelrod

2,5991 gold badge22 silver badges31 bronze badges

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

3 Comments

grautur Over a year ago

Cool, thanks! I randomly tried quote E'\b', which ended up working.

Ian Warburton Over a year ago

@grautur What does E'\b' mean? Backspace? what about the 'E'? "escape string constant" - got it.

Rodrigo Over a year ago

@IanWarburton Yes, it seems it's a backspace: en.wikipedia.org/wiki/%5Cb

wildplasser · Accepted Answer · 2011-09-11 11:08:32Z

7

Tab separated is the default format for copy statements. Treating them as CSV is just silly. (do you take this path just to skip the header ?)

copy articles from 'articles.tsv';

does exactly what you want.

answered Sep 11, 2011 at 11:08

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

2 Comments

Tom De Leu Over a year ago

Sounds simple enough, but what if the CSV data contains backslashes as valid field content? (yes, I've encountered this) The default format for COPY treats a backslash as an escape char, so then you HAVE to use COPY with CSV format...

wildplasser Over a year ago

I had not thought about that one. Yes: it makes sense.

andilabs · Accepted Answer · 2020-01-30 11:47:02Z

3

I struggled with the same error and a few more. Finally gathering knowledge from few SO questions I came up with the following setup for making COPY TO/FROM successful even for quite sophisticated JSON columns:

COPY "your_schema_name.yor_table_name" (your, column_names, here) 
FROM STDIN WITH CSV DELIMITER E'\t' QUOTE '\b' ESCAPE '\';
--here rows data
\.

the most important parts:

QUOTE '\b' - quote with backspace (thanks a lot @grautur!)
DELIMITER E'\t' - delimiter with tabs
ESCAPE '\' - and escape with a backslash

answered Jan 30, 2020 at 11:47

andilabs

23.5k14 gold badges126 silver badges159 bronze badges

Comments

AnushaSP07 · Accepted Answer · 2023-12-05 03:37:56Z

0

To copy from CSV file to PostgreSQL table with headers in CSV file using query:

First Add all the files in C:/temp folder

Then write the below scripts which accepts both NULL values as well as EMPTY strings

 copy PUBLIC."TABLE_NAME" FROM 
'C:\tmp\TABLE_NAME.CSV' 
(format csv, null "NULL", DELIMITER ',', HEADER);

answered Dec 5, 2023 at 3:37

AnushaSP07

4311 gold badge7 silver badges10 bronze badges

Collectives™ on Stack Overflow

Ignore quotation marks when importing a CSV file into PostgreSQL?

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related