csv export comma problem

michaelowen

hello,

I need to know what to do with a csv export in which i would like the entire field content to be enclosed with quotes. My problem is with fields that include commas.

I think i'm close with this, but how would i tell it to enclose all fields with quote?

<?php
include("config.php");

$connect = mysql_connect("$user_hostname", "$user_username", "$user_password");
mysql_select_db("$user_database", $connect);

$F = fopen('entrants_full.csv' , 'w'); // open for write
$delim = ",";
$res = mysql_query("SELECT * FROM xxxxx where field = xxx");

$numfields = mysql_num_fields($res);
for($x=0;$x<$numfields;$x++){
$titles[] = mysql_field_name($res, $x);
}
fwrite($F, join($delim, $titles) . "\n");

while ($row = mysql_fetch_row($res)) {
fwrite($F, join($delim, $row) . "\n");
}
fclose($F);

header("Content-Type: text/plain");
header("Pragma: cache");
header('Content-Disposition: attachment; filename=entrants_full.csv');
header("Expires: 0");
readfile('entrants_full.csv');

?>

should i do a search and replace in the field before it hite the database, or should i just tell this script to enclose all fields with quotes?

-Michael

kkobashi

"I need to know what to do with a csv export in which i would like the entire field content to be enclosed with quotes. My problem is with fields that include commas. "

A CSV file means comma seperated values. Therefore, the fields are delimited by commas. Each field will not have commas as part of their values (for if it did, it would screwup the formatting and ability to parse).

For example:

First, Last, Street Address, City, State, Zip
Joe, Smith, 123 Elm Street, Los Angeles, California, 90210

Bad example:
First, Last, Street Address, City, State, Zip
Joe, Smith, 123 Elm Street, Los, Angeles, California, 90210

If there was a comma between Los and Angeles, you would be screwed.

So when you say:
"My problem is with fields that include commas", there can't be commas in the fields for a CSV formatted file. I just wanted to clear that part up.

Give us an example of how you want to see the CSV file.

is it like this?

First, Last, Street Address, City, State, Zip
"Joe", "Smith", "123 Elm Street", "Los Angeles", "California", "90210"

Why do you want to enclose in double quotes? Is it because some data may have a comma as part of its value? If so, why not use another delimiter?

michaelowen

yes, that is exactly it. Input is being entered with commas and it's greating the parsing.

for example "125 River Ave Apt. 3" is being entered as "125 River Ave, Apt. 3" and thus it thinks it's another field.

Should i do a search and replace when users enter the fields into the db, or enclose them the fields in quotes (which are then split with commas)?

-mike

kkobashi

Wait a minute...

In your example you are READING from a mySQL database and WRITING to a CSV file....

michaelowen

would a tilde show up fine if excel tried to open the "csv" file?

yes, i am writing a text cvs file for dowload from a mysql database.

-michael

kkobashi

You can added double quotes around the values.

"Joe", "Smith", "123 Elm Street, Apt. 333", "Los Angeles", "California", "90210"

The problem is, that the application that reads it (like Microsoft Excel) could interpret the double quotes as part of the token value. You will then be left with the delimina of how to strip off the double quotes...

yelvington

The whole point of enclosing fields with quotes is to allow embedded commas to be passed in the data.

"The quick, brown","4","fox"

Three fields. The first comma, because it is inside a quoted data element, is not considered to be a field separator.

Any application (including Excel) that understands CSV will have an option to detect fields enclosed by quotes.

Here are some useful explanations:
http://www.edoceo.com/utilis/csv-file-format.php
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

michaelowen

Ok, so what is everybody's ideal recommendation to remedy this?

search/replace any commas when this info is uploaded to the db?

how could i alter the above script to enclose every field in quotes?

-Mike