How to force Excel to open UTF8 CSVs as such

Ever wondered how to make Microsoft Excel open UTF8 encoded CSVs properly? Me, too.

The Problem

Recently we had a problem with a client whereby their MySQL DB was UTF8, all controllers, models and views were UTF8, the Content-Transfer-Encoding was UTF8, but still Excel converted them to ISO Latin 1.

The Solution

The solution took me 90 minutes of Googling and trial and error, but I got there.

Step 1: Use iconv to translate data to UTF16 Little Endian

MS Excel will not open UTF8 documents as such, but does recognise the Little Endian BOM. So let’s convert the UTF8 data to UTF16LE:
$out = iconv ( 'UTF-8', 'UTF-16LE//IGNORE', $out ); // your data needs to be UTF-8 encoded...
Where $out is the data you wish to display in Excel.

Step 2: Fake the UTF16 response from the server from a UTF8 page

This was the step that took a while. Playing around with declared character encodings and file character encodings, but what we have is the following:

Where $content_for_layout is your Excel data (I am using CakePHP here).

The two first characters are the UTF16 Little Endian BOMs (Byte Order Marks). Make sure you save your file as a UTF8 file and the job is done.

Eagerterrier's Blog