Making UTF-8 CSV for Excel

19. 04. 2021

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

But there is a 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 the 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.

Do you need more detailed help? Book a consultation:

Let’s chat about it

Do you want more?

We share the world of software development on our Instagram. Join us 🙂

We also tend to look for Android dev (iOS as well), but there are also chances for a project manager or a tester. Send us an e-mail: skoumal@skoumal.net.

10 Entrepreneurship Lessons Worth Thousands of Dollars

Instead of great success we have experienced great entrepreneurship lessons (for now). It also transformed me, a person who has …

Read article

Unique Czech words reflecting coronavirus now also with English explanations as Flashcard quiz in Vocabulary Miner

  Project ÄŚestina 2.0 covering a variety of the modern Czech language with its slangs and new words has joined …

Read article

Performance of built-in higher-order functions Map, Filter, Reduce, and flatMap vs. for-in loop in Swift

  The most popular higher-order functions are map, filter, and reduce. We all use them since we think that syntax …

Read article

Generate JSON key for Google Play deployment

In order to allow our CI server to deploy applications on Google Play automatically, we have to generate the JSON …

Read article