PHP - how to successfully export to CSV for Excel use [ANSWERED]

Audience: PHP developers
Concept: Exporting CSV for Excel so that it can be easily opened by users / data analysts.

The challenge is that if you are using utf8 or international characters (most global organisations or
websites do) then the issue of scambled characters comes up. Usually you see the euro symbol

e.g.

—

instead of en-dash -

or

’
instead of  '

in fact anything starting with â€

This is where the encoding of the file doesn't match what Excel expects, so there are 2 parts to resolving the problem. 

STEPS TO RESOLVE

1. Encode your CSV in UTF8 format - this makes it compatible with Excel's utf8 import routine

2. Give Excel a helping hand - tell it that the CSV is UTF-8 encoded


HOW TO RESOLVE IN DETAIL


1. ENCODE
Make sure your data is encoded in UTF8.
If you are getting data from MySQL, then use the following ....

    $mysqli->set_charset("utf8");


2. BOM
Then just before you write your data to your csv, use the contents of the enclosed BOM file and put them at the beginning.

  //get your csv file pointer $fp
  //...
  //write a BOM for Excel
  $BOM=file_get_contents('BOM');
  fwrite($fp, $BOM);
  // now write the rest of your data
  //... 

You will also need to download the following BOM file.

BOM file