How do I export to CSV file with table record value with comma in it properly?

Question

I have a function that exports values into a CSV file, but the "comments" field has commas in it and it messes up the columns when you open it in a spreadsheet program.

Is there a way around exporting it properly?

//this exports only names and comments into a CSV file
function exportNamesCommentsCSV($table, $columns) {
    $file = "volunteer_comments";
    $csv_output = '';
    $table = 'volunteers_2009';
    $result = mysql_query("select * FROM ".$table."");
    $i = 0;
    if (mysql_num_rows($result) > 0) {
     while ($row = mysql_fetch_assoc($result)) {
      $csv_output .= $row['Field'].", ";
      $i++;
     }
    }
    $csv_output .= "\n";

    $values = mysql_query("SELECT lname, fname, email, comments FROM ".$table."");

    while ($rowr = mysql_fetch_row($values)) {
     for ($j=0;$j<$i;$j++) {
      $csv_output .= $rowr[$j].", ";
     }
     $csv_output .= "\n";
    }

    $filename = $file."_".date("Y-m-d_H-i",time());
    header("Content-type: application/vnd.ms-excel");
    header("Content-disposition: csv" . date("Y-m-d") . ".csv");
    header( "Content-disposition: filename=".$filename.".csv");
    print $csv_output;
    exit;
}

EDIT: This would be an example of a comment...

I would prefer to work the gates, but I can work anywhere really.

The , (comma) in the example comment above throws off the proper column structure.

Answer 1

You'll want to wrap the entry in a qualifier; typically quotes.

"col1", "col2,,,", "col3"

If the entry contains quotes, you'll need to double up on them.

Here's the Wikipedia article

Answer 2

What you need to do is enclose values in quotes, like so:

"value","value","value"

This will correctly handle commas in the values, and numeric values will also still work fine.

To put a quote in the value, however, you need to put two quotes together. E.g. representing this string:

Bobby says, "Hi!"

it will need to be represented as

"some value","Bobby says, ""Hi!""","Some other value"

You can do this with:

$csv_value = "\"" . eregi_replace("\"", "\"\"", $orig_value) . "\"";

Answer 3

One way to achieve what you want is to do a search and replace on your "comments" field, assuming you're sure that this is the only field than can contain embedded commas.

You might also need to check that your memo field can't contain CRs or LFs, they too will mess with your csv file import.

Answer 4

PHP has functions for dealing with making comma separated files for you.

Check out the fputcsv function. It lets you do something like this:

<?php
$list = array (
    array('this has, commas, in it','bbb','ccc','dddd'),
    array('123','456','789', 'this is "like this"'),
    array('1234','124','1242', 'yay, woo"!"')
);

$fp = fopen('file.csv', 'w');

foreach ($list as $record) {
    fputcsv($fp, $record);
}

fclose($fp);
?>

The file file.csv will have this in it:

"this has, commas, in it",bbb,ccc,dddd
123,456,789,"this is ""like this"""
1234,124,1242,"yay, woo""!"""

Later on when you need to read it, if you ever do, you can use fgetcsv.

So in your situation, all you need to do is make an array of arrays that each have the name and the comment, and then put it through fputcsv and you're golden.