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 was very annoying as it was all being done the way it always works for me!
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..
Thank You. This saved me a lot of time.
thanks this worked for me
Hi, I am now getting the following error: #1148 – The used command is not allowed with this MySQL version.
Thanks.
Make sure to enclose all the values in ” and end lines as \n
“ERROR 1148 (42000): The used command is not allowed with this MySQL version” can be caused from this.
Good tip. Saved me a ton of time. Thanks!
thank you sooooo much!!! this has saved me so many processes!
Thank you so much, saved me!!!
Thanks dude – this just saved me!.
Thanks it works fine.
Thanks this saved me days of agro.
Little note for others phpmyadmin throws an error message but it still worked!
Many thanks !!!
I had been trying for hours to get the phpMyAdmin Import to work with a simple .csv file. Did not find this documented elsewhere. This was a big help – it worked perfectly.
Thank you!!!! I have pulled out my hair on this for the past 3 hours and now with your fix it’s in there in less than 1 min. Amazing.
thank you again for sharing. It really does help
No problem!
Awesome! THANK YOU
It Work!,
however it will only import one line of my excel/csv file, instead of 200 lines.
Anybody any idea what the problem is?
thx
What is the encoding of your file?
Convert it to UTF-8 so that new line breaks are seen correctly otherwise the character encoding may be stopping it from seeing multiple entries otherwise.
Thanks !!
It works for me
) yey
Really nice thing it helped to come out with the head ache of 3 hours . I want to thank ypu from my heart
Glad to hear it helped you out!
Only had one hand full of hair left when I found this page. THANK YOU for posting this, it saved me a lot of works.
Ahh, thanks Andrew, hopefully this will work!
This article got me close to a solution, but the system would only import 1 line. What fixed it was changing “auto” to “\r”.
Maybe it’s a Mac thing. The csv file came originally from Filemaker Pro. Anyway, hope that helps others. Thanks for getting me on the right road!
Perfect! my files also came from FileMaker and the “\r” is what makes it work
Thanks this helped me save hours
Hi, still can’t get it to work though. I have 6 fields. the first field is the ID field, which is AutoIncrement. However, in my csv file, i have 5 fields only, so as not to “corrupt” the auto-increment. I have also tried, puting 6 fields in my csv file. And I have tried typing the field names in “column names” under “format of imported file”. I prepared my csv file from excel, export as csv file, with the first column, blank (leaving it for the auto-increment).I have also tried not leaving it blank. All doesnt seem to work. Sometimes, it gives me the “invalid field count” error message. At other times, depending on the file I use, the apache just shuts down. would appreciate any help. Thanks!
Hi Patrick,
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 am sorry. I forgot to mention that in my excel file, i do have column names. I have tried, with and without column names. both dont seem to work.
Many thanks Andrew (from Ireland!!)
No Problem Conor!
Man, Thank you. I was at the point of throwing things. B/C my import wouldn’t work. Nice blog, check mine out sometime. http://g00t.com.
Thanx a lot for this post, a year later it’s still valid, saved me a lot of time and a big headache
Kudos
Glad it helped you!
Take a look at some of our other posts if you haven’t already!
thanks, you saved a lot of my time.. is this the permanent solution for this problem?
Yes, this is the way you should do it as it better handles your data.
why the data is not insert into my database??it just have the column name…
You should not have the column names as your first row of data.
Make sure that the field terminations are correct and that any auto-increment fields are blank.
Like all the others – thank you. You saved me time as well.
It’s absolute true. This is works well with most of the CSV file. Thanks for your post
Thanks a lot!! Brilliant!
Great Article i like it so nice of you
Thanks Alot it Save My 3 to 4 days i done it!!!!!!!!!!!
Thank you. I was struggling and this fixed it!
#1148 – The used command is not allowed with this MySQL version
“If LOAD DATA LOCAL INFILE is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version”
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’;
Thanks Charley!
thank you so much this has saved me tons of time
keep up the good work!
Thank you for saving me a lot of time!
Thanks a lot. You just save my butt.
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.
@Jerry, thank you for your post, I’m sure it will help many others that find this page.
Thank you so much. *offers a cherry lifesaver*
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
Cheers for that
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
This worked great. I just wish I knew where to look for directions to get this to work from a web page.
Awesome – this needs a Google +1 for sure!!