CODEWITHSUNDEEP
postgresql
grautur
grautur

Reputation: 30485

Ignore quotation marks when importing a CSV file into PostgreSQL?

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.

Upvotes: 14

Views: 20416

Answers (4)

AnushaSP07
AnushaSP07

Reputation: 401

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);

Upvotes: 0

andilabs
andilabs

Reputation: 23281

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

Upvotes: 3

wildplasser
wildplasser

Reputation: 44250

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.

Upvotes: 7

daxelrod
daxelrod

Reputation: 2589

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.

Upvotes: 11

Related Questions