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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This is actually pretty simple to fix  I originally wrote about the fix  10 years ago over here https   ao gl 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[php] Invalid column count in CSV input on line 1 Error

Examples related to php

Examples related to mysql

Examples related to excel

Examples related to csv