Member Avatar for 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

  • 2 Contributors
  • 1 Reply
  • 183 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Member #210412

Dani AI

Generated

A short diagnosis and practical steps for (and thanks to for the pointer):

The error text means MySQL is seeing the quote characters as part of the field value, so the loader is not stripping the enclosing quotes before attempting to parse the DATETIME. Changing a server “datetime format” by itself won’t remove stray characters from the input; the more likely issues are mismatched enclosure/escape settings, different line endings, or hidden bytes (BOM / non-ASCII quote characters) in the export.

Quick checks to run

  • Inspect the raw file for a BOM or non-ASCII quotes (Linux: hexdump -C filename | head to look for ef bb bf; Windows: open in Notepad++ and check Encoding).
  • Confirm line endings. If the file is Windows-style CRLF, use LINES TERMINATED BY '\r\n'; using '\n' on a CRLF file can leave a stray \r on the last field.
  • Load the file into a staging table with all columns as VARCHAR to see the exact stored bytes, then run SELECT HEX(col), LENGTH(col) to reveal hidden characters (leading 22 indicates a " byte).

Reliable fixes

  • Convert the export to plain UTF-8 without BOM and normalize line endings before loading.
  • Use a staging load that captures the raw field into a user variable and convert it with STR_TO_DATE (this trims quotes and parses explicitly). Example pattern:
LOAD DATA LOCAL INFILE 'path/to/file.del'
INTO TABLE staging
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(@a,@b,@rawdt)
SET col1 = CAST(@a AS UNSIGNED),
    col2 = CAST(@b AS UNSIGNED),
    insert_date = STR_TO_DATE(TRIM(BOTH '"' FROM @rawdt), '%Y-%m-%d %H:%i:%s');

If problems persist after these steps, the staging approach + HEX() inspection will always show exactly what characters are being passed to MySQL so the root cause can be corrected (BOM, CR, non-ASCII quotes, or an unexpected escape convention).

Member Avatar for Member #210412

set default datetime format as yours. then try it again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.