Invalid field count in CSV input on line 1 phpmyadmin

Question

I have read many threads but can't find the right specific answer. I get this error message when I try to import additional data into an existing table. The field names are all aligned correctly, but not every row has data in every field. For example, although I have a field named middle_name, not every row has a middle name in it. During the import process, is this blank field not counted as a field and thus throwing off the field count?

I have managed to get most of the data to import by making sure I had a blank column to allow for the auto-increment of ID, as well as leaving the header row in the file but choosing 1 row to skip on the import.

Now the problem is the last row won't import - get error message Invalid format of CSV input on line 19. When I copy the file to Text Wrangler, the last row ends with ,,,,,. This accounts for the last 5 columns which are blank. I need to know what the trick is to get the last row to import.

Here are the settings I have been using:

Answer 1

I’ve had similar problems (with a tab-separated file) after upgrading from an ancient version of phpMyAdmin. The following points might be helpful:

• phpMyAdmin must have he correct number of columns. In older versions of phpMyAdmin you could get away with not supplying empty values for columns at the end of the row, but this is no longer the case.
• If you export an Excel file to text and columns at the start or end of rows are completely empty, Excel will not export blanks for those rows. You need to put something in, or leave blank then edit the resulting file in a text editor with regular expressions, e.g. to add a blank first row, search for ^ and replace with , (CSV file) or \t (tab file); to add two columns to the end search for $ and replace with ,, or \t\t etc.
• Add a blank line to the bottom of the file to avoid the error message referring to the last line of data. This seems to be a bug that has been fixed in newer versions.
• Whilst in the text editor, also check the file encoding as Excel sometimes saves as things like UTF-16 with BOM which phpMyAdmin doesn’t like.

Answer 2

I saw the same error while trying to import a csv file with 20,000 rows into a custom table in Drupal 7 using phpmyadmin. My csv file didn't have headers and had one column with many blanks in it.

What worked for me: I copied the data from Excel into Notepad (a Windows plain text editor) and then back into a new Excel spreadsheet and re-saved it as a csv. I didn't have to add headers and left the blank rows blank. All went fine after I did that.