Opening CSV file with UTF16 encoding in Excel 2010

Question

If I open a comma-delimited file ( .CSV extension ) with UTF16 encoding by double-clicking the file, Excel does not show it divided into its proper columns but puts everything in the first column.

If, however, I open a comma-delimited file with UTF8 encoding by double-clicking, Excel shows it properly.

In both cases the file has a proper BOM mark for its encoding.

If I open the same UTF16 file within Excel and respond to the dialogs asking me what kind of file it is, what encoding, and what is the delimiter, Excel shows it properly.

Microsoft happily touts UTF16 as the encoding Windows supports but evidently Excel does not support it as well as it supports UTF8. This has led others on my programming team to tout the superiority of creating UTF8 output files over UTF16 in our Windows application, even though we are dealing with wide character foreign language encodings.

Why does Excel behave the way it does, and is there any way to change it so that it automatically open a UTF16 CSV file properly ?

Answer 1

Is the system on network or domain?

Open the UTF-8 and UTF-16 file in Word and check if you are able to find any difference in those files.

Provide us the steps how you created a UTF-16 file.

Also upload a sample data on the SkyDrive and provide us the SkyDrive link to help us understand the issue better. Refer the link which explains how to upload the data onto SkyDrive:

http://social.technet.microsoft.com/Forums/en-US/w7itproui/thread/4fc10639-02db-4665-993a-08d865088d65

Note: Make sure that there is no confidential data in the document before uploading the document on the SkyDrive.

Answer 2

Is the system on network or domain?

 

Open the UTF-8 and UTF-16 file in Word and check if you are able to find any difference in those files.

 

Provide us the steps how you created a UTF-16 file.

Also upload a sample data on the SkyDrive and provide us the SkyDrive link to help us understand the issue better. Refer the link which explains how to upload the data onto SkyDrive:

http://social.technet.microsoft.com/Forums/en-US/w7itproui/thread/4fc10639-02db-4665-993a-08d865088d65

Note: Make sure that there is no confidential data in the document before uploading the document on the SkyDrive.

The system is on a network.

The UTF-8 and UTF-16 file are different of course.

I wrote a class which outputs UTF-16 characters, with the proper BOM, from lines of CStringWs to a file. The BOM is always put in its proper place in the beginning of the file before strings are written.

I uploaded a cut-down copy of the UTF16 csv file to my SkyDrive publicf folder.

Here is a link:

https://skydrive.live.com/#cid=F05796399AAE14C4&id=F05796399AAE14C4!106

Answer 3

As you are writing a program with UTF-16 as output characters, post your query in the forum link mentioned below for better suggestions:

http://social.technet.microsoft.com/Forums/en-US/ocsclients/threads

Answer 4

As you are writing a program with UTF-16 as output characters, post your query in the forum link mentioned below for better suggestions:

http://social.technet.microsoft.com/Forums/en-US/ocsclients/threads

The link you gave me has nothing to do with my question, and you have wasted my time also, since I did as you requested. If you did not know about this issue, you should not have answered..

Answer 5

Libreoffice Calc will import UTF-16 without any dialog and can export to xls or xlsx.  

Other options are to use notepad ++ and use “encoding" in ANSI or UTF-8.  Both work.

Both Libreoffice and Notepad ++ are free.