postgresql Error "extra data after last expected column” while trying to import csv file

Question

I have a csv file with 8 columns but my table have a 6 columns with same sequence as csv file. Following is the format and schema of csv and table

example.csv

"CH_ID","CH_NAME","ADDRESS_1","ADDRESS_2","POST_CODE","MDSC","UPDATE_TIMESTAMP","DELETED_IND"
    403,"1182463","","10 St Pauls Court","SG2 8DN",1,2017-10-20 12:08:36,"N"

table structure:-

CREATE TABLE ts_ch (
    id int8 NOT NULL DEFAULT nextval('ts_care_homes_seq'::regclass),
    ch_name varchar(200) NULL DEFAULT NULL::character varying,
    address_1 varchar(255) NULL DEFAULT NULL::character varying,
    address_2 varchar(255) NULL DEFAULT NULL::character varying,
    post_code varchar(10) NULL DEFAULT NULL::character varying,
    mdsc_patient int2 NULL,
    PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
) ;

I tried the following command:

copy ts_care_homes(id, ch_name, address_1, address_2, post_code, mdsc_patient)  FROM '/var/tmp/care_home_v.csv' csv HEADER

But I'm getting this error:

extra data after last expected column

You could try COPY ... FROM PROGRAM ... and use a program like cut to remove the extra columns before loading. — Laurenz Albe
actually csv is generated by third party and i am writing script in php using copy command to import data. Is it possible to import data of selected columns using copy command? — Sur

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-02-27T07:29:28

You cannot load only part of a CSV file with a plain COPY command.

One way to achieve what you want is to use COPY ... FROM PROGRAM, for example like this (assuming you have sed installed):

COPY laurenz.ts_ch(id, ch_name, address_1, address_2, post_code, mdsc_patient)
FROM PROGRAM
   'sed -e ''s/\(,\([^,]*\)\|\(\("[^"]*"\)*\)\)\{2,2\}$//'' /var/tmp/care_home_v.csv'
(FORMAT csv, HEADER on);

The sed command strips the last two columns from the CSV file.

postgresql Error "extra data after last expected column” while trying to import csv file

1 Answers