Hi.

I am using MS Query to retrieve data into Excel from an Oracle database.  The data contains several different characters (such as degree and diameter symbols) which are stored as Unicode, but the query returns the same character for all of them.

In the MS Query window the special characters appear as upside down question marks; in Excel they show as white question marks in a black diamond.  The ASCII code of the character displayed in Excel is '63', and the UNICODE() value is 65533.  This isn't the character that is stored in the Oracle database.

Is there a way to correctly retrieve these characters? 

The ODBC driver is Oracle in OraClient11g_home1, version 11.02.00.01.  I've tried it with the 'Force SQL_WCHAR Support' setting on, but it didn't make a difference.  So, I've pretty much exhausted my knowledge now...

Thanks in advance,  Steve

Hi Steve,

As far as I know the function of MS Query didnt update anymore after the version of excel 2003. I suppose the issue might be caused by the different rules between Unicode and ASCII code, so you get different result in excel from Oracle database. I suggest you can try to use PowerQuery and PowerPivot. Their function are stronger than MS Query, and you can get latest function. Probably they can help you to solve your issue.

Hope its helpful.

Regards,

Hi GuGuuuMy

Thanks for your reply.  PowerQuery and PowerPivot were new to me.  I've tried both but unfortunately they haven't fixed my problem.  In PowerPivot I still get the upside down question marks instead of the correct characters.  In PowerQuery they appear as square boxes instead but still aren't correct.  I can't see anywhere to adjust the settings for handling Unicode characters with either of these add-ins.

Steve

Hi Steve,

As shown in the following figure, you can try to save as the file with "Unicode" (Save as-Tool-Web Options-Encoding), but this is not a perfect solution. The data might be changed when the excel file covert the ASCII code to Unicode.

I suggest you should contact Oracle company for helping, ask them if there is a way to export data from oracle which has been coverted, and the excel file can read the data without coverting.

Regards,

• Edited by guGuuuMy 16 hours 5 minutes ago

Hi Steve,

As shown in the following figure, you can try to save as the file with "Unicode" (Save as-Tool-Web Options-Encoding), but this is not a perfect solution. The data might be changed when the excel file covert the ASCII code to Unicode.

I suggest you should contact Oracle company for helping, ask them if there is a way to export data from oracle which has been coverted, and the excel file can read the data without coverting.

Regards,

• Edited by guGuuuMy Monday, April 27, 2015 3:21 PM

Thanks guGuuuMy.  I think you're right that I need to look to Oracle to find the answer.  The issue seems to be with the ODBC driver, and how it is translating between the character set in the database and the character set that it is sending to the PC.