Mysql to Excel

Convert from Mysql To Excel

[born2win]

Hi All,
I need to write a program to transfer data from mysql database to Excel. Is there any free php code to do it. I tried one of the code which I got through search
[codes]
<?php
include 'library/config.php';
include 'library/opendb.php';
// DB Connection here

$select = "SELECT * FROM member_particulars";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $export , $i ) . ",";
}
$header .= $header . "\r\n";
while( $row = mysql_fetch_row( $export ) )
{
$line = '';
foreach( $row as $value )
{
if ( (!isset( $value ) ) ¦¦ ( $value == "" ) )
{
$value = ",";
}
else
{
$value = str_replace( '"' , '""' , $value );
$value = '"' . $value . '"' . ",";
}
$line .= $value. "\r\n";
}
$data .= trim( $line ) . "\r\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
$data = "\n(0) Records Found!\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=member_particulars.csv");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\015\012$data";

?>
[/codes]
I am getting all the data in one column. Can anyone help me to fix this one or any new code will be appreciated.

[Frank_Rizzo]

Can you try saving the file to disk rather than open with Excel (if you have the option that is). Then load Excel and open the csv file from disk.

I have a working script similar to yours. There are some slight differences.

First I add a chr(13) at the end of each line but you use \r\n

In the header section I use:

header("Content-type: application/octet-stream");
header('Content-Disposition: attachment; filename="' . $csv_filename . '"');
print $csv_data;

If you save the file to disk and open in Excel it could give you some clues. Try opening in Notepad too. What you should see is columns seperated by commas and a newline after each row.

[edited by: Frank_Rizzo at 9:20 am (utc) on Aug. 8, 2006]