Opening CSV file with UTF16 encoding in Excel 2010
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 ?
This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.
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: