CSV import issue - please help

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
bht
Reply | Threaded
Open this post in threaded view
|

CSV import issue - please help

bht
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly