Skip site navigation (1) Skip section navigation (2)

CSV import issue - please help

From: Bernard <bht(at)actrix(dot)gen(dot)nz>
To: pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: CSV import issue - please help
Date: 2005-08-21 02:33:53
Message-ID: 1upfg19cru2jigbm553fugj5k6iebtd4ps@4ax.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
Dear Postgresql specialists

I would like to seek help with problems I am experiencing with the
COPY command.

We have a user whose 3rd party software exports text data in CSV
format.

The Postgresql import function COPY supports CSV but fails when
importing the data.

I have studied in the Postgresql documentation the specification for
CSV input of the COPY command and found that the data is compliant
with that specification.

In particular, I could confirm that the data uses the same newline
character "\n" for both CSV record termination and new lines within
text.

To eliminate any misunderstandings, I have executed a testcase on
Linux, again exclusively using the Linux "\n" newline character as
follows:


# su postgres
$ psql -d myDB -U postgres
=# CREATE TABLE TEST(FIELD_1 TEXT NOT NULL);
=# INSERT INTO TEST(FIELD_1)VALUES('Hello1 \n\n');
=# INSERT INTO TEST(FIELD_1)VALUES('Hello2');
=# COPY TEST TO '/tmp/TEST.txt' DELIMITER '\t' CSV;

WARNING:  CSV fields with embedded linefeed or carriage return
characters might not be able to be reimported

=# DELETE FROM TEST;
DELETE 2
=# COPY TEST FROM '/tmp/TEST.txt' DELIMITER '\t' CSV;
ERROR:  unterminated CSV quoted field
CONTEXT:  COPY test, line 2: ""


The error messages in the tescase suggest that the implementation does
not agree with the specification in the manual.

I have also verified with other tests that the user's CSV output
conforms with the Postgres specification.

Possibly someone with more in-depth knowledge may have additional
information, e.g. undocumented command parameters, that circumvent
this problem.

The user has only a CSV export function available without any option
to eliminate newline characters from that data.

Unfortunately the user is not in the position to convert the output to
another format or change the software output in any non-standard way.

Any help would be highly appreciated.

Regards

Bernard

Responses

pgsql-bugs by date

Next:From: Michael FuhrDate: 2005-08-21 03:16:15
Subject: Re: CSV import issue - please help
Previous:From: Tom LaneDate: 2005-08-20 15:31:15
Subject: Re: BUG #1831: plperl gives error after reconnect.

pgsql-general by date

Next:From: Michael FuhrDate: 2005-08-21 03:16:15
Subject: Re: CSV import issue - please help
Previous:From: Tom LaneDate: 2005-08-21 02:06:39
Subject: Re: extract (dow/week from date)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group