homepage Welcome to WebmasterWorld Guest from 54.196.199.117
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
importing a csv file into mysql via phpmyadmin
surrealillusions




msg:4185907
 11:35 am on Aug 12, 2010 (gmt 0)

Hi all,

Got a problem with importing a CSV file into a database.

The database has 4 columns.
id (auto-increment)
job
user
timestamp

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...

:)

 

Frank_Rizzo




msg:4185911
 11:55 am on Aug 12, 2010 (gmt 0)

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 ....

surrealillusions




msg:4185929
 12:36 pm on Aug 12, 2010 (gmt 0)

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?

rocknbil




msg:4186081
 5:53 pm on Aug 12, 2010 (gmt 0)

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:

id,job,user,timestamp
"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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved