ERROR: extra data after last expected column in postgres table

Question

I am working on a project where I need to create a new table, then import data from a CSV. I've read many similar questions ("extra data after last expected column") and answers on StackOverflow, but I still haven't found the culprit.

CREATE TABLE colleges2014_15 (
unitid integer, 
intsnm text, 
city text, 
stabbr text, 
zip_clean char, 
control integer, 
latitude float, 
longitude float, 
tutionfee_in float, 
tuitionfee_out float, 
pctpell float,
inc_pct_lo float, 
dep_stat_pct_ind float, 
dep_debt_mdn float, 
ind_debt_mdn float, 
pell_debt_mdn float,
ugds_men float, 
ubds_women float, 
locale integer, 
PRIMARY KEY(unitid)
);

The table is created successfully with the 19 different columns. Then I go try to import the data into the new table.

COPY colleges2014_15(
unitid, 
intsnm, 
city, 
stabbr, 
zip_clean, 
control, 
latitude, 
longitude, 
tutionfee_in, 
tuitionfee_out, 
pctpell,
inc_pct_lo, 
dep_stat_pct_ind, 
dep_debt_mdn, 
ind_debt_mdn, 
pell_debt_mdn, 
ugds_men, 
ubds_women, 
locale
)
FROM '/Users/compose/Downloads/CollegeScorecard_Raw_Data x/MERGED2014_15_cleaned.csv' CSV HEADER
;

And I get the error message. I've done the following in the CSV:

• Made sure it's saved as UTF-8 CSV (working on a Mac)
• Already cleaned out all commas in every row
• Cleaned out all NULL values
• Confirmed that all the data types (integer, float, text, etc.) are correct
• I've tried to simply COPY only the first column, unitid; it failed. I've tried importing only the second column (intsnm) and it failed with the same error.

The full error message when trying to COPY over all 19 columns is as follows:

An error occurred when executing the SQL command: COPY colleges2014_15( unitid, intsnm, city, stabbr, zip_clean, control, latitude, longitude, tutionfee_in, tuitionfee_out, pctpell, inc_pct_...

ERROR: extra data after last expected column Where: COPY colleges2014_15, line 2: "100654,Alabama A & M University,Normal,AL,35762,35762,1,34.783368,-86.568502,9096,16596,0.7356,0.651..." 1 statement failed.

Execution time: 0.03s

The full error message when trying to copy simply the first column only is:

An error occurred when executing the SQL command: COPY colleges2014_15( unitid ) FROM '/Users/compose/Downloads/CollegeScorecard_Raw_Data x/MERGED2014_15_cleaned.csv' CSV HEADER

ERROR: extra data after last expected column Where: COPY colleges2014_15, line 2: "100654,Alabama A & M University,Normal,AL,35762,35762,1,34.783368,-86.568502,9096,16596,0.7356,0.651..." 1 statement failed.

Execution time: 0.01s

Hugely appreciate any help.

Answer 1

It took me a while to figure out what was wrong searching on the error so have posted my problem to help others. My issue was inexperience with pgAdmin as pgAdmin requires the table to be created WITH columns before the data is imported. I had expected that the headers would be used from the .csv file, most other packages I have used worked this way.

If you are working with a GIS system using PostGIS there is an easy solution. I am using QGIS 3.4, with Postgres and PostGIS installed.

In QGIS
Select Database menu option
Select DBManager
On left - choose location for table Select Import Layer/File On the next window select the following
Input - choose file
Table - enter table name
OK