postgresqlcsvcopy

unterminated CSV quoted field in Postgres

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

Solution

  • 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.