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!!

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

Johann,

it works now, thx.