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.
This feature has been requested for the past 4 years by dozens of people, however, this still hasn't been implemented that I can see.
Best Answer
If changing the default encoding for all offline files (not only text files) would also work for you, then there is now (I'm not sure since when, but it works in Firefox 59.0) an option to change the default encoding to UTF-8 in
about:config: set the value ofintl.charset.fallback.utf8_for_fileto true.Aside: it appears that one of the purposes of preventing the setting of the default encoding for online files is to avoid fingerprinting.
See also similar questions on stackoverflow (with the actual answer (used here)) and unix.stackexchange (with an interesting discussion of some of the rationale).