MySQL Invalid column count in CSV input on line 1

Question

Ok row 1 consists of the following names in my CSV file:

id
username
password
email
fname
lname
organization
add_1
add_2
city
state
zip
country
phone
fax
group_id

In my MySQL table, it's as followed:

id  int(11)
username    varchar(35)
password    varchar(60)
email   varchar(50)
fname   varchar(25)
lname   varchar(25)
organization    varchar(50)
add_1   varchar(50)
add_2   varchar(50)
city    varchar(45)
state   varchar(2)
zip varchar(6)
country varchar(25)
phone   varchar(11)
fax varchar(11)
group_id    tinyint(3)

I don't get why it's throwing this error... there are 16 columns in each one. They are named exactly the same..

I even tried deleting the ID column and still no luck.

My CSV file:

username,password,email,fname,lname,organization,add_1,add_2,city,state,zip,country,phone,fax,group_id
apewraps,BxKgp9IykPvaPuN07Rowbv9CPhgO73F1TWZEDtJjN,[email protected],First Name,Last Name,Organization,Address,City,OH,40394,US,3049303939,1
bp-graphics,8C3EwVs12AhauYKAHrCfbXc79ENJYf7tw9UiFzvN6,[email protected],First Name,Last Name,Organization,Address,City,OH,40394,US,3049303939,1

I even tried it with the ID column:

id,username,password,email,fname,lname,organization,add_1,add_2,city,state,zip,country,phone,fax,group_id
1,apewraps,BxKgp9IykPvaPuN07Rowbv9CPhgO73F1TWZEDtJjN,[email protected],First Name,Last Name,Organization,AddressCity,OH,40394,US,30493039391
2,bp-graphics,8C3EwVs12AhauYKAHrCfbXc79ENJYf7tw9UiFzvN6,[email protected],First Name,Last Name,Organization,AddressCity,OH,40394,US,30493039391

Still nothing. I was looking at this thread: Problems importing a CSV file

But those trailing commas and id column didn't help. I even kept the trailing commas at the end and it still does nothing.

Thanks for any help.

Answer 1

I was specifying my end of my columns odd.

It had this by default:

Lines terminated with: auto

When it should have been:

Lines terminated with: \n

And make sure your columns are terminated by ,. Sometimes it has ; by default.

Answer 2

One of the ways to do it (kinda hacky) is to delete your existing table. Go to the database and select import, then import the .csv file with the necessary settings and Voila it gets imported with a new table being created with your existing columns, you might have to change a few column names and delete a few rows from the end.