By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

load data infile - fails to load my db2 del (ascii) file

P: 2
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 =
Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
  2.  

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

and below is the TABLE01 structure =
Expand|Select|Wrap|Line Numbers
  1. FieldName, Type, Null, Primary
  2. ------------------------------------------------------
  3. MY_TABLEID, int(11), NO, PRIMARY KEY
  4. CNTS, int(11), NO, NOT PRIMARY KEY
  5. INSERT_DATE, datetime, NO, PRIMARY KEY
  6.  

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 =
Expand|Select|Wrap|Line Numbers
  1. 1,0,2007-07-31 00:25:12
  2. 2,0,2007-07-31 14:09:00
  3. 3,0,2007-07-31 00:00:00
  4. 4,0,2007-07-31 00:00:00
  5. 5,0,2007-07-31 00:00:00
  6.  
And i do load data infile again with same command =
Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
  2.  
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
Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ...?????
  2.  
Any help is appreciated.
Thank you.


Regards,
Lanes
Nov 7 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.