unable to load CSV file in Postgresql

Question

I am new to PostgresSQL and i am trying to load a data of the following format:

1,57000000000A,A,EN,0:01,male,hahaha,"Address is address",1234567,Not Applicable,,
walk in and 
scream,papa,00:20:54,02:17:41,ABC,00:20:54,02:17:41,musical,farm,,cody,9,bad 
boy,,,,,X,,rest,,,musical is muscal,John Kotters ,,,2011-01-01

There are more than 10k records and some columns may have NULL data. When i try to do this:

COPY ane FROM 'C:/Program Files (x86)/PostgreSQL/8.4/csv-data/my-data.csv' WITH
DELIMITER ',';

I am getting the following error:ERROR: extra data after last expected column SQL state: 22P04

I have checked the columns and they are ok. This means that no columns are left out.

Hope someone can help. Advance thanks.

Unless you provide a (short!) sample file and the table definition of ane nobody can say anything for certain. The error message is quite clear. Maybe you have a text column with a comma (,) in it that is not properly quoted? — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 11, 2012 at 18:58
From the sample you posted the fields containing "walk in and scream" and "bad boy" appears to have newlines in it which is the CSV record terminator. You probably need to quote those fields or they end the record and you get field misalignment on the subsequent lines. — dbenhur
– dbenhur, Commented Mar 11, 2012 at 21:56

madi · Accepted Answer · 2012-03-13 10:22:14Z

4

It is supposed to be

 COPY ane FROM 'C:/Program Files (x86)/PostgreSQL/8.4/csv-data/my-data.csv' WITH
 DELIMITER ',' CSV;

It worked for me

answered Mar 13, 2012 at 10:22

madi

5,6625 gold badges40 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erwin Brandstetter Over a year ago

You don't need DELIMITER ','. That's the default for CSV anyway.

Collectives™ on Stack Overflow

unable to load CSV file in Postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related