Php – How to export to CSV file with table record value with comma in it properly

csvmysqlphp

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.

Best Solution

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) . "\"";
Related Question