phpMyAdmin – Invalid field count in csv input on line 1

I was trying to import a massive csv dataset into phpMyAdmin and it kept throwing error:
Invalid field count in csv input on line 1

This is quite annoying as I followed all the usual steps as per normal.

To solve this I had to do the following:

  • Import
  • Browse for your CSV file.
  • Select CSV using LOAD DATA (rather than just CSV)
  • Change “Fields terminated by” from “;” to “,”
  • Make sure “Use LOCAL keyword” is selected.
  • Click “Go”

Done!

Still having problems? Let me know..

Tags:
5 1 vote
Article Rating
Subscribe
Notify of
guest
118 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Imen
Imen
4 years ago

This saved me, thank you so much 🙂

livetvaccess
livetvaccess
4 years ago

Hi,

I have the above issue and I need the CSV file to be separated by semicolon (Not just comma), If I change Fields terminated by” from “;” to “,” , will it be still imported as semicolon separated csv file?
Thanks

Philip Harper
5 years ago

God I love you.

Everything is BRILLIANT when it works.

Gary
Gary
6 years ago

Ur the man! It WORKS!

Payam
7 years ago

I am trying to import the comments back into my log I had to delete my old blog and reinstall everything. I have around 80 comments to import which are on 292 lines some with spaces between them.

Yet when i try to import with your method its says the below
————————————-
Import has been successfully finished, 1 queries executed. (comments.csv)
LOAD DATA LOCAL INFILE ‘/tmp/phpcMpvte’ INTO TABLE `wp_comments` FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\’ LINES TERMINATED BY ‘rn’# 1 row affected.
——————————-
but when i then go into my wp_comments file i cant see any of them comments just a blank second line. any ideas on what i should be doing?

Christina
7 years ago

This saved me so much time – thank you so much!

Johann
Johann
7 years ago

At last someone knows what needs to be done – My “NEW” best friend 🙂
Thanks!

C
7 years ago

I didn’t have to change any of the settings, but I did have to open my CSV file in Excel and save it. Once I did this, I was able to import all records without a problem

G
G
7 years ago

Thanks… was pulling my hair out trying to figure out what was wrong with my CSV… My fields were terminated by semi-colons so where you say “Change “Fields terminated by” from “;” to “,””, I didn’t do that.

Ian
7 years ago

Hi all,

Getting this error: #1083 – Field separator argument is not what is expected; check the manual

I’ve tried importing from Windows and mac generated csvs, with and without the id column filled, and changing the line delimiter to r, n, rn and any combination of the 3.

Ian
7 years ago
Reply to  Andrew

I’m still getting the same error message with apostrophes as well as quotes. The problem could be that I am trying to put large blocks of text into a single cell; that text contains commas, quotes, and line breaks. Do I really need to escape all of those? I’ve never gotten this error message before with similar blocks of text.

G
G
7 years ago
Reply to  Ian

Try changing the comma to a semi-colon ;

Nafisah
Nafisah
7 years ago

Hi , I’ve imported the csv file however not all fields are filled up . I can only see the id numbers. The names,contact,email,website,position are missing . I am using microsoft excel . How ?

Nafisah
Nafisah
7 years ago
Reply to  Andrew

I’ve deleted the id column in my excel and the column in the database is auto-increment.
Now I’ve 5 columns in my excel as well as 5 columns in my database . But its still not working. Any other solution ?

Svetlana
7 years ago

Still doesn’t work for us… it’s telling me “can’t take uncompressed imports”. We have a standard CSV file…

Patrick
Patrick
7 years ago

Tried this method and got the message: “This plugin does not support compressed imports!”

Anyone know what to do for that? My file is not compressed, just a regular xlsx sheet.

Roger
Roger
7 years ago

Awesome. Thank you!

bishnoi
7 years ago

hi,
check your column name.. it should start form a. if column is not starting form a it will give same error
thanks

Kara
Kara
7 years ago

Wish I would have googled this about 3 hours ago. Thank you!!!

yes
yes
7 years ago

You are sweet … Its done.. Thanks

marty
marty
7 years ago

Thank you, Thank you, Thank you

Daniel Hurst
Daniel Hurst
7 years ago

Hi,

I am only able to add one row, ive tried the r
also checked that it was utf8 and its on csv using LOAD DATA

HELP!!!!!!

Mustapha
Mustapha
7 years ago

Thanks man!!! u saved me after wasting the whole day looking at that error message. U r Great… 😀

sujata
sujata
7 years ago

Thanks a lot

Sarah
Sarah
7 years ago

Wow, thank you so much for providing THE solution to my problem! I was nearly ready to throw in the towel.

Jo
Jo
8 years ago

I never register for anything on the net but had to say a big thank you on this one!

Humphrey
Humphrey
8 years ago

wow this works like charm, great tip…

Vaishnavi Kadu
Vaishnavi Kadu
8 years ago

Hey, this soln was like miracle to me. I chkd my csv file 10-15 times. Still was getting error n I was frustated. Thank you very much!

Belladict
Belladict
8 years ago

THANK YOU!!!

