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..


117 Comments

Imen · November 19, 2016 at 13:11

This saved me, thank you so much 🙂

livetvaccess · June 10, 2016 at 14:08

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

Gary · April 11, 2014 at 13:33

Ur the man! It WORKS!

Payam · August 21, 2013 at 13:31

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?

    Andrew · August 22, 2013 at 08:12

    Double check if your “fields terminated” in your CSV file isn’t actually comma separated (“,”) rather than semi-colon separated (“;”)

Christina · July 13, 2013 at 11:37

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

Johann · May 25, 2013 at 17:14

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

C · May 24, 2013 at 18:55

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 · May 21, 2013 at 11:28

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 · May 20, 2013 at 21:20

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.

    Andrew · May 21, 2013 at 07:34

    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!

      Ian · May 21, 2013 at 13:23

      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 · May 21, 2013 at 11:29

    Try changing the comma to a semi-colon ;

Nafisah · May 15, 2013 at 02:27

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 ?

    Andrew · May 15, 2013 at 08:17

    Make sure that the columns match and that ids are not inserted if auto_increment is enabled in your table.

      Nafisah · May 15, 2013 at 10:39

      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 · May 1, 2013 at 15:53

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

    Andrew · May 15, 2013 at 08:19

    Could you please paste the exact error message you are receiving.

Patrick · March 25, 2013 at 15:49

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.

    Andrew · March 25, 2013 at 15:53

    You must first export out to a standard CSV file rather than attempting to import the original XLS(x) file.

Roger · March 21, 2013 at 17:12

Awesome. Thank you!

bishnoi · March 21, 2013 at 11:41

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

Kara · March 17, 2013 at 02:10

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

yes · February 22, 2013 at 13:04

You are sweet … Its done.. Thanks

marty · February 16, 2013 at 08:28

Thank you, Thank you, Thank you

Daniel Hurst · February 3, 2013 at 13:52

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 · January 25, 2013 at 17:54

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

sujata · January 25, 2013 at 06:54

Thanks a lot

Sarah · January 13, 2013 at 18:41

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

Jo · November 25, 2012 at 13:32

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

    Andrew · November 26, 2012 at 14:35

    Glad it helped you out!

Humphrey · November 5, 2012 at 13:02

wow this works like charm, great tip…

Vaishnavi Kadu · September 14, 2012 at 10:10

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 · September 11, 2012 at 21:43

THANK YOU!!!

Rick Gladwin · July 13, 2012 at 21:04

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.

    Andrew · September 17, 2012 at 13:59

    Thanks for the extra step Rick!

kk · July 2, 2012 at 10:06

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

Renne · June 27, 2012 at 12:16

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

David Brandon · June 20, 2012 at 19:26

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.

    HSMoore Webmaster · July 2, 2012 at 13:14

    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.

Gavin · June 13, 2012 at 19:43

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

    HSMoore Webmaster · June 15, 2012 at 08:42

    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.

      Gavin · June 15, 2012 at 17:10

      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 · May 21, 2012 at 09:26

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?

    HSMoore Webmaster · May 22, 2012 at 14:00

    @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?

Elizabeth · May 11, 2012 at 15:30

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

seth · April 25, 2012 at 20:35

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

Duane Charles · April 11, 2012 at 04:25

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 · March 1, 2012 at 19:06

Thanks a lot,
Saves my time alot…

Nicole Hanusek · February 24, 2012 at 21:55

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

David Carruth · January 14, 2012 at 22:48

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

Peter Reguin · December 2, 2011 at 04:10

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 · November 24, 2011 at 11:48

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 😉

Jamus · November 5, 2011 at 03:26

Cheers for that

wordman · September 29, 2011 at 00:05

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 · September 10, 2011 at 22:16

Thank you so much. *offers a cherry lifesaver*

Jerry · September 5, 2011 at 13:17

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.

    Andrew · September 5, 2011 at 13:27

    @Jerry, thank you for your post, I’m sure it will help many others that find this page.

kalar · August 29, 2011 at 20:09

Thanks a lot. You just save my butt.

Marc · August 7, 2011 at 02:40

Thank you for saving me a lot of time!

GBP · July 25, 2011 at 04:40

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

shiva · July 12, 2011 at 18:39

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

    Andrew · July 13, 2011 at 08:54

    “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”

      Charley · August 20, 2011 at 05:39

      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’;

        Andrew · August 22, 2011 at 10:00

        Thanks Charley!

a bear · June 21, 2011 at 16:01

Thank you. I was struggling and this fixed it!

hassan · June 2, 2011 at 06:51

