Making UTF-8 CSV for Excel

22.1.2011

Last Updated on

Sometimes you need to export a table to the Excel and other spreadsheets. CSV file saved in UTF-8 seems to be sufficiently universal format.

But there is problem with non-ASCII characters in Excel. Excel default encoding depends on the system. The workaround is to put three magical bytes to the file beginning. They are called BOM (Byte order mark) and say to the editor that file is encoded as UTF-8.

Code for the report of the assets of your company:

//headers
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Description: File Transfer');
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename=export.csv;');
header('Content-Transfer-Encoding: binary'); 

//open file pointer to standard output
$fp = fopen('php://output', 'w');

//add BOM to fix UTF-8 in Excel
fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));
if ($fp)
{
  fputcsv($fp, array("Cars", "Planes", "Ships"), ";");
  fputcsv($fp, array("12", "2", "6"), ";");
  fputcsv($fp, array("23", "3", "5"), ";");
  fputcsv($fp, array("31", "5", "8"), ";");
}

fclose($fp);

Edit 2017-11-30: BOM does not work in older versions of Excel 2007. Try to update Excel in case of problems.

Vladislav Skoumal
SKOUMAL CEO

Comments

Author picture

Thanks Vlad, just what I was looking for all evening 🙂

Reply
Load more
Leave a comment

  Twitter is there to enhance your Android skills. How? Just follow the right people and get your feed updated with Android insights and hacks. If you are from the Czech Republic or Slovakia, you can be surely inspired by your local Slovak and Czech ...

Read post

  Twitter can definitely educate the Android Developer you are. It is as easy as to follow the right people and then just checking their regular insights in your feed.  The only thing that remains is how to choose the right people.  And we can try to help you with ...

Read post

The Flashcard maker app Vocabulary Miner by Czech SKOUMAL Studio helps memorize vocabulary that the user really needs. Now, topical Word lists tailored to the needs of expats and travelers are available in 12 languages. Users can effectively learn Czech ...

Read post

  Have you ever felt like Google Play is discriminating against your app? You are not alone. What is the real problem? Google Play was re-branded from Android Market a few years back. Many features have been redone, removed or added since then, but things like rating stay ...

Read post