Postgres CSV copy statement

Question

Hi guys i have question better say error question. MY function in postgres look's like:

CREATE TEMP TABLE aljazeera

(
    date character varying,
    channel_name character varying,
    date1 character varying,
    start character varying,
    program character varying,
    sub character varying,
    epis_title character varying
    ) ;

COPY aljazeera FROM '/opt/transcode/data/epg/output/CSV/ALJAZEERA/pre_g_1.csv' WITH (FORMAT CSV, DELIMITER ',', QUOTE '"');

And my file looks like :

http://www.speedyshare.com/bNcjM/pre-g-1.csv

(WARNING: possible malware, certainly nasty bundleware on download link. Only use the top link Download: pre-g-1.csv).

When I try to upload to table it say error:

ERROR:  unterminated CSV quoted field
CONTEXT:  COPY aljazeera, line 175: "2013-12-24,02:00:00","KRAJ PROGRAMA",,,,
2013-12-24,07:30:00,"Sportski magazin  (R)",,"Sportski doga..."

I don't know where is problem. Any advice for this issue.

That speedyshare thing is awful, and tries to trick users into downloading an extremely suspicious looking "download manager", complete with double file extension. Please don't link to it here, and in fact please don't use it anywhere ever again. — Craig Ringer
– Craig Ringer, Commented Dec 29, 2013 at 23:04

Craig Ringer · Accepted Answer · 2013-12-29 23:20:15Z

Once I eventually downloaded the file without that nasty download manager I could reproduce the error:

craig=> \copy aljazeera FROM 'pre_g_1.csv' WITH (FORMAT CSV, DELIMITER ',', QUOTE '"')
ERROR:  unterminated CSV quoted field
CONTEXT:  COPY aljazeera, line 175: "2013-12-24,02:00:00","KRAJ PROGRAMA",,,,
2013-12-24,07:30:00,"Sportski magazin  (R)",,"Sportski doga..."

The error isn't actually on line 175. It is that some prior line has an unbalanced quote. By binary search it was easy to narrow that down to line 26:

2013-12-24,02:00:00","KRAJ PROGRAMA",,,,

I'm sure you can see what the problem is there. You've got a stray quote in the date.

BTW, if you ever need to link to chunks of text, you can use http://gist.github.com/, http://pastebin.com/, http://pastebin.ca/, etc. (For PostgreSQL query plans http://explain.depesz.com/ is best). That speedyshare thing is nasty.

Collectives™ on Stack Overflow

Postgres CSV copy statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related