Keywords: Excel | encoding | CSV | diacritics
Abstract: This article analyzes the common issue where Microsoft Excel incorrectly displays diacritic characters when opening UTF-8 encoded .csv files. It explains the causes, including encoding assumptions and version-specific bugs, and provides solutions such as adding a UTF-8 BOM, exporting in UTF-16, and using the Import Text wizard. The goal is to help developers ensure data integrity in Excel.
Introduction
When programmatically exporting data to .csv files, particularly using languages like PHP, if the files are encoded in UTF-8, Microsoft Excel may fail to correctly display diacritic characters upon opening, such as “Numéro 1” appearing as “Numéro 1”. This issue is commonly caused by Excel's default encoding assumptions.
Causes of the Problem
Excel assumes by default that .csv files use a single-byte encoding like Windows-1252, rather than UTF-8. Without a BOM (Byte Order Mark), Excel misinterprets the UTF-8 byte sequences as characters from other encodings. For instance, the character é is encoded in UTF-8, but in Windows-1252, the same bytes may correspond to different characters, leading to display errors.
Solutions
Based on the best answer, adding a UTF-8 BOM is the preferred method. The BOM is a special byte sequence (0xEF, 0xBB, 0xBF) placed at the start of the file to identify it as UTF-8 encoded. This typically works for modern Excel versions (2007 and later).
However, for older Excel versions (e.g., Excel 2003), due to a known bug, the BOM may be ignored. In such cases, exporting in UTF-16 encoding is an alternative, as older Excel versions (like 2000 and 2003) often handle it correctly. Note that UTF-16 can cause issues in some other text editors.
Another option is to use Excel's “Import Text” wizard, which allows manual specification of the file encoding. This ensures correct opening but adds user intervention complexity, making it less suitable for automated processes.
Conclusion and Recommendations
For modern Excel users, adding a UTF-8 BOM is the simplest and most effective approach. For users on older Excel versions, consider using UTF-16 encoding or providing guidance on the Import Text wizard. Developers should weigh compatibility and convenience based on the target user environment.