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:

118 thoughts on “phpMyAdmin – Invalid field count in csv input on line 1”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  16. Pingback: Importing CSV files from Excel in MySQL using PhpMyAdmin « Chris Robinson

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

  17. Modesto Betancourth

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

    Kudos

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

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

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

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

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

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

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

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

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

Leave a Reply

Your email address will not be published.