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.
While opening CSV files, Excel will use a system regional setting called List separator to determine which default delimiter to use.
Microsoft Excel will open .csv files, but depending on the system's
regional settings, it may expect a semicolon as a separator instead of
a comma, since in some languages the comma is used as the decimal
separator. (from Wikipedia)
On Windows, you can change the List separator setting in the Regional and Language Options as specified on the Office support website :
Change the separator in a CSV text file
- Click the Windows Start menu.
- Click Control Panel.
- Open the Regional and Language Options dialog box.
- Click the Regional Options Tab.
- Click Customize / Additional settings (Win10).
- Type a new separator in the List separator box.
- Click OK twice.
Note: this only works if the Decimal symbol is not also designated as comma (in line with the Wikipedia citation above). If it is, Excel will not use comma as the List separator, even if chosen. For many non-United States regions, comma is the default Decimal symbol.
On Mac OS X, this setting seems to be deduced from the decimal separator setting (in the Language & Region pane of System Preferences, go to Advanced). If the Decimal Separator is a point then the default CSV separator will be a comma, but if the Decimal Separator is a comma, then the default CSV separator will be a semicolon.
As you said yourself in the comment, there is an alternative for Mac users to quickly look at those CSV files. It's plugin for Quick Look called quicklook-csv that handles separator detection.
Best Answer
Use LibreOffice to open the file, then save in desired format: I had exactly the same problem you described when trying to use Excel 2010 to read UTF-8 MySQL data with multi-line Japanese text in some fields exported as quoted CSV with \r\n used for end-of-record (tried \r and \n also with no difference in Excel's behaviour). LibreOffice 4.1.3 imported the CSV file correctly, and I could save it in Excel 2010 xlsx format and thereafter open the xlsx file correctly in Excel.