Hi all,
I am new to MySQL. I am using Python and mySQL on Ubuntu 10.04 (in virtual machine host WIN7 x64). I have an excel sheet with 42 fields, and a table. One of my record in the csv is similar to
"0","12","0","0","\N","\N","1","0","\N","0","1","0","12","3","some html formatted text","1","plain text","2002","Nov","1","\N","\N","0","0","\N","0","0","0","0","\N","\N","11","text","\N","\N","\N","text","\N","some plain text
<img src="upfiles/image.jpg">","0"
I used the following query to import these thing:
load data local infile 'test.csv' into table testing
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(field1, field2, ..., field 40, field42); // I do not import the fields which type is DATE and default value is now. I also ignore the auto-increment int unsigned field.
Query OK, 81 rows affected, 2424 warnings (0.01 sec)
Records: 81 Deleted: 0 Skipped: 0 Warnings: 2288
But, truly my test.csv only have 40 rows/records! (before import, I have cleared m table be using "delete from testing;").
After that, I investigate my table:
mysql> select * from testing;
After check everything, I saw that ONLY my 1st, 4th, 7th, ..., 37th, 40th record is correct (according to the csv file) (look like an arithmetic series). I lost all of my 2sd, 3rd, 5th,...,38, 39 record. Instead of these record, my data is filled with some strange record which is always 0 and NULL for all fields.
I delete the whole table again. Go to my csv file, then delete the first record and import again. This time, my correct record is 2sd, 5th, ...
I have also copy paste the csv file to a txt file and the same result. However, I ONCE time can import correctly all of my 40 records in txt file. Test it again, it wrong?!
I know that my explain is so complicate but hope someone has run into this error before know what wrong with my machine.
Regards,
Khanh
Edited 1 time(s). Last edit at 01/04/2012 10:43PM by khanh khanh.