|importing a csv file into mysql via phpmyadmin|
Got a problem with importing a CSV file into a database.
The database has 4 columns.
in that order, from left to right. The CSV file has only 2 columns - job and user.
Have tried creating all 4 columns in the csv file, but no success, have tried different seperators (, ; [tab] etc..) but nothing.
All get the same error:
Invalid field count in CSV input on line 1.
How do I get this file into the database? Theres about 2500 lines in the file, so I dont fancy typing it all out...
You could import it into a spreadsheet, add the two columns, save as csv and try that file.
Another method is to use php to read the csv file line by line and then
issue INSERT into ... fields ....
It was originally an .xslx file. Although I'm using openoffice to edit it as I'm on Linux.
Some of the text fields have highly likely got , " ' in, I'm guessing those will make a difference?
|Have tried creating all 4 columns in the csv file, but no success, have tried different seperators (, ; [tab] etc..) but nothing. |
Your error means just what it means, the columns phpMyAdmin are getting from the CSV ant not the same as what's in your database. A couple reasons why that may be . . . when you created the four columns, did you edit the CSV directly? This would require editing all 2500 lines, you can't just add the header.
Otherwise if you went back into Excel, did you create your own auto-increment numbers in the XLS->CSV? If you left it null, it should have worked out, being an auto_increment column, but not sure.
Last, you might be onto something here:
|Some of the text fields have highly likely got , " ' in, I'm guessing those will make a difference? |
Your error says "line 1" which is the header, so I'm guessing there's still a mismatch between your CSV and the database tables, but this **will** get you if you get past line 1. Since a CSV is comma separated, any commas in the fields will be interpreted as a field delimiter, giving the same error but on the line with the problem. This is why you need quote-qualified files, which will automatically escape inner quotes on export from Excel:
"1",:this is a \"job\" description, quote qualified","2010-08-12- 12:34:56"
So re-export it from Excel, make sure it's got 4 col's, select quote-qualified on export.