How can I format CSV data to show specialchars but only have them seen as text within MS Excel or Calc?

I have a script to export data from MYSQL to a CSV file. the data is numbers, text and special chars. All fields are deliminated with double quotes and separated by commas.

I need to export data in the following form:

"this is a (x2") badly grammorize'd sentence. Yes, "no", maybe & more.","0043","false" etc..

However I can only get it to work when I apply htmlsepcialchars to each field. The data needs to 开发者_运维技巧remain as above, but when it comes into Excel or Calc some of the commas and single quotes etc screw it up. Meaning some of the sentence is in one cell and more in another.

$dataResult = mysql_query("SELECT * FROM data");

while ($rowData = mysql_fetch_row($dataResult)) 
{
    for ($j=0;$j<32;$j++) 
    {
        $csv_output .= '"'.htmlspecialchars($rowData[$j]).'",';
    }

$csv_output .= "\n";

}

htmlspecialchars is only meant for escaping data inserted into HTML (as the function name suggest). For CSV data, consider writing a function adhering to the CSV standard, especially by using two quotes instead of one.

For example:

function csvspecialchars($msg) {
  return str_replace('"', '""', $msg);
}

There may be other characters to escape as well. Check out the standard at RFC 4180.

According to wikipedia, you can escape a double quote by doubling it (eg, "this is a (x2"") badly grammorize'd sentence. Yes, ""no"", maybe & more.","0043","false"). That should also fix the problem with the commas and I can't say why single quotes would be a problem...

The built-in fputcsv function takes care of everything neccesary to output valid csv. Unfortunately it can only output to a file, but there's a workaround in the comments, credit goes to Guile:

<?php
function outputCSV($data) {

    $outstream = fopen("php://output", 'w');

    function __outputCSV(&$vals, $key, $filehandler) {
        fputcsv($filehandler, $vals, ';', '"');
    }
    array_walk($data, '__outputCSV', $outstream);

    fclose($outstream);
}

$mydata = array(
 array('data11', 'data12', 'data13'),
 array('data21', 'data22', 'data23'),
 array('data31', 'data32', 'data23'));

outputCSV($mydata);
/* Output sent :
data11;data12;data13
data21;data22;data23
data31;data32;data23
*/