Tag Archives: csv

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);
  }
  fclose($fp);

  // 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;
      }
    }
    else
    {
      $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.