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),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 | 3 Answers
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.
Not sure this counts as an answer, but I just hit this with a bunch of CSV files, and found that simply opening them in Excel and re-saving them with no changes made the error go away. IOTW there is possibly some incorrect formatting in the source file that Excel is able to clean up automatically.
2I tried your example and it works fine but ....
your command from the psql command line is missing \
database=# \COPY agency FROM 'myFile.txt' CSV HEADER DELIMITER ',';And next time please include DDL
I created DDL from the csv headers
