Invalid column count in CSV input on line 1 Error

Question

I'm trying to get a ".csv" file onto an SQL database with phpMyAdmin. However, whenever I import it, I get the error: Invalid column count in CSV input on line 1. I've spent all day playing around with different options to try and get it to work but with no avail. There are exactly 47 columns in my .csv file. I have created 47 columns in my SQL table. The names however aren't exactly the same as the ones in the file. Whenever I import, it keeps giving me that error. Any help would be greatly appreciated! ~Carpetfizz One thing I thought might be causing the problem was that the first column isn't named anything in my excel document. Could this be causing an issue?

EDIT 12:30AM: phpMyAdmin is already the latest version available, via (apt-get install phpmyadmin) (phpmyadmin is already latest version)

Here is the .csv file, if that helps.

Solution 1

Fixed! I basically just selected "Import" without even making a table myself. phpMyAdmin created the table for me, with all the right column names, from the original document.

Solution 2

If your DB table already exists and you do NOT want to include all the table's columns in your CSV file, then when you run PHP Admin Import, you'll need fill in the Column Names field in the Format-Specific Options for CSV - Shown here at the bottom of the following screenshot.

In summary:

• Choose a CSV file
• Set the Format to CSV
• Fill in the Column Names field with the names of the columns in your CSV
• If your CSV file has the column names listed in row 1, set "Skip this number of queries (for SQL) or lines (for other formats), starting from the first one" to 1

Solution 3

I got the same error when importing a .csv file using phpMyAdmin.

Solution to my problem was that my computer saved the .csv file with ; (semi-colon) as delimiter instead of , (commas).

In the Format-Specific Options you can however chose "columns separated:" and select ; instead of , (comma).

In order to see what your computer stores the file in, open the .csv file in an text editor.

Solution 4

You will need to skip first row (where column names are defined) and you will need to check which "character" is separating cells (usually is ,, but in my case was ;)

Bellow is the picture of my import:

Solution 5

This is actually pretty simple to fix, I originally wrote about the fix ~10 years ago over here https://ao.ms/phpmyadmin-invalid-field-count-in-csv-input-on-line-1/

What you want to do is change "Fields terminated by" from ";" to "," and then make sure that the "Use LOCAL keyword" is selected.

View more solutions

Comments

[kgui]

Also, I ran into the same problem you had. I fixed it just as you did but, you left out an important detail. When you click "Import", make sure a table in the database is NOT selected. The database name SHOULD be selected instead.

[Ans]

should mark as correct answer. You need to mention the column names.

[Sébastien Gicquel]

Best solution for me if you have a different number of columns in the .CSV file.

[BitBug]

Thank you! All my searching and this is the only right answer I found. This should be marked as correct answer

[E. Villiger]

@Crt: your comment should be an answer, if not the accepted one!

[Eoin]

And if the table has the same name then you can click on that table name, go to operations, copy table, and just append a number to make it different then drop the original table.

[JimB814]

Excellent suggestion! Opening my .csv file in a text editor showed that my file had 12 additional columns, which was triggering the Invalid column count error.

[michaelmcgurk]

THIS is a super underrated answer. Fixed my problem. Thank you!! :-)

[anpami]

"#1148 - The used command is not allowed with this MySQL version", it says (my MySQL version seems to be "5.7.29-32-log").

[ArtFranco]

Thanks! I've actually combined all the answers: skip number of queries, add the columns names and changed the "Column separator" value to ; (semi-colon). Worked great!

[Bhavin Thummar]

Thank you. In my case I have need to ignore first column.