All geek questions in one place

Postgresql COPY CSV ERROR: additional data after the last expected column

I am trying to import data from http://www.unitedstateszipcodes.org/zip-code-database . A subset of the data is as follows:

"zip","type","primary_city","acceptable_cities","unacceptable_cities","state","county","timezone","area_codes","latitude","longitude","world_reg$ "00501","UNIQUE","Holtsville",,"IRS Service Center","NY","Suffolk County","America/New_York","631","40.81","-73.04","NA","US","0","384", "00544","UNIQUE","Holtsville",,"Irs Service Center","NY","Suffolk County","America/New_York","631","40.81","-73.04","NA","US","0","0"

The postgresql command that I run is as follows:

 copy development.zip_codes FROM '/tmp/zip_code_database.csv' WITH DELIMITER ',' CSV HEADER;

And the result is the following:

 ERROR: extra data after last expected column SQL state: 22P04 Context: COPY zip_codes, line 2: ""00501","UNIQUE","Holtsville",,"IRS Service Center","NY","Suffolk County","America/New_York","631"..."

What am I doing wrong with import?

+6
source share
1 answer

Works like a charm, here ...

 DROP TABLE zipcodes CASCADE; CREATE TABLE zipcodes ( id serial NOT NULL PRIMARY KEY , zzip varchar NOT NULL UNIQUE , ztype varchar , primary_city varchar , acceptable_cities varchar , unacceptable_cities varchar , state varchar , county varchar , ztimezone varchar , area_codes varchar , latitude varchar , longitude varchar , world_region varchar , country varchar , decommissioned varchar , estimated_population varchar , notes varchar ); COPY zipcodes (zzip,ztype,primary_city , acceptable_cities,unacceptable_cities , state,county,ztimezone,area_codes , latitude,longitude,world_region,country , decommissioned,estimated_population,notes ) FROM '/tmp/zip_code_database.csv' WITH CSV HEADER delimiter ',' ;

Result:

 DROP TABLE CREATE TABLE COPY 42522

(maybe the PR has CR / CRLF problems?)

+3
source

Source: https://habr.com/ru/post/959083/

More articles:


All Articles