Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    14

    Unanswered: LOAD problem with coldel

    Hi,

    I tried to load a delimited text file to a table in DB2 v8. Data includes some double-quotes. For example,

    123!"Hello"!"Wow" it is not ok!how "great"!oh "ohhhh" oh!that is all

    with coldel=!

    after loading such text file, and then select * from the table.

    col1 col2 col3 col4 col5 col6
    ---------------------------------------------------------------------------
    123 Hello Wow how "great" oh "ohhhh" oh that is all

    Double-quotes in col2 and col3 disappeared!!!

    And then i tried to use escape character "\"

    123!\"Hello\"!\"Wow\" it is not ok!how \"great\"!oh \"ohhhh\" oh!that is all

    col1 col2 col3 col4 col5 col6
    -----------------------------------------------------------------------------------------
    123 \"Hello\" \"Wow\" it is not ok how \"great\" oh \"ohhhh\" oh that is all

    I suspect that if a field start with <!">, the system treat <"> as coldel instead of <!>. if a field start with <!> only, it works normally and double-quotes can be treated as part of the data.

    Does any of u know how to fix this problem? Thx a lot!!

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: LOAD problem with coldel

    Hi db2worker,

    option chardel is your friend, not coldel.

    By default, chardel is ", so the system considers any double quotes as string delimiters.

    You may want to set chardel to some character that is just not going to be used in your data.
    If you find that annoying, feel free to join in on my design change request with IBM (I want to be able to unset chardel, so it is not used at all). Got no response so far, but will continue to press on.

    Johann

    Originally posted by db2worker
    Hi,

    I tried to load a delimited text file to a table in DB2 v8. Data includes some double-quotes. For example,

    123!"Hello"!"Wow" it is not ok!how "great"!oh "ohhhh" oh!that is all

    with coldel=!

    after loading such text file, and then select * from the table.

    col1 col2 col3 col4 col5 col6
    ---------------------------------------------------------------------------
    123 Hello Wow how "great" oh "ohhhh" oh that is all

    Double-quotes in col2 and col3 disappeared!!!

    And then i tried to use escape character "\"

    123!\"Hello\"!\"Wow\" it is not ok!how \"great\"!oh \"ohhhh\" oh!that is all

    col1 col2 col3 col4 col5 col6
    -----------------------------------------------------------------------------------------
    123 \"Hello\" \"Wow\" it is not ok how \"great\" oh \"ohhhh\" oh that is all

    I suspect that if a field start with <!">, the system treat <"> as coldel instead of <!>. if a field start with <!> only, it works normally and double-quotes can be treated as part of the data.

    Does any of u know how to fix this problem? Thx a lot!!

  3. #3
    Join Date
    Jul 2003
    Posts
    14
    Johann,

    it works now, thx.

Posting Permissions

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