Great Article i like it so nice of you
Thanks Alot it Save My 3 to 4 days i done it!!!!!!!!!!!

Dijo David · May 26, 2011 at 09:04

Thanks a lot!! Brilliant!

Shyamal · May 6, 2011 at 21:11

It’s absolute true. This is works well with most of the CSV file. Thanks for your post

melanie · May 5, 2011 at 21:53

Like all the others – thank you. You saved me time as well.

ct · May 1, 2011 at 06:27

why the data is not insert into my database??it just have the column name…

    Andrew · May 1, 2011 at 13:11

    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.

Udana · April 26, 2011 at 12:02

thanks, you saved a lot of my time.. is this the permanent solution for this problem?

    Andrew · April 26, 2011 at 14:33

    Yes, this is the way you should do it as it better handles your data.

Modesto Betancourth · April 23, 2011 at 16:26

Thanx a lot for this post, a year later it’s still valid, saved me a lot of time and a big headache

Kudos

    Andrew · April 23, 2011 at 20:36

    Glad it helped you!
    Take a look at some of our other posts if you haven’t already!

Andrew · April 14, 2011 at 20:38

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.

Conor · March 25, 2011 at 03:39

Many thanks Andrew (from Ireland!!)

    Andrew · March 25, 2011 at 15:01

    No Problem Conor!

Patrick · March 22, 2011 at 01:03

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.

Patrick · March 22, 2011 at 00:52

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!

    Andrew · March 22, 2011 at 10:23

    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?

Farhan · March 9, 2011 at 22:19

Thanks this helped me save hours

Bernie Dodge · March 9, 2011 at 01:41

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!

    Kika · September 17, 2011 at 16:49

    Perfect! my files also came from FileMaker and the “r” is what makes it work

    Joao · May 14, 2013 at 19:13

    Great!! Thank you both for this time saver tip

Adam · March 8, 2011 at 12:42

Ahh, thanks Andrew, hopefully this will work!

Mel's Video Of The Day! · February 27, 2011 at 21:33

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.

Ramachadnran · February 18, 2011 at 11:00

Really nice thing it helped to come out with the head ache of 3 hours . I want to thank ypu from my heart

    Andrew · February 18, 2011 at 11:33

    Glad to hear it helped you out!

Ashis · February 10, 2011 at 21:58

Thanks !!

It works for me :)) yey

thomas · February 9, 2011 at 11:39

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

    Andrew · February 9, 2011 at 12:31

    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.

Res · February 6, 2011 at 12:20

Awesome! THANK YOU

Winston · January 25, 2011 at 20:45

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

    Andrew · February 9, 2011 at 12:34

    No problem!

Elaine · January 24, 2011 at 03:41

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.

Riverrat · January 21, 2011 at 07:38

Thanks this saved me days of agro.
Little note for others phpmyadmin throws an error message but it still worked!

kuldeep Vyas · December 31, 2010 at 07:45

Thanks it works fine.

Steve · December 11, 2010 at 23:24

Thanks dude – this just saved me!.

Aaron · December 1, 2010 at 05:31

Thank you so much, saved me!!!

geekmate · November 29, 2010 at 17:09

thank you sooooo much!!! this has saved me so many processes!

Tom · November 9, 2010 at 19:40

Good tip. Saved me a ton of time. Thanks!

Harry Seager · October 20, 2010 at 21:15

Hi, I am now getting the following error: #1148 – The used command is not allowed with this MySQL version.

Thanks.

    Andrew · February 9, 2011 at 12:37

    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.

      Carl · December 23, 2012 at 19:59

      How do you get Excel to save all the values enclosed in ” “????

      Every time I export in this NEW Office crap I get nothing of the sort.

      Do I have to manually go in and add them?
      this is driving me nuts, shouldn’t this be easier, it is the year 2012 right?

        Andrew · December 29, 2012 at 15:25

        You should just export as CSV and then deal with it afterwards using PHPMyAdmin. Please show an example if you are still battling with this one.

      eastaixee · December 25, 2012 at 17:20

      i try but still error 1148.
      i use ms. excell in my csv.file
      so how i to enclose all the values in ” and end lines as n?

        Andrew · December 29, 2012 at 15:24

        You do this using the PHPMyAdmin import advanced panel as per the image in this post’s example.

guy · September 14, 2010 at 02:53

thanks this worked for me

Karensanity · September 10, 2010 at 15:10

Thank You. This saved me a lot of time.

Importing CSV files from Excel in MySQL using PhpMyAdmin « Chris Robinson · June 8, 2011 at 20:56

[…] 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 […]

Leave a Reply

Your email address will not be published. Required fields are marked *