Making UTF-8 CSV for Excel



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

  Project Čestina 2.0 covering a variety of the modern Czech language with its slangs and new words has joined forces with vocabulary Flashcard app Vocabulary Miner. They are bringing new words not only to Czechs but ...

Read post

  The most popular higher-order functions are map, filter, and reduce. We all use them since we think that syntax is much better and it is even faster to write them than the old way for-in loop. But is it really true ...

Read post

In order to allow our CI server to deploy applications on Google Play automatically, we have to generate the JSON key. Thanks to this key, we can quickly deploy applications without manually uploading each and every version. We can also control what version we did upload on Google Play through our ...

Read post

The Czech vocabulary-building app Vocabulary Miner created by studio SKOUMAL is based on the popular Flashcard System and special algorithm.  It promises the user to simply say more with every word and delivers it through effective, yet simple vocabulary training by ...

Read post