Rick Gladwin
8 years ago

One extra step for users of Excel for Mac 2011

When selecting “Save As…” in Excel, make the file type “Windows Comma Separated (csv)”. Using the default CSV format means the hint in this article still won’t work.

I changed the exported file format (using the hint) and that did the trick.

kk
8 years ago

Superb dude,,it works like crazyy….Thanks a ton

Renne
Renne
8 years ago

Thanks a lot for this post! Really helped me out.

David Brandon
8 years ago

Thank you so much for this post. You really saved my ass. Spent 3 days trying to get around this same problem importing a csv file into mysql database table.

Gavin
Gavin
8 years ago

I have trouble loading my csv file. I’ve changed the ‘fields terminated’ to a comma while having ‘use LOCAL keyword’ checked, and I get the following error message: “#1148 – The used command is not allowed with this MySQL version”. I see the comments posted by Andrew and Charlie on this, but I don’t quite understand how to follow their suggestion. How do you enable “LOAD DATA LOCAL INFILE”?

Thanks

Gavin
Gavin
8 years ago
Reply to  Andrew

I am importing to a table already created. I can’t view the image at the top of the forum, and when I open it in a new tab I get this error message:
“Not Found
You seem to be lost!
The page you were looking for could not be found, you can try looking around the site for yourself or perhaps what you were looking for is in our blog?”
How do I access the settings you mentioned? and what should I be changing in those settings?

renee
renee
8 years ago

hi! i am having a hard time uploading the csv file…
all that is upload is just 2 ROWS of data.. i have 100+ data… what should i do?

Elizabeth
Elizabeth
8 years ago

Thanks! This solved my problem. I really appreciate your help.

seth
seth
8 years ago

thank you!!!!!!!!!!!!!!!!!!!!!!

Duane Charles
8 years ago

Dammit, I had to search through a 1/2 days worth of info , and this is all I needed. Thanks! Importing csv to a database… is too simple

Ericson
8 years ago

Thanks a lot,
Saves my time alot…

Nicole Hanusek
8 years ago

Thank you so much, I’m pretty sure you saved me from ripping all of my hair out!

David Carruth
8 years ago

Awesome – this needs a Google +1 for sure!!

Peter Reguin
8 years ago

This worked great. I just wish I knew where to look for directions to get this to work from a web page.

One more tip - export from Excel

Hi Andrew, and thanks for your tutorial!
It did not work for us 100%, however, gave a good start.

This worked for us and might help others for multilingual Excel worksheets / csv files:

1. Export the XLS-Sheet (delete the headline before) from Excel to CSV. You might want to open the CSV with a text editor to see that columns are separated by ;
3. Go to phpmyadmin, open your database, chose your table.
4. Click on import. Browse and select your CSV-file.
5. Change format from “SQL” to “CSV using LOAD DATA”. Leave utf-8 encoding.
6. Columns terminated by should be: ;
7. Leave the other settings default, click Go.
Done.

This should import all multilingual excel sheets properly.

All the best 😉

Dileep Kumar D
8 years ago

Thanks for all important updates.. Thanks again..

Jamus
9 years ago

Cheers for that

wordman
9 years ago

Andrew,

Hello and a big THANK YOU from Las vegas! Your tech tip worked for me as well, I found what I was looking for with this comment you made to another poster:

“For your auto-increment field you need to leave it blank for the whole column except for the very first item which you can put a value in, you then delete that value from the csv file once it has been saved. Excel will delete the whole column from the csv if it does not have values in it.
Does this make sense?”

I’ll admit, the wording confused me somewhat, specifically, how do I delete a value from a saved file? Not to worry, I added a 1 in the first cell of my AI column in Excel, saved the file, followed your excellent instructions and voila! Perfectly stocked table.

Thank you! Bookmarked!

Sincerely,

wordman

Steph
Steph
9 years ago

Thank you so much. *offers a cherry lifesaver*

Jerry
Jerry
9 years ago

This workaround didn’t work for me. Maybe because I had utf8 data (Ukrainian language).

What worked though was:
1. Upgrade to the latest phpMyAdmin (3.4.4)
2. Import the .xlsx file as is

I was impressed.

kalar
9 years ago

Thanks a lot. You just save my butt.

Marc
Marc
9 years ago

Thank you for saving me a lot of time!

GBP
GBP
9 years ago

thank you so much this has saved me tons of time 😀 keep up the good work!

shiva
shiva
9 years ago

#1148 – The used command is not allowed with this MySQL version

Charley
Charley
9 years ago
Reply to  Andrew

This might help (depending on how mySQL was setup):

SET @@global.local_infile = ‘On’;

…run your SQL now and when it’s done remember to turn it off again…

SET @@global.local_infile = ‘Off’;

a bear
a bear
9 years ago

Thank you. I was struggling and this fixed it!

trackback

[…] table. I kept getting the error Invalid field count in csv input on line 1. With the help of http://andrewodendaal.com/phpmyadmin-invalid-field-count-in-csv-input-on-line-1/, I was able to find the right settings which are as […]