Reputation: 9149

Invalid column count in CSV input on line 1 Error

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)

Space in A1

Here is the .csv file, if that helps.

Upvotes: 64

Answers (14)

AO_

Reputation: 2874

This is actually pretty simple to fix, I originally wrote about the fix ~10 years ago over here https://ataiva.com/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.

Upvotes: 10

OuzoPower

Reputation: 278

I also faced this issue in phpMyAdmin. Using LOAD_DATA instead of LOAD solved the problem.

The columns were:

id,date,description,amount,payment_method

with id as an AUTO_INCREMENT primary key. Records were like this one:

,2019-01-01,Car insurance for year 2019,321.00,bank

The table encoding was utf8_general_ci and the file was in utf-8 encoded.

LOAD also failed when setting manually a value for column id.

Upvotes: 0

jtubre

Reputation: 699

The dumbest thing ever that will fix this error in Microsoft Excel (assuming you actually have everything else right):

Select your data and click "Borders All" in Excel (puts visual borders around your data) before saving the CSV. Sound pointless? I completely agree! However, it will fix this error. I use this trick at least three times a week.

Upvotes: 1

Brane

Reputation: 3339

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:

Upvotes: 9

Netwise Spain

Reputation: 1

The final column of my database (it's column F in the spreadsheet) is not used and therefore empty. When I imported the excel CSV file I got the "column count" error.

This is because excel was only saving the columns I use. A-E

Adding a 0 to the first row in F solved the problem, then I deleted it after upload was successful.

Hope this helps and saves someone else time and loss of hair :)

Upvotes: 0

P-S

Reputation: 4026

When facing errors with input files of any type, encoding issues are common.

A simple solution might be to open a new file, copy pasting your CSV text in it, then saving it as a new file.

Upvotes: 4

Bill Wilken

Reputation: 1

I had a similar problem with phpmyAdmin. The column count in the file to be imported matched the columns in the target database table. I tried importing files in both .csv and .ods format to no avail, getting a variety of errors including one arguing that the column count was wrong.

Both the .csv and .ods files were created with LibreOffice 5.204. Based on a bit of experience with import issues in years past, I decided to remake the files with the gnumeric spreadsheet, exporting the .ods in compliance with the "strict" format standard. Voila! No more import problem. While I haven't had time to investigate the issue further, I suspect that something has changed in the internal structure of LibreOffice's file output.

Upvotes: 0

Rule

Reputation: 21

Had the same problem and did two changes: (a) did not over-write existing data (not ideal if that is your intention but you can run a delete query beforehand), and (b) counted the columns and found that the csv had an empty column so it always pays to go back to your original work even though all 'seems' to look correct.

Upvotes: 2

Yazid Erman

Reputation: 1186

If the table was already created, and you were lazy enough not to specify the columns in the fields names input, then all you have to do is to select the empty columns at right to the file content and delete them.

Upvotes: 1

Nicholas

Reputation: 151

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.

Upvotes: 15

Daniel Flippance

Reputation: 7932

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

enter image description here

Upvotes: 110

Joseph Quaye

Reputation: 21

Your solution seems to assume you wish to create an entirely new table.

However if you want to add content to an already existing table, look up the structure of the table and note the number of columns (the id column, if you have one still counts->even though it may be auto increment/unique)

So if you table looks like this id Name Age Sex

make sure your excel table looks like A1 id B1 Name C1 Age D1 Sex

and now they both have 4 columns.

Also right under partial import, beside the skip the number of queries.... increase the number to skip the appropriate line. choosing 1 will skip automatically the first line. For those who may have headers in the excel files

Upvotes: 0

Cody O'Dell

Reputation: 181

I just had this issue and realized that there were empty columns being treated as columns with values, I saw this by opening my CSV in my text editor. To fix this, I opened my spreadsheet and deleted the columns after my last column, they looked completely empty but they were not. After I did this, the import worked perfectly.

Upvotes: 2

Carpetfizz

Reputation: 9149

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.

Upvotes: 45

Invalid column count in CSV input on line 1 Error

Answers (14)

Related Questions