[SOLVED] Importing CSV file exported from Excel Using phpMyAdmin

[toxictoad]

Hi,

 

I have an excel doc with some info I'm trying to import using phpMyAdmin but I keep getting an error. I've also tried exporting the few records that are in the db and re-importing them but I still get the same error?

 

ERROR: Invalid field count in CSV input on line 1.

 

Anyone know why?

 

Thanks

[mezise]

Please provide us with several first lines of imported CVS file.

[gluck]

It looks that your insert is off on the column count. 

[fenway]

Likely header info... but impossible to tell without seeing the first 10 lines.

[toxictoad]

ok I managed to import a few records but badly because my Film Title ends up messing with the index and I can't work out how to get round it.

 

This is the table structure

----------------------------------------------------------------------------------

filmID | title | genre | year | rating | country | link | director | plot | image | trailer

----------------------------------------------------------------------------------

This is what I've managed to import

 

----------------------------------------------------------------------------------------------------------------------------

1408,Horror,2007,3,USA,http://www.phatjoints.com/dvd/,Mikael Hafstram,To Come,Y,Y,

10000 BC,Adventure,2008,3,USA,,Roland Emmerich,To Come,Y,Y,

2001: Space Odessy,Sci-Fi,1968,3,USA,http://www.phatjoints.com/dvd/,Stanley Kubrick,To Come,Y,Y,

2010: The Year We Make Contact,Sci-Fi,1984,NA,USA,http://www.phatjoints.com/dvd/,Peter Hyams,To Come,Y,Y,

21 Grams,Drama,2003,2,USA,http://www.phatjoints.com/dvd/,Alejandro González Iñárritu,To Come,Y,Y,

28 Days Later,Horror,2002,4,UK,http://www.phatjoints.com/dvd/,Danny Boyle,To Come,Y,Y,

28 Weeks Later,Horror,2007,3,UK,http://www.phatjoints.com/dvd/,Juan Carlos Fresnadillo,To Come,Y,Y,

2 Fast 2 Furious,Action,2003,3,USA,http://www.phatjoints.com/dvd/,John Singleton,To Come,Y,Y,

2 Hitmen,Comedy,2007,1,USA,http://www.phatjoints.com/dvd/,Brett Scott Ermilio,To Come,Y,Y,

300,Action,2006,3,USA,http://www.phatjoints.com/dvd/,Zack Snyder,To Come,Y,Y,

30 Days of Night,Horror,2007,3,USA,http://www.phatjoints.com/dvd/,David Slade,To Come,Y,Y,

3:10 to Yuma,Western,2007,4,USA,http://www.phatjoints.com/dvd/,James Mangold,To Come,Y,Y,

88 Minutes,Drama,2007,3,USA,http://www.phatjoints.com/dvd/,Jon Avnet,To Come,Y,Y,

8 Mile,Drama,2002,3,USA,http://www.phatjoints.com/dvd/,Curtis Hanson,To Come,Y,Y,

Accepted,Comedy,2006,3,USA,http://www.phatjoints.com/dvd/,Steve Pink,To Come,Y,Y,

-------------------------------------------------------------------------------------------------------------------------

 

But this is what it looks like in the db

-------------------------------------------------------------------------------------------------------------------------

filmID   title   genre        year rating     country               link         director    plot    image    trailer

8   Drama 2002       3 US   http://www.phatjoint Curtis Hanson               To Come    Y     Y

88   Drama 2007     3       US   http://www.phatjoint Jon Avnet               To Come    Y        Y

3   Western      2007     4       US   http://www.phatjoint       James Mangold             To Come    Y   Y

30   Horror 2007       3 US   http://www.phatjoint David Slade               To Come      Y       Y

300 Action 2006     3       US   http://www.phatjoint       Zack Snyder             To Come      Y     Y

2   Comedy      2007     1       US http://www.phatjoint       Brett Scott Ermilio            To Come    Y     Y

28 Horror 2007     3         UK   http://www.phatjoint Juan Carlos Fresnadillo      To Come    Y     Y

21 Drama       2003     2         US http://www.phatjoint       Alejandro Gonz?lez Irritu      To Come    Y   Y

2010 Sci-Fi       1984     NA       US http://www.phatjoint       Peter Hyams               To Come  Y     Y

2001 Sci-Fi       1968     3         US   http://www.phatjoint       Stanley Kubrick               To Come    Y     Y

1408 Horror       2007     3       US   http://www.phatjoint       Mikael Hafstram               To Come    Y     Y

10000 Adventure  2008 3     US                                       Roland Emmerich           To Come    Y Y

 

What do I need to do so I can import everything into a blank table so that the auto_increment works from 1?

 

Thanks

[mezise]

So in your movies table firmID is auto_increment field and title is foreign key of titles table.

 

Remove commas from the end of lines (which represents last column) and at the begining of every line

add one comma (which represent first column that will be treated as NULL and will be auto incremented by

database engine).

 

[mezise]

Please remember: choosing CVS columns separator character you must be sure that the character is not present within content of any column.

[fenway]

Please remember: choosing CVS columns separator character you must be sure that the character is not present within content of any column.

Or just used OPTIONALLY ENCLOSED BY, or whatever it's called.

[toxictoad]

Thanks guys I'll see how I get on 

[toxictoad]

works now