Excel – csv file to phptheadmin import error

csvmicrosoft excelMySQLphpmyadmin

here is the pattern of my csv file

--------------------------------------------------------------
|   col1    |   col2   |           |   col4   |            |  
--------------------------------------------------------------
|   col1    |   col2   |           |   col4   |            |   
--------------------------------------------------------------
|   col1    |   col2   |           |   col4   |            |   
--------------------------------------------------------------
|   col1    |   col2   |           |   col4   |            |   
--------------------------------------------------------------
|   col1    |   col2   |           |   col4   |            |   
--------------------------------------------------------------
|   col1    |   col2   |           |   col4   |            |  
--------------------------------------------------------------

i have something like this, empty columns on the table in .csv file in excel 2010.
empty column has default data set in phpmyadmin.
while importing it's throwing Invalid column count in CSV input on line 1.

how can i solve this?

thanks in advance.!

Edit:

here is the sample of my csv file

-----------------------------------------------------------------------------
|   9    |   6   |           |   Yes, Capacitive   |   3.5 Inches   |      |
-----------------------------------------------------------------------------
|   9    |   6   |           |   Yes, Capacitive   |   4.5 Inches   |      |  
-----------------------------------------------------------------------------
|   9    |   6   |           |   Yes, Capacitive   |   4.5 Inches   |      |   
-----------------------------------------------------------------------------
|   9    |   6   |           |   Yes, Capacitive   |   3.5 Inches   |      |   
-----------------------------------------------------------------------------
|   9    |   6   |           |   Yes, Capacitive   |   4.5 Inches   |      |  
-----------------------------------------------------------------------------
|   9    |   6   |           |   Yes, Capacitive   |    4 Inches    |      | 
-----------------------------------------------------------------------------

Best Answer

The formatting is the problem in this case. The reason you get an import error is that the parser cannot understand the data as a structured comma separated list. This problem is the result of two parts

  • Quoting is not consistent. Some elements are opened with quote, but not closed, hence the quote is expanded into the new field

  • The delimeter is existing also in the data. When you have a comma seperator and a comma is used in the text variable and the quotes are not consistent then the parser may mistake a textual commma for a structural comma.

Two suggestions to solve the problem:

  • Clean up the quotation marks: all opening quotation marks must have a corresponding closing quotation marks.

  • Use a delimiter which cannot be found in the data. This way the parser will not confuse the data with the structure of the CSV.

Related Question