Postgresql COPY CSV ERROR: extra data after last expected column

Question

I'm trying to import the data from http://www.unitedstateszipcodes.org/zip-code-database. A subset of the data looks like this:

"zip","type","primary_city","acceptable_cities","unacceptable_cities","state","county","timezone","area_codes","latitude","longitude","world_reg$
"00501","UNIQUE","Holtsville",,"I R S 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 I running is this:

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

And the result is this:

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

What am I doing wrong with the import?

wildplasser · Accepted Answer · 2013-11-30 16:20:24Z

3

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 OP has CR/CRLF problems ?)

edited Nov 30, 2013 at 16:20

answered Nov 30, 2013 at 15:18

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wildplasser Over a year ago

The column contents seem to be shifted WRT the headers, stating with the unacceptable--state column; you might need to shuffle the columns a bit ...

wildplasser Over a year ago

Note: I needed the explicit namelist because I had added the serial id column. The explicit list will also make it possible to shuffle the items.

Devin Dixon Over a year ago

WRT? What does that mean? I was also trying to make the zipcode a primary key in my example, but you are using serials.

wildplasser Over a year ago

Oops, I screwed up: I accidently read in the id serial, too. UPDATED

Collectives™ on Stack Overflow

Postgresql COPY CSV ERROR: extra data after last expected column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related