PHP file generating CSV, how to correctly output text.

Question

Currently I am working on a PHP script to output a CSV file from entries in a MySQL database. My problem lies in how to correctly output the values. Many of the entries in the MySQL database will contain commas and quotes, which destroy the format of the CSV file if I just plainly print them out to the file.

I'm aware that I can surround the text in quotes, but the entries that contain quotes would mess up the format of the file.

My question is, what can I do to keep this from happening?

Also, do new lines affect the interpretation of the file?

In addition, I'd rather not use the fputcsv function in PHP. I'm attempting to make the PHP script output the contents of the file (with appropriate headers) rather than write to a new file.

Thanks in advance!

Regards, celestialorb

Answer 1

fputcsv is your friend

Answer 2

I have found tab separated value files to be helpful in these situations. TSV is less susceptible to the issues with commas etc in your data.

Answer 3

The process is called escaping, and most parsers (included PHP's) use the backslash to escape characters:

"This string contains literal \"quotes\" denoted by backslashes"

You can escape characters in a string with addcslashes:

// escape double-quotes
$string = addcslashes('this string contains "quotes"', '"');

echo $string; // 'this string contains \"quotes\"'

Given an array of data you want to separate by commas, you can do the following:

// Escape all double-quotes
foreach ($data as $key => $value)
  $data[$key] = addcslashes($value, '"');

// Wrap each element in double quotes
echo '"' . implode('", "', $data), '"';

Answer 4

I think your dismissal of fputcsv might have been premature. In the comments of the fputcsv manual there's an example where they use fputcsv to output to the browser instead of a file. http://php.net/manual/en/function.fputcsv.php

Here is that code, plus some headers to show that it does indeed prompt the user to download a csv file.

$mydata = array(
 array('data11', 'data12', 'data13'),
 array('data21', 'data22', 'data23'),
 array('data31', 'data32', 'data23'));
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"my-data.csv\"");
outputCSV($mydata);

function outputCSV($data) {
    $outstream = fopen("php://output", 'w');
    function __outputCSV(&$vals, $key, $filehandler) {
        fputcsv($filehandler, $vals, ';', '"');
    }
    array_walk($data, '__outputCSV', $outstream);
    fclose($outstream);
}