Making UTF-8 CSV for Excel

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

Have you ever felt like Google Play is discriminating your app by rating in a long run? You are not alone. What is the real problemĀ here? Google Play was re-branded from Google Market a few years back after it reached its full model capacity. Many features have been redone, removed ...

Read post

We, SKOUMAL Studio, develop mobile apps. We are strong believers in freedom, yet reliability, flat hierarchy yet careful attitude, and no rigidity, yet without anarchy. Our own product - language app Vocabulary Miner - needs to speak more languages to our students. That will be your job ...

Read post

All things considering you are probably using git to version your changes, if notĀ hereĀ is a quick-start guide. You see I am a bit punctual towards my code and documenting its changes, I write extensive commit messages however I'm extremely reluctant to write changelogs for clients or users ...

Read post

We've all been there,ā€¦ reducing the number of overdraws or customizing image with little to none external libraries. This modern twist toĀ thisĀ (andĀ this) age-old article will surely help you with that. I've managed to rewrite the example code in Kotlin so it's far more readable than ...

Read post