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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-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

Browse pgsql-bugs by date

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

Browse pgsql-general by date

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