MySQL Forums
Forum List  »  Newbie

import csv/txt to database
Posted by: khanh khanh
Date: January 04, 2012 10:37PM

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.

Options: ReplyQuote


Subject
Written By
Posted
import csv/txt to database
January 04, 2012 10:37PM
January 05, 2012 01:12AM
January 05, 2012 01:18AM
January 05, 2012 03:38AM
January 05, 2012 03:43AM
January 05, 2012 05:14AM
January 07, 2012 04:40AM
January 05, 2012 09:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.