Need to export string w/ commas in it properly into a csv file

Question

EDIT: Copied wrong part of function into this question, below is the appropriate one.

$values = mysql_query("SELECT lname, fname, email, dtelephone, etelephone, contactwhen, thursday, 
friday, saturday, sunday, monday, comments FROM volunteers_2009 WHERE venue_id = $venue_id");

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

I have comments that may have a comma in it, and even double quotes, when it has a comma in the comment field, it throws off the entire csv file.

The code below is how it loads the data into the csv string, to put into a csv file.

How do I get it to export the comments field data properly?

Answer 1

Edit: My bad, I just tested that and it doesn't work--not in Excel at least.

You could replace the commas with something else, however:

$field = str_replace(',','#COMMA#',$row['Field']);
$csv_output .= $field.', ';

Answer 2

You could use a different delimiter like "|" or "@" or some other character that is not likely to appear in the data.

Most csv software has the ability to read in a "csv" file that uses something other than a comma as a delimiter.

Answer 3

Enclose the field in double quotes. Double up any embedded double quotes.

ordinary field,"field with , in it","field with double double quote ("""")"

Note that this is very, very close to the question Dealing with commas in a CSV file

Answer 4

Check out fputcsv() here. There are some useful comments there too.

Answer 5

Umn, why not just use the built-in mySQL command? If I missed something, please ignore, but I think this is the fastest route:

http://www.netreveal.com/ddalton/2006/08/how_to_export_mysql_to_csv.html

Website basically suggests:

SELECT <fields or *> INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM <table> WHERE <condition>

Answer 6

Is it important to keep the commas and double quotes in your data when you export it? You could always read it in and then modify the data before exporting it to the CSV.

What I typically do is enclose everything in double quotes and make the combination of "," (double quote comma double quote) my delimiter. That way if I stumble upon a random double quote or a random comma, it's not a big deal.