MySQL Forums
Forum List  »  IBM DB2

load data infile - fails to load my del (ascii) file
Posted by: lanes balik
Date: November 06, 2007 04:26AM

hi all,


right now i'm trying to migrate from db2 running under linux to mysql v5.1.

i manage to export out the db2 structure & data into a del (ascii) file.
but when i try to load the data from the del file to mysql table, it generate an error.

below is the load data infile syntax i use =
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';


and below is the sample layout of del file (in the above case is TABLE01.del) =
1,0,"2007-07-31 00:25:12"
2,0,"2007-07-31 14:09:00"
3,0,"2007-07-31 00:00:00"
4,0,"2007-07-31 00:00:00"
5,0,"2007-07-31 00:00:00"


and below is the TABLE01 structure =
FieldName, Type, Null, Primary
------------------------------------------------------
MY_TABLEID, int(11), NO, PRIMARY KEY
CNTS, int(11), NO, NOT PRIMARY KEY
INSERT_DATE, datetime, NO, PRIMARY KEY


the error i encounter is =
ERROR 1292 (22007): Incorrect datetime value: '"2007-07-31 00:25:12".....


i do some trick by trying to remove the qoute sign from "2007-07-31 00:25:12" so the del file become =
1,0,2007-07-31 00:25:12
2,0,2007-07-31 14:09:00
3,0,2007-07-31 00:00:00
4,0,2007-07-31 00:00:00
5,0,2007-07-31 00:00:00

And i do load data infile again with same command =
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
and it was successful.


to me it is very strange, since i can do a sql insert using "2007-07-31 00:25:12" for example: insert into TABLE01 values (11, 22, "2007-07-31 00:25:12") successfully without any error!!!
in fact, when i use a MySQL Administrator tools, and i export out the data from TABLE01 into CSV, then i found that the structure is exactly the same with del file generated by db2.

do i make any mistake??

or do i need to initialize something before i call
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ...?????


Any help is appreciated.
Thank you.


Regards,
Lanes

Options: ReplyQuote


Subject
Views
Written By
Posted
load data infile - fails to load my del (ascii) file
11630
November 06, 2007 04:26AM


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.