unterminated CSV quoted field in Postgres

Question

I'm trying to insert some data into my table using the copy command :

copy otype_cstore from '/tmp/otype_fdw.csv' delimiter ';' quote '"' csv;

And I have this answer :

ERROR: unterminated CSV quoted field

There is the line in my CSV file where I have the problem :

533696;PoG;-251658240;from id GSW C";

This is the only line a double quote and I can't remove it, so do you have some advice for me ?

Thank you in advance

two choices: either you escape the quote or you simply remove it from that line. — Arkhena
– Arkhena, Commented May 22, 2017 at 8:46
I can't remove from my line. What do you mean by esacpe the quot ? — Alexandre S.
– Alexandre S., Commented May 22, 2017 at 8:48
escape the quote would be to change the value to "from id GSW C""". Alternatively, you could use a different quoting character (if that is an option). The CSV file is syntactically incorrect, you'll have to fix it in some fashion. — Laurenz Albe
– Laurenz Albe, Commented May 22, 2017 at 8:59

Danny_ds · Accepted Answer · 2021-08-04 17:12:39Z

21

If you have lines like this in your csv:

533696;PoG;-251658240;from id GSW C";

this actually means/shows the fields are not quoted, which is still perfectly valid csv as long as there are no separators inside the fields.

In this case the parser should be told the fields are not quoted.

So, instead of using quote '"' (which is actually telling the parser the fields are quoted and why you get the error), you should use something like quote 'none', or leave the quote parameter out (I don't know Postgres, so I can't give you the exact option to do this).

Ok, I did a quick lookup of the parameters. It looks like there is not really an option to turn quoting off. The only option left would be to provide a quote character that is never used in the data.

quote E'\b' (backspace) seems to work ok.

edited Aug 4, 2021 at 17:12

answered May 22, 2017 at 19:47

Danny_ds

11.4k1 gold badge26 silver badges48 bronze badges

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

Comments

Rakesh · Accepted Answer · 2021-03-17 14:52:06Z

2

Bit late to reply, but I was facing same issue and found that for double quote we need to add one more double quote to escape with along with prefix and suffix double quote to that special characater. for your case it would be

input data is : from id GSW C"

change data to : from id GSW C""""

note there are 4 consecutive double quotes. first and last double quote is prefix and postfix as per documentation. middle 2 double quotes is data with one escape double quote. Hope this helps for readers with similar issue going forward.

So for every double quote in data it needs to be escaped with one escape character (default double quote). This is as per documentation.

edited Mar 17, 2021 at 14:52

answered Mar 17, 2021 at 12:18

Rakesh

315 bronze badges

Comments

Daney CM · Accepted Answer · 2024-10-21 16:58:25Z

0

Import the csv file using the Import/Export data option, Select the file you want to Import Go to Options, scroll down to "Escape" select double quotes instead of single quotes "

answered Oct 21, 2024 at 16:58

Daney CM

11 bronze badge

Collectives™ on Stack Overflow

unterminated CSV quoted field in Postgres

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related