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 -- View this message in context: http://www.nabble.com/load-data-infile---fails-to-load-my-db2-del-%28ascii%29-file-tf4762331.html#a13620281 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
