I have data in excel sheet named with district.csv needs to import only few columns from excel to postgres table named with hc_court_master
copy hc_court_master (district_cd, category,category1,shrt_nm,court_name)
from 'D:\district.csv'
with (format csv)
I was getting the error
ERROR: extra data after last expected column
SQL state: 22P04
Context: COPY hc_court_master, line 1: "court_code,court_name,short_c_name,category,dist_no,dist_name,category1"
There is two problem:
You can not copy just few column from csv. And you can not change their order.
Just use HEADER keyword in with clause to ignore header in csv file.
copy hc_court_master (court_code, court_name, short_c_name, category, dist_no, dist_name, category1) from 'D:\district.csv' with (format csv, HEADER)
If you do not want all fields in your table you can:
ALTER TABLE DROP COLUMN after importing
Import into temp table and after that run INSERT INTO ... SELECT ...
As mentioned here you can not to load selected fields from CSV, only whole data. So
begin; -- Start transaction
do $$
begin
create temp table t( -- Create table to load data from CSV
court_code text, -- There are whole list of columns ...
court_name text,
short_c_name text,
category text,
dist_no text,
dist_name text,
category1 text) on commit drop;
copy t from '/home/temp/a.csv' with (
format CSV,
header -- To ignore first line
);
end $$;
insert into hc_court_master (district_cd, category,category1,shrt_nm,court_name)
select <appropriate columns> from t;
commit;
copy hc_court_master (district_cd, category,category1,shrt_nm,court_name)
from 'D:\district.csv'
with (format csv)
i think sql above will work only if your csv contains only required columns (district_cd, category,category1,shrt_nm,court_name).
You can delete extra columns then try to upload.
Please refer below link
