4 Replies Latest reply: Nov 27, 2012 4:18 PM by alphasow RSS

    Getting Chinese characters from .XLS file to oracle 11g

    user10636796
      Hi, Everyone,

      I have a .xls(97-2003 version) as a source file which has chinese characters.

      I am trying to save it as a csv and them import them to oracle thru sql loader but the chinese character get transformed into ?????? in the csv file as a result in oracle they also look the same as ?????.

      I also tried saving them into unicode text and separated by "pipe delimiter " but when it comes to oracle the chinese characters turn into gebarish.(can't read them).

      could anyone help me to solve this problem so that i can load the chinese characters into the database also?

      oracle database is 11.1.0.6
      windows version is windows server 2003

      Thanks in advance
        • 1. Re: Getting Chinese characters from .XLS file to oracle 11g
          Lubiez Jean-Valentin
          Hello,

          I am trying to save it as a csv and them import them to oracle thru sql loader but the chinese character get transformed into ?????? in the csv file as a result in oracle they also look the same as ?????.
          If the characters are converted before being imported you couldn't get them back after the import. They are lost.
          I also tried saving them into unicode text and separated by "pipe delimiter " but when it comes to oracle the chinese characters turn into gebarish.
          It's recommended to use AL32UTF8 Character Set on the Target Database. You may check it with the following query.
          select * from v$nls_parameters where parameter like 'NLS_CHARACTERSET';
          About conversion problem, the followin Notes from MOS give good advices:
          Character Sets & Conversion - Frequently Asked Questions [ID 227330.1] 
          
          AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]
          The Chapter C.3) Configuring your Windows client to be an UTF-8 (Unicode) client. of the Note *788156.1* may be useful.


          Hope this help.
          Best regards,
          Jean-Valentin
          • 2. Re: Getting Chinese characters from .XLS file to oracle 11g
            Srini Chavali-Oracle
            >
            I am trying to save it as a csv and them import them to oracle thru sql loader but the chinese character get transformed into ?????? in the csv file as a result in oracle they also look the same as ?????.
            >

            Pl identify the version of Excel being used, along with the encoding scheme used to create/save the CSV file.

            >
            I also tried saving them into unicode text and separated by "pipe delimiter " but when it comes to oracle the chinese characters turn into gebarish.(can't read them).
            >

            What is the characterset of the database ? Pl provide details on how the data is being loaded into the database. Where/how do you see the gibberish ?

            HTH
            Srini
            • 3. Re: Getting Chinese characters from .XLS file to oracle 11g
              orafad
              It seems the issue is with saving a different format file from Excel.

              I did a simple test, "save as...", showing:
              - with .csv format the acp is used and non-acp code characters is converted to a '?' sign.
              - with Unicode text .txt format unicode (no surprise, right) is used, with tab as default delimiter it seems. Different language characters, e.g. CJK graphs, input with Symbol tool are kept. Note the encoding used (probably UTF-16).

              Verify the .csv or .txt file content after save from Excel via Notepad or other tool. With Notepad, make sure you use Encoding properly in the file open dialog.

              So, you have two choices. Either use proper ACP, i.e. change Windows locale so that it matches source file's code page, or use a different format, such as 'Unicode Text' when converting the file via Excel.


              Main point - Make sure you first verify the source file with which you feed sql*loader!

              Edited by: orafad on Jul 26, 2011 8:14 PM
              • 4. Re: Getting Chinese characters from .XLS file to oracle 11g
                alphasow
                Hi

                Did you ever this to work for you? I am trying to do exactly the same thing but I am not ale to get the file save as .csv with the chinese charters. if someone did get this to work ok , your help would be appreciated.
                thanks
                alpha