CSV Exports for Excel for Mac

Exporting a UTF-8 CSV readable by Excel for Mac as well as other popular spreadsheets proved a challenge. Using the UTF-8 BOM with comma delimiter and UTF data worked for everything except Excel for Mac. The solution was found by reading stackexchange — convert to UTF-16LE, UTF-16LE data and delimit with a tab.

Here is some PHP code for that:

  $data = array(
    array('空手', '家族'),
    array('My name is', 'John'),
    array('नमस्ते', '四')

  $file = 'test.csv';
  $fp = fopen($file, 'w');
  $utf16_representation_BOM = b"\xFF\xFE";
  fprintf($fp, $utf16_representation_BOM);
  foreach ($data as $row)
    putCsvRow($fp, $row);

  // can't use fputcsv; must convert the entire line to UTF-16LE
  function putCsvRow($f, $data)
    // item by item, decide if we need quotes or escaping
    if (!empty($data))
      // replace " with ""
      $to_escape = array('"');
      $escaped = array('""');

      // enclose anything containing whitespace, comma or quote
      $things_that_require_quotes = '/[\s,"]/';

      $row = array();
      foreach ($data as $item)
        $item = str_replace($to_escape, $escaped, $item);
        $needs_quotes = preg_match($things_that_require_quotes, $item);
        if ($needs_quotes)
          $item = '"' . $item . '"';
        $row[] = $item;
      $row = array();
    $line = implode("\t", $row) . PHP_EOL;
    $cv_row = mb_convert_encoding($line, 'UTF-16LE', 'UTF-8');
    fwrite($f, $cv_row);

Now the file will be viewable as expected even in Excel for Mac. Horrible workaround? Yes. Excel for Mac should handle the more standard UTF-8.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>