Saving a file in a CSV type in Excel always removes the BOM

Question

I've been trying to find a reasonable solution/explanation (unsuccessfully) to find out why Excel defaults to removing the BOM when saving a file to the CSV type.

Please forgive me if you find this a duplicate of this question. This handles reading CSV files with non-ASCII encoding, but it doesn't cover saving the file back out (which is where the biggest issue lies).

Here is my current situation (which I'm going to gather is common among localized software dealing with Unicode characters and a CSV format):

• We export data to a CSV format using UTF-16LE, ensuring the BOM is set (0xFFFE). We validate after the file is generated with a Hex editor to ensure it was set correctly.

• Open the file in Excel (for this example we're exporting Japanese characters) and witness that Excel handles loading the file with the correct encoding.

• Attempts to save this file will prompt you with a warning message indicating that the file may contain features that may not be compatible with Unicode encoding, but asks if you'd like to save anyway.

• If you select the Save As dialog, it will immediately ask you to save the file as "Unicode Text" rather than CSV. If you select the "CSV" extension and save the file it removes the BOM (obviously along with all the Japanese characters).

Why would this happen? Is there a solution to this problem, or is this a known 'bug'/limitation of Excel?

Additionally (as a side issue) it appears that Excel, when loading UTF-16LE encoded CSV files, only uses TAB delimiters. Again, is this another known 'bug'/limitation of Excel?

Answer 1

There are no polite answers to questions of the form "Why does Excel do X?".

Why don't you just adopt "Save as Unicode Text" as your standard transfer format? Excel Save as ensures there's a BOM, uses TAB as a delimiter, quotes fields just like CSV ...

By the way, you don't "select the CSV extension". You have TWO choices -- format and extension. Try save as unicode text in the 'Save as type' box and manually change the extension in the 'File name' box to csv. Then exit from Excel and double-click on the file name in Windows Explorer. Opens up fine. Try it.

Update to discuss OP's comments:

• "Save as Unicode Text" (in Excel 2007) will always save your file as a Text extension (I can't speak for previous or newer versions). That's the idea of "Save as type"...

=> Please read carefully what I wrote above and try it out for your self. It will always save your file with a TXT extension unless you override that: delete the txt in the 'File name' box and type csv.

• To clarify your "Excel Save as ensure there's a BOM", is incorrect.

=> "Save as Unicode text" ensures that there is a UTF-16LE BOM at the front of the file. Try it and see.

• This is the reason I asked a question in the first place! If I open a CSV formatted file that contains a BOM to indicate my file Encoding is UFT-16LE and modify this file via Excel, I expect when I save this file that the Encoding remains in tact...

=> and your expectations are not met, because Excel doesn't retain any info about input CSV files. There is no way to force it to do what you want to do. So you either give up or try something else, like what I'm suggesting.

• What would be really nice is someone who's experienced these problems before, provide your insight please.

=> I have experienced these problems, with Chinese data and Excel 1997, years ago. I have, before answering your question, verified with Excel 2003 and Excel 2007 that Excel is still behaving badly. I have verified that the "save as type = Unicode text, extension = CSV" workaround does indeed work. I have provided my insights.

Answer 2

Easier than trying to leave a comment...

Save as Unicode Text, regardless of trying to overwrite the extension, will append TXT. Take for example the File Name as "text.csv", Saving as type Unicode Text results in "test.csv.txt" (using Excel 2007). Did you seriously try this? To also note, it never appends .txt in the File Name.

Absolutely saving as Unicode Text sets the BOM, I can't disagree with that. Hence my question asking why saving as CSV ignores the BOM and sets the file to ASCII.

You defiantly provided some useful insight (thanks!):

"and your expectations are not met, because Excel doesn't retain any info about input CSV files. There is no way to force it to do what you want to do. So you either give up or try something else, like what I'm suggesting."

As you state, I should try your solution. Let me throw you a quick complication/show stopper to it, however. We have ~ 10,000 users who are all computer illiterate and have become accustomed to using Excel to manage their CSV file (not by force, I might add) but because the default application to manage a CSV file is Excel (right out of the box).

Option 1 for the solution: Export the file as .txt (in CSV format with UTF-16 encoding, TAB delimited). Great. Now 10,000 users have no idea how to open their file in Excel since it's no longer the default. 10,000 users call our support line all at once. We have 10,000 unhappy users.

Option 2 for the solution: Export the file as .csv (in CSV format with UTF-16 encoding, TAB delimited). Great. Now 10,000 users have no idea how to change the extension of the file when saving it, since they're not used to any prompt asking them the format. 10,000 users call our support line all at once. We have 10,000 unhappy users.

Do you get my point? I'm not trying to be argumentative but I've addressed most of our options (your solution included).

What baffles me is if I export a CSV formatted file with UTF-16 encoding, why on earth would the default application to load the file be Excel when it clearly can't handle saving the file back out with the same encoding? (keeping in mind when I say default, obviously the user can change this but refer to my 'complication' above).

What I'm ultimately looking for is a range of answers of what people have done in the real-world in this situation (as I can imagine this issue is more common than not). I greatly appreciate your input John, and I apologize if I come off a bit brash.