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.
