[lanesbalik]

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

[aschk]

By the looks of the error

ERROR 1292 (22007): Incorrect datetime value: '"2007-07-31 00:25:12".....

It appears it's trying to read in ' "2007-07-31 00:25:12" ' , and as you can see the bit inside the ' is enclosed in ", thus not making it a datetime. However, what I find interesting is that you have specified the optionally enclosed by '"' parameter.

[Pat Phelan]

I'm pretty sure that the documentation gives this one away!

Originally Posted by MySQL 5.0 documentation

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values from columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM):

If you use the ENCLOSED BY option, you are explicitly telling MySQL that the data in the input file a string. Quoting the string in code is different than quoting the string in an input file.

-PatP

[lanesbalik]

hi aschk, Pat Phelan,

if i remove the OPTIONALLY ENCLOSED BY '"' then MySQL engine will give me this error message :
' for column 'LAST_INSERT_DATE' at row 1alue: '"2007-07-31 00:25:12"..


Pat Phelan wrote:
............. you are explicitly telling MySQL that the data in the input file a string. Quoting the string in code is different than quoting the string in an input file.....

then the question is, how to import the datetime value??

since the mysqladmin export the datetime value using quote eq. "2007-07-31 00:25:12"
and also, can you take a look that the insert sql engine in MySQL can accept quote eq. insert into mytable (mydatetime) values ("2007-07-31 00:25:12");

so, am i right to say that insert engine is diffrent from load data infile engine??..

thanks.

rgds,
lanes