I was trying to load an Excel sheet to Mysql 5.0, I did this:

mysql> create database 'mydb';
mysql> use 'mydb';
mysql> create table 'mytable'(c1 INTEGER NOT NULL,c2 INTEGER);
mysql> load data infile 'path\file.csv' into table mytable;

from here the command line give me many errors, for example:

row 1 doesnot contain for all columns.

So, if I fix that, other error appear, and again other error, all of then about something wrong.

A good idea??
thanks

make sure the csv has only 2 items per line, then try

LOAD DATA LOCAL INFILE 'path\file.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(c1, c2);

thanks, but

doing that the command line giveme this error:

>Incorrect integer value: ' ■7' for column 'c1' at row 1

and the value is integer....

probably not.
' ■7' is a very strange integer.

If it is about a very large amount of data that is contaminated with a lot of these kind of garbage lines, you might want to do the following:
- Create the import table with only varchar columns.
- Import the data. It should go well, since it is not checking it.
- use the dataconversion functions of your database to get as much data out of the import and copy those things over to the destination
( so for example cast column1 to int and verify that the result is bigger then 0 )
- delete the rows in the import table that where possible to convert.

At the end of this process you are left with a clean final table and a temporary import table containing only garbage.
Perhaps you can make sense of what is left in there, and continue the data extraction process.

I did exactly this to import 230.000 user accounts. Of those around 10k had something strange in them, like a very (unexpectedly) long password or email adress, a streetadress with a quote character in it, things left NULL that where supposed to be not-null, etc etc.
Realy, when poughing through so much data you will encounter a lot of strange things.

Good luck!

This post has been edited by Trogdor: 31 Jul, 2008 - 02:17 AM