Thread: COPY complaining about unquoted carriage return found in data... in a quoted field
COPY complaining about unquoted carriage return found in data... in a quoted field
From
Ivan Sergio Borgonovo
Date:
I'm on pg 8.3.14
I'm trying to import a csv with
\copy anagraficaclienti from
'myfile.csv'
delimiter as E' ' -- this is a tab \t
null as 'NULL'
csv
header
quote as E'"'
escape as E'\\'
What I get is
ERROR: unquoted carriage return found in data
HINT: Use quoted CSV field to represent carriage return.
CONTEXT: COPY anagraficaclienti, line 48656
The record actually contains \r\n in a field but up to my
understanding that field is quoted.
I think what seems to be wrong is my understanding of what postgres
consider quoted field.
the content in hex
09 22 56 | 69 61 20 46 | 61 65 6E 74 | 69 6E 61 2C
20 35 33 0D | 0A 22 09 22 | 35 30 30 31 | 34 22 09
\t"Via Faentina, 53\r\n"\t
What am I missing?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Re: COPY complaining about unquoted carriage return found in data... in a quoted field
From
Pavel Stehule
Date:
Hello COPY doesn't like '\n' too. Replace '\n' by '\\n' Regards Pavel Stehule 2011/5/10 Ivan Sergio Borgonovo <mail@webthatworks.it>: > I'm on pg 8.3.14 > I'm trying to import a csv with > > \copy anagraficaclienti from > 'myfile.csv' > delimiter as E' ' -- this is a tab \t > null as 'NULL' > csv > header > quote as E'"' > escape as E'\\' > > What I get is > > ERROR: unquoted carriage return found in data > HINT: Use quoted CSV field to represent carriage return. > CONTEXT: COPY anagraficaclienti, line 48656 > > The record actually contains \r\n in a field but up to my > understanding that field is quoted. > I think what seems to be wrong is my understanding of what postgres > consider quoted field. > > the content in hex > 09 22 56 | 69 61 20 46 | 61 65 6E 74 | 69 6E 61 2C > 20 35 33 0D | 0A 22 09 22 | 35 30 30 31 | 34 22 09 > > \t"Via Faentina, 53\r\n"\t > > What am I missing? > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: COPY complaining about unquoted carriage return found in data... in a quoted field
From
Ivan Sergio Borgonovo
Date:
On Tue, 10 May 2011 14:38:23 +0200 Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > COPY doesn't like '\n' too. > > Replace '\n' by '\\n' mmm maybe you were mislead by the "semi-transliterated" hexdump. There is no "slash" in the record, the actual input was the one reported in hex. The following line was just to help reading the hexdump. 09 22 56 | 69 61 20 46 | 61 65 6E 74 | 69 6E 61 2C 20 35 33 0D | 0A 22 09 22 | 35 30 30 31 | 34 22 09 as a quick reference: 0x09 = \t 0x0a = \n 0x0d = \r 0x20 = space 0x22 = " I thought that csv quote as '"' where there exactly to avoid escaping of delimiters (other than the quoting character itself) inside fields. From the docs: http://www.postgresql.org/docs/8.3/static/sql-copy.html «If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character.» So up to my understanding once I've "quoted" a field I've to take care of escaping just the quote and the escape characters and the field may contain delimiter characters (field separator and record separator) without escaping. I was wondering if a) the documentation is wrong b) I didn't write a correct \COPY command string c) there is a bug d) I can't correctly interpret the documentation e) I'm more stupid then usual thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
Re: COPY complaining about unquoted carriage return found in data... in a quoted field
From
Ivan Sergio Borgonovo
Date:
On Tue, 10 May 2011 15:59:07 +0200 Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: Sorry for the noise. The csv was automatically generated. The code was right but during generation there was some problem with the box generating it (php segfaulting) and there were some unclosed quotes in a much earlier line. Postgresql actually behaves as documented and expected... and the documentation is clear. > On Tue, 10 May 2011 14:38:23 +0200 > Pavel Stehule <pavel.stehule@gmail.com> wrote: > > > Hello > > > > COPY doesn't like '\n' too. > > > > Replace '\n' by '\\n' > > mmm maybe you were mislead by the "semi-transliterated" hexdump. > > There is no "slash" in the record, the actual input was the one > reported in hex. The following line was just to help reading the > hexdump. > > 09 22 56 | 69 61 20 46 | 61 65 6E 74 | 69 6E 61 2C > 20 35 33 0D | 0A 22 09 22 | 35 30 30 31 | 34 22 09 > > as a quick reference: > 0x09 = \t > 0x0a = \n > 0x0d = \r > 0x20 = space > 0x22 = " > > I thought that > > csv > quote as '"' > > where there exactly to avoid escaping of delimiters (other than > the quoting character itself) inside fields. > > From the docs: > http://www.postgresql.org/docs/8.3/static/sql-copy.html > «If the value contains the delimiter character, the QUOTE > character, the NULL string, a carriage return, or line feed > character, then the whole value is prefixed and suffixed by the > QUOTE character, and any occurrence within the value of a QUOTE > character or the ESCAPE character is preceded by the escape > character.» > > So up to my understanding once I've "quoted" a field I've to take > care of escaping just the quote and the escape characters and the > field may contain delimiter characters (field separator and record > separator) without escaping. > > I was wondering if a) the documentation is wrong b) I didn't write > a correct \COPY command string c) there is a bug d) I can't > correctly interpret the documentation e) I'm more stupid then usual > > thanks > -- Ivan Sergio Borgonovo http://www.webthatworks.it