[RESOLVED] Export to excel with comma in field

[beylah]

The name is the database if "Lastname, Firstname" - so when I export to excel the firstname goes into the second field. I have tried using an explode to no avail.

include("../connectdb.php");

$XML = "name,phone,department\n";

$file ="directory-". date("Y-m-d"). ".csv";

$query = "SELECT * FROM directory_national WHERE title!='Room' AND city='toronto' OR city='ottawa'  ORDER BY name";
$result = mysql_query($query);
while($rows = mysql_fetch_array($result)) {
$name = $rows["name"];
$phone = $rows['phone'];
$department = $rows['department'];

$XML.= $name. ",";
$XML.= $phone. ",";
$XML.= $department. "\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$file\"");
header("Content-Transfer-Encoding: binary");
if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){
    header('Cache-Control: public');
}
echo $XML;
exit; 

[OctoberWind]

What about a string_replace to convert the comma?

$name = str_replace(","  "&# 44 ;", $rows["name"]);  // or replace it with a different char? 

or else encapsulate the string that has a comma in quotes:

$XML.= " \" " . $name . " \" ,"; 

[beylah]

That worked thanks, but there is an error in your code: a comma is missing

$name = str_replace(",",  "&# 44 ;", $rows["name"]); 

Also though, is there a way that the data can cme out clean without me having to do a search and replace for the unique character, that is, enclose the name with its comma?

[OctoberWind]

That worked thanks, but there is an error in your code: a comma is missing

D'oh!

Also though, is there a way that the data can cme out clean without me having to do a search and replace for the unique character, that is, enclose the name with its comma?

Yea, take a look at my second part (which I have have edited after you saw my initial post).

If you enclose the entire string in quotation marks, that should give you the whole string in one cell (assuming the string doensn't contain quotes).

$XML.= " \" " . $name . " \" ,"; 

[beylah]

$XML.= " \" " . $name . " \" ,"; 

This purt quotes around the name and still put it into 2 fields:

"lastname firstname"

[ssystems]

Why not just use put_csv into a cache or a physical file then spit out the actual content.

[beylah]

because i would like my boss to get the data by himself when he needs it without me having to fix it

[ssystems]

Why would you fix it? You don't have to.

1. Get data from database
2. Build the array (if not big)
3. Put in a container file
4. then spit out in php that file

while($rows = mysql_fetch_array($result)) { 
 // append to csv file
} 

// headers
// send the csv file 

[beylah]

Sorry ssytems - I misunderstood what you had said. I thought you were on the same reply as OctoberWind.

Ok so looking at your suggestion it makes a lot of sense but I am sorry I dont know how to do what you ask. 1. get dat from db I can do that. 2. Build an array - have no clue how or what to array.

If you could help that would be great.

[Gudlife]

<?php
$CSV = "name;phone;department\n";
$search = ";";
$replace = " - ";

while($rows = mysql_fetch_array($result)) {
$name = $rows["name"];
$phone = $rows['phone'];
$department = $rows['department'];

// optional str_replace in case you are not sure vars don't contain the ";" char.
str_replace ($search, $replace, $name);
str_replace ($search, $replace, $phone);
str_replace ($search, $replace, $department);
$CSV .= $name . ";" . $phone . ";" . $department . "\n";
}

//... do what you want with your csv from this point
?>

... CSV is a point coma format, and not a coma format^^

[ssystems]

... CSV is a point coma format, and not a coma format^^

CSV is an acronym for "Coma" Separated Values. Technically it can be any string separated values, it can be tabs, pipes, double pipes, superstring variable, etc.

Your code

$query = "SELECT * FROM directory_national WHERE title!='Room' AND city='toronto' OR city='ottawa'  ORDER BY name"; 
$result = mysql_query($query);

is # 1: Get the data

for #2: Build the array, below mysql_fetch_array already made it an array for you and is actually an associated one

while($rows = mysql_fetch_array($result)) { 
// append to csv file
}

for #3: Place the container file

while($rows = mysql_fetch_array($result)) { 
    fputcsv($fh, array_keys($data));
}

At that point you are actually placing it in a stream file. For #4 you just need to spit it out. Here is a similar solution http://bluedogwebservices.com/howto-...tream-csv-php/