“extra data after last expected column” while trying to import a csv file into postgresql

Question

I try to copy the content of a CSV file into my postgresql db and I get this error "extra data after last expected column".

The content of my CSV is

    agency_id,agency_name,agency_url,agency_timezone,agency_lang,agency_phone
100,RATP (100),http://www.ratp.fr/,CET,,

and my postgresql command is

COPY agency (agency_name, agency_url, agency_timezone) FROM 'myFile.txt' CSV HEADER DELIMITER ',';

Here is my table

CREATE TABLE agency (
    agency_id character varying,
    agency_name character varying NOT NULL,
    agency_url character varying NOT NULL,
    agency_timezone character varying NOT NULL,
    agency_lang character varying,
    agency_phone character varying,
    agency_fare_url character varying
);

     Column      |       Type        | Modifiers 
-----------------+-------------------+-----------
 agency_id       | character varying | 
 agency_name     | character varying | not null
 agency_url      | character varying | not null
 agency_timezone | character varying | not null
 agency_lang     | character varying | 
 agency_phone    | character varying | 
 agency_fare_url | character varying |

This means that using the , delimiter, the copy command parsed more fields than the destination table! Can you please bring your destination table data structure and samples of your CSV content ? — Houari
I tried to specify the columns but it's still the same problem. I edited my question. — Frederic Le Feurmou
Your table contain 3 columns, but your csv file that you want to import contains 6 — Houari
Yes, but sadely, the postgreSQL copy command can't handle column selection from your file. In other words, you can only copy whole file at once! — Houari
You could have a table with 6 columns and a file containing only 3 fields for example, and you can import your 3 fields to a spécific columns of your table... — Houari

Mladen Uzelac Mladen Uzelac · Accepted Answer · 2014-11-02T20:03:08

Now you have 7 fields.

You need to map those 6 fields from the CSV into 6 fields into the table.

You cannot map only 3 fields from csv when you have it 6 like you do in:

\COPY agency (agency_name, agency_url, agency_timezone) FROM 'myFile.txt' CSV HEADER DELIMITER ',';

All fields from the csv file need to to be mapped in the copy from command.

And since you defined csv , delimiter is default, you don't need to put it.

“extra data after last expected column” while trying to import a csv file into postgresql

3 Answers