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, Mar 11 '12 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, Mar 11 '12 at 21:56

score 4 · Accepted Answer · answered Mar 13 '12 at 10:22

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 '12 at 10:22

madi

5,612
5
36
48

2

You don't need `DELIMITER ','`. That's the default for CSV anyway. – Erwin Brandstetter Apr 03 '12 at 21:43

unable to load CSV file in Postgresql

1 Answers1

Linked