Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    4

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

    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. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm pretty sure that the documentation gives this one away!
    Quote 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

  4. #4
    Join Date
    Nov 2007
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •