When I export Chinese characters from Oracle forms to Excel, they are not Chinese anymore

Question

I have problem with Chinese characters when I export them from Oracle forms 10g to Excel on Windows 7. Although they look like Chinese but they are not Chinese characters. Take this into consideration that I have already changed the language of my computer to Chinese and restarted my computer. I use owa_sylk utility and call the excel report like:

    v_url := 'http://....../excel_reports.rep?sqlString=' ||
       v_last_query      || 
       '&font_name='     ||       
       'Arial Unicode MS'||
       '&show_null_as='  ||
       ' '               ; 

web.show_document(v_url,'_self');

Here you can see what it looks like:

Interestingly, when I change the language of my computer to English, this column is empty. Besides, I realized that if I open the file with a text editor then it has the right Chinese word, but when we open it with Excel we have problem.

Does anyone has a clue?

Thanks

Answer 1

Yes, the problem comes from different encodings. If DB uses UTF-8 and you need to send ASCII to Excel, you can convert data right inside the owa_sylk. Use function convert.

For ex. in function owa_sylk.print_rows change p( line ); on p(convert(line, 'ZHS32GB18030','AL32UTF8')); Where 'ZHS32GB18030' is one of Chinese ASCII and 'AL32UTF8' - UTF-8.

To choose encoding parameters use Appendix A

You can also do *SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'CHARACTERSET'* to see all the supported encodings.

Answer 2

This is a character encoding issue. What you need to make sure is that all tools in the whole chain (database, web service, Excel, text editor and web browser) use the same character encoding.

Changing your language can help here but a better approach is to nail the encoding down for each part of the chain.

The web browser, for example, will prefer the encoding supplied by the web server over the OS's language settings.

See this question how to set UTF-8 encoding (which can properly display Chinese in any form) for Oracle: export utf-8 data to text file with oracle sql developer

I'm not sure how to set the encoding for owa_sylk, you will have to check the documentation (couldn't find any, though). If you can't find anything, ask a question here or use a different tool.

So you need to find out who executes excel_reports.rep and configure that correctly. Use a developer tool of your web browser and check the "charset" or "encoding" of the page.

The problems in Excel are based on the file format which you feed into it. Excel (.xls and .xlsx files) files are Unicode safe, .csv isn't. So if you can read the file in your text editor, chances are that this is a non-Excel file format which Excel can parse but it doesn't contain the necessary encoding information.

If you were able to generate a UTF-8 encoded file with the steps above, you can load the file by using "Choose 65001: Unicode (UTF-8) from the drop-down list that appears next to File origin." in the "Text Import Wizard" (source)