How to format data in a file which will be open in excel?

[orangeapple]

Hi there,

I am trying to generate a file with php from a database extract that i would like to be open in excel.

What i need is to format the content of the file in CSV i guess. The 'fgetcsv()' can do the job by formating the content of an existing file.

Is there an other option than to create a file with my database content, save it on the server and open/format it with 'fgetcsv()'?

Thanks a lot for your help.

[vincevincevince]

You have to decide what type of CSV you want.

Assuming the data is in a 2D array, you can do something like:

$data=Array(.... balh blahb.... (2D));
$fh=fopen("filepath.csv","w");
foreach ($data as $row)
{
$row="";
foreach ($row as $point)
{
$row[]=csvquote($point);
}
fwrite($fh,implode(",",$row)."\n\r");
}
fclose($fh);

How it should work:
open a file, then go through the data row at a time, and with each data point... escape it, add it to an array. Implode an array for each row, separating each value with a comma. Then put a \n\r linebreak between each row.

Look into content-type headers which will make excel open it straight away :)

The function csvquote could be:-

function csvquote($string)
{
if (!preg_match("/[^0-9]/",$string)) return $string; //num
else return "\"".addslashes(preg_replace("/([\n\r])/","\\$1",$string))."\"";
}

(the plan is to not quote numbers, but put " " around strings, and escape " and \n and \r. The function isn't tested but that's the basis of what I'd do). You can probably use mysql_escape_string() instead as it is pretty much similar in effect.