Re: multiline CSV fields

This example should fail on data line 2 or 3 on any platform,
regardless of the platform's line-end convention, although I haven't
tested on Windows.

cheers

andrew

[andrew(at)aloysius inst]$ bin/psql -e -f csverr.sql ; od -c
/tmp/csverrtest.csv
create table csverrtest (a int, b text, c int);
CREATE TABLE
insert into csverrtest values(1,'a',1);
INSERT 122471 1
insert into csverrtest values(2,'foo\r\nbar',2);
INSERT 122472 1
insert into csverrtest values(3,'baz\nblurfl',3);
INSERT 122473 1
insert into csverrtest values(4,'d',4);
INSERT 122474 1
insert into csverrtest values(5,'e',5);
INSERT 122475 1
copy csverrtest to '/tmp/csverrtest.csv' csv;
COPY
truncate csverrtest;
TRUNCATE TABLE
copy csverrtest from '/tmp/csverrtest.csv' csv;
psql:cvserr.sql:9: ERROR: literal carriage return found in data
HINT: Use "\r" to represent carriage return.
CONTEXT: COPY csverrtest, line 2: "2,"foo"
drop table csverrtest;
DROP TABLE
0000000 1 , a , 1 \n 2 , " f o o \r \n b a
0000020 r " , 2 \n 3 , " b a z \n b l u r
0000040 f l " , 3 \n 4 , d , 4 \n 5 , e ,
0000060 5 \n
0000062
[andrew(at)aloysius inst]$

Bruce Momjian wrote:

>Can I see an example of such a failure line?
>
>---------------------------------------------------------------------------
>
>Andrew Dunstan wrote:
>
>
>>Darcy Buskermolen has drawn my attention to unfortunate behaviour of
>>COPY CSV with fields containing embedded line end chars if the embedded
>>sequence isn't the same as those of the file containing the CSV data. In
>>that case we error out when reading the data in. This means there are
>>cases where we can produce a CSV data file which we can't read in, which
>>is not at all pleasant.
>>
>>Possible approaches to the problem:
>>. make it a documented limitation
>>. have a "csv read" mode for backend/commands/copy.c:CopyReadLine() that
>>relaxes some of the restrictions on inconsistent line endings
>>. escape embedded line end chars
>>
>>The last really isn't an option, because the whole point of CSVs is to
>>play with other programs, and my understanding is that those that
>>understand multiline fields (e.g. Excel) expect them not to be escaped,
>>and do not produce them escaped.
>>
>>So right now I'm tossing up in my head between the first two options. Or
>>maybe there's another solution I haven't thought of.
>>
>>Thoughts?
>>
>>cheers
>>
>>andrew
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>
>>
>>
>
>
>