Ask a new question

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 ?

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.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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..


6 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

MV Grandpa

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

This is a late response, but hopefully will help others:

Excel does support UTF16 CSV Files. But only if the TABULATOR character is used as a delimiter,

then Excel separates the columns correctly when opening the .csv File.

26 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

This is a late response, but hopefully will help others:

Excel does support UTF16 CSV Files. But only if the TABULATOR character is used as a delimiter,

then Excel separates the columns correctly when opening the .csv File.

Thanks for the info!  Confirmed in Excel 2016.

I have made some tests and confirmed that this problem goes a long way back to Excel 2007!

My test is simple - using PowerShell to generate the CSV file, so no more *external* program and Microsoft support can no longer blame on others!

Get-ADUser -filter * | Export-Csv -encoding "unicode" "C:\temp\aduser.csv"

That issue is really ridiculous!  I call this a BUG because CSV stands for Comma Separated Values, not Tabulator separated values, come on!

23 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated August 4, 2023 Views 16,695 Applies to:
Want to do more?
Sign in to ask questions, subscribe, and reply to threads.