PHP: encoding of a CSV file for Excel

I'm working on a MySQL database where all tables are encoded in UTF-8. One of the most required tasks on this database is creating downloadable CSV files to be read on Microsoft Excel. Creating a CSV file from a database is almost trivial, thanks to the PHP's fputcsv() function that parse an array and returns a formatted CSV line. The actual problem here is the target encoding, that is, the character set that MS Excel is able to handle.

First surprise: Excel is not able to handle properly UTF-8 characters if you don't change some of its default import settings. Of course we can't ask our clients to change their program settings when they download something from our website. Fortunately, there's a trick: Excel's default encoding is Windows-1252, so we have only to convert our UTF-8 characters to Windows-1252.

Since we're dealing with an array of values, we create a function that will be passed to array_walk() so that each value will be properly converted:

function excelEncode(&$value, $key)
  $value = iconv('UTF-8', 'Windows-1252', $value);


array_walk($excel_values, 'excelEncode');

By using the iconv() function we make sure that every single array value is properly encoded in a format that Excel can handle by default. Is there a less verbose way? Yes: just use the proper charset string on your header() function when you output a CSV file. This is the most quick and easy solution.

This entry was posted in by Gabriele Romanato. Bookmark the permalink.

Leave a Reply

Note: Only a member of this blog may post a comment.