Writing a CSV file for Mac users with PHP

Question

I use a generic algorithm to write CSV files that in theory works for the major OSes. However, the client started to use Mac a few weeks ago, and they keep telling me the CSV file cannot be read in Microsoft Excel 2008 for Mac 12.2.1.

They have their OS configured to use "semicolon ;" as list separator, which is exactly what I am writing in the CSV. They also say, that when they open the file in notepad, they have noticed there are no linebreaks, everything is displayed in a single line; which is why Excel cannot read the file properly; but in my code, I am using the cross-browser line break \r\n

This is the full code I use:

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=participantes.csv");

$separator = ";";
$rs = $sql->doquery("SELECT A QUERY TO RETRIEVE DATA FROM THE DB");

$header = "";
$num_fields = mysql_num_fields($rs);

for($i=0; $i<$num_fields; $i++){
  $field = mysql_field_name($rs, $i);
  $header .= $field.$separator;
}

echo $header."\r\n";

while($row = $sql->fetch($rs)){
  $str = "";
  for($i=0; $i<$num_fields; $i++){
    $field = mysql_field_name($rs, $i);
    $value = str_replace(";", ",", $row->{$field});
    $value = str_replace("\n", ",", $value);
    $value = str_replace("\d", ",", $value);
    $value = str_replace(chr(13), ",", $value);
    $str .= $value.$separator;
  }
  echo $str."\r\n";
}

Is there anything I can do so Mac users can read the file properly?

Answer 1

Here's some code I did to convert a tab delimited data into CSV, and it comes in fine on my mac. Note that I have it set up to make me click to download, rather than pushing it to the browser. It's not a great solution (I'm pretty sure the code is crap) but it works for what I need it for.

$input = $_POST['input'];
//Remove commas.
$replacedinput1 = str_replace(",", "-", $input);
//remove tabs, replace with commas
$replacedinput2 = str_replace(" ", ",", $replacedinput1);
//create an array
$explodedinput = explode("
", $replacedinput2);
//open the CSV file to write to; delete other text in it
$opencsvfile = fopen("/var/www/main/tools/replace_tab.csv","w+");
//for each line in the array, write it to the file
foreach($explodedinput as $line) {
fputcsv ($opencsvfile, split(',', $line));
};
//close the file
fclose($opencsvfile);
//have the user download the file.
/*
header('Pragma: public');
header('Expires: Fri, 01 Jan 2010 00:00:00 GMT');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Type: application/csv');
header('Content-Disposition: filename=replace_tab.csv'); */

//Or not, since I can't get it to work.
echo "<a href='replace_tab.csv'>Download CSV File, then open in Numbers or Excel (Note, you may need to right click, save as.)</a>.";

Answer 2

For debugging purposes:

1. Create a CSV file and send it to them by mail. Can they open it OK?
2. Have them download the file from your page and have it sent back to you. Compare the files in a Hex-editor to rule out the off-chance that they look differently from what you send to the browser or from what you have saved.
3. Have them double-check their Excel-settings.
4. Have them create a working CSV file from scratch (text editor on a mac) and spot any differences from your approach.