Invalid field count in CSV input on line XX in phpmyadmin?

1. I have a 16MB CSV file with 50,000 lines that I want to import into my database.
2. I go to the PHPMyAdmin-Interface, choose IMPORT and set the correct options.
3. The import fails with the error Invalid field count in CSV input on line 24,333.
4. When I look into the CSV file via Notepad++ and turn on the Option View -> Show Symbols -> Show all Characters everything in this line looks absolutely okay, means like exactly the same as in the lines before that were imported correctly.

How can I find the problem that the import is complaining about?

I cannot provide a sample of the data because it contains sensitive information.

3 Answers

To find these cases, look for any characters that may be interpreted as a delimiter, or a complex or unclosed quote. Remember that if there is a newline as part of a field value earlier in the file, your line numbering may be off, so the line you are looking at may not be the one that the import is complaining about.

Also try importing the CSV file into Excel or OpenOffice to see if those applications treat the file as you expect. That will give you a nice visual way to identify where the problem is.

A solution for me was to copy/paste all of the contents from excel, create a new excel sheet and then paste all of the content.

Then, save it, upload, etc.

I don't know why this worked for me but it did and it's something you can try.

If you are importing a .csv file in an excising table then first remove the id (Primary key) of the table then import the .csv file and after importing add the id back in the table as a Primary key.

If you are importing in a new table then first import the .csv file before creating a new table and after importing .csv file rename the imported table as per requirements.