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..
still getting this error” LOAD DATA LOCAL INFILE ‘C:\\xampp\\tmp\\phpC960.tmp’ INTO TABLE
translationFIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\r\n’;”Awsome
hi, i integrate as per ur screenshot but getting warnings.
This saved me, thank you so much 🙂
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
God I love you.
Everything is BRILLIANT when it works.
Ur the man! It WORKS!
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?
Double check if your “fields terminated” in your CSV file isn’t actually comma separated (“,”) rather than semi-colon separated (“;”)
This saved me so much time – thank you so much!
At last someone knows what needs to be done – My “NEW” best friend 🙂
Thanks!
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
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.
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.
Hi Ian,
It’s complaining about your field separator, not the line delimiter.
Each field in the csv per row is separater by a comma (,) so make sure non of your field cells have a comma in them, if they do make sure you add apostrophes “” around them to encapsulate them.
The line termination should be automatically detected as different platforms will read file encoding linebreaks differently.
Hope this helps!
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.
Try changing the comma to a semi-colon ;
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 ?
Make sure that the columns match and that ids are not inserted if auto_increment is enabled in your table.
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 ?
Still doesn’t work for us… it’s telling me “can’t take uncompressed imports”. We have a standard CSV file…
Could you please paste the exact error message you are receiving.
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.
You must first export out to a standard CSV file rather than attempting to import the original XLS(x) file.
Awesome. Thank you!
Glad to help!
hi,
check your column name.. it should start form a. if column is not starting form a it will give same error
thanks
Wish I would have googled this about 3 hours ago. Thank you!!!
You are sweet … Its done.. Thanks
Thank you, Thank you, Thank you
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!!!!!!
Thanks man!!! u saved me after wasting the whole day looking at that error message. U r Great… 😀
Thanks a lot
Wow, thank you so much for providing THE solution to my problem! I was nearly ready to throw in the towel.
I never register for anything on the net but had to say a big thank you on this one!
Glad it helped you out!
wow this works like charm, great tip…
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!
THANK YOU!!!
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.
Thanks for the extra step Rick!
Superb dude,,it works like crazyy….Thanks a ton
Pleasure!
Thanks a lot for this post! Really helped me out.
Glad it helped you
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.
Take a look around our site whenever you are having a problem, and feel free to ask us if you have any issues that we have not covered.
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
Make sure you are importing to a table by selecting the table first, then click import and check for the settings, they should be there as per the image preview at the top of this post.
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?
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?
@renee, First check to see what your csv file looks like in a text-editor, perhaps there is an error in there? By error I mean maybe there is an additional comma or some kind of linebreak type operation going on?
Thanks! This solved my problem. I really appreciate your help.
thank you!!!!!!!!!!!!!!!!!!!!!!
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
Thanks a lot,
Saves my time alot…
Thank you so much, I’m pretty sure you saved me from ripping all of my hair out!
Awesome – this needs a Google +1 for sure!!
This worked great. I just wish I knew where to look for directions to get this to work from a web page.
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 😉
Thanks for your additions!
Thanks for all important updates.. Thanks again..
Cheers for that
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
Thank you so much. *offers a cherry lifesaver*
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.
Thanks a lot. You just save my butt.
Thank you for saving me a lot of time!
thank you so much this has saved me tons of time 😀 keep up the good work!