Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2015
    Posts
    10

    Unanswered: db2 v.9.7 not identifying non english character during loading from excel to db2 tabl

    Hi All ,

    I am using db v.9.7 as database. Source for data is excel files which we load in db2 tables.

    In excel file we have few non-english character which during loading comes up as non identified (shown as question mark) . I cannot play anything with that question mark..like trip..replace etc.

    Please suggest me way how to identify and convert that non english character into english so that it loads properly in db2 OR how should i change my db2 config settings so that it can recognize that character and load as it is.

    The data code set we use is UTF-8,
    country region code - 1,Territory =US .Please find the screenshot attached for database config settings.

    I am new to db2 and have very little experience on these types of stuff.

    Please advice. Thanks!
    Attached Thumbnails Attached Thumbnails DB config screenshot.jpg  

  2. #2
    Join Date
    Jun 2015
    Posts
    10

    reply

    Hi All ,

    Please let me know if you need any more inputs from me for understanding the issue. Thanks!

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    any specific character in excel that does not get loaded
    display the column in hex in db2 to see the character/ascii code
    how is connection defined between excel and db2
    how data is loaded
    could you use import instead ? local - remote
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    what application do you use for loading from these excel files to db2?
    Regards,
    Mark.

  5. #5
    Join Date
    Jun 2015
    Posts
    10

    Reply :

    Quote Originally Posted by przytula_guy View Post
    any specific character in excel that does not get loaded
    display the column in hex in db2 to see the character/ascii code
    how is connection defined between excel and db2
    how data is loaded
    could you use import instead ? local - remote
    Hi All ,

    Thanks for expressing interest in my issue. Below are few example for which we have special character in excel and when we load them into database
    we have that issue. The character is É (above E) and Ó (above O) .

    I am using db2 load utility in control center to load data form excel to db2 table. We convert the excel xls file into csv and load it.

    Examples:-

    1) AMÉRICA MÓVIL, S.A.B. DE C.V.
    2) AMÉRICA MÓVIL

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    first : cc is not supported anymore - other tools like datastudio should be used
    what is the hex display showing for this row/column ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Jun 2015
    Posts
    10
    Hi ,

    first : cc is not supported anymore - other tools like datastudio should be used >>>> i am using db2 9.7 control center. Is datastudio is free or licenced tool? I can use any IBM tools as they wont cost my project. Hence am using default control center.


    what is the hex display showing for this row/column ?>> can you please tell me how to get hex code

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    datastudio is for free and can be downloaded from ibm..
    select hex(col1) from tablename ...
    I did a test similar to this problem and character gets correctly loaded

    (0)[db2inst1@dlx00003 work]$ db2 "select * from nls"

    COL1
    ----------
    É

    1 record(s) selected.

    (0)[db2inst1@dlx00003 work]$ db2 "select hex(col1) from nls"

    1
    --------------------
    C3892020202020202020

    1 record(s) selected.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    What's the codepage of your csv file?
    If UTF-8, try the following modifier of the db2 load utility:
    Code:
    modified by codepage=1208
    Regards,
    Mark.

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    before asking the details of modifiers : read
    http://www-01.ibm.com/support/knowle...2-4-83&lang=en
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Jun 2015
    Posts
    10
    Hi ,

    did you tried loading that into table from excel?

    So you are trying to say that it is because of control center? if we use data-stage we may not face any problem?

  12. #12
    Join Date
    Jun 2015
    Posts
    10
    Hi Mark,
    yes it is UTF-8.
    Please refer to attch screenshot for having db config details.

    Where to use this : modified by codepage=1208

  13. #13
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated in previous update :
    before asking the details of modifiers : read
    http://www-01.ibm.com/support/knowle...2-4-83&lang=en
    the screenshot is from db : not client
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  14. #14
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by max1234 View Post
    Where to use this : modified by codepage=1208
    Look at the LOAD command description.
    Regards,
    Mark.

Posting Permissions

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