Saving a file in a CSV type in Excel always removes the BOM
- by rickp
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?
