Drupal MySQL to CSV export - easy way

Thank you!

It works fine after that $mysql_result is corrected to $result.

Regards.

I'm getting the contents of the tables back fine but the headers are not being populated. mysql_num_fields returns nothing as does mysql_field_name.

Any ideas?

Found a solution to my problem, this is the update that worked for me.

$count = $result->field_count;
for($i = 0; $i < $count; $i++){
$finfo = $result->fetch_field_direct($i);
$header[] = $finfo->name;
}

Thanks, it works great

Dear Janak and others,

I'm making a website with drupal. In time of making a geolocation track module I felt the need of an excel download button. So after lot of googling I found your blog. Its great. It generates a CSV. I changed it in my module as the following,

function geolocation_exceldownload($query = '') {
$filename = 'test.xls';
drupal_set_header('Content-Type: application/octet-stream');
drupal_set_header('Content-Disposition: attachment; filename=' . $filename);
drupal_set_header("Pragma: no-cache");
drupal_set_header("Expires: 0");
@$result = db_query($query);
$count = $result->field_count;
for($i = 0; $i < $count; $i++){
$finfo = $result->fetch_field_direct($i);
$header[] = $finfo->name ."\t";
}
$header = implode(" ", $header) ."\r\n";
while($row = db_fetch_object($result)) {
$line = '';
$data = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\nno matching records found\n";
}
print($header.$data);
}

Though it generates a proper excel format(.xls) and also shows all the data grid correctly but can any one make it to follow the .xlsx format..?? Till I'm unable to do it but I think you people surely have some more ideas to make the module snippet more better...

Thank you for the great post again....

I am using the following code. Past in the body of the page... It is working export the data but also export the header ,primary links logo navigation. I just only want to data to export. code as under. (remove the database pw and connection string to reduce the code. query working )

 
$filename = 'test.xls';
drupal_set_header('Content-Type: application/octet-stream');
drupal_set_header('Content-Disposition: attachment; filename=' . $filename);
 
 $flag = false; 
 $result = mysql_query("SELECT symbol FROM price") or die('Query failed!'); 
 while(false !== ($row = mysql_fetch_assoc($result))) { 
 if(!$flag) {
 // display field/column names as first row 
 echo implode("\t", array_keys($row)) . "\n"; 
 $flag = true; 
 } 
  echo implode("\t", array_values($row)) . "\n";
 } 
 
 

I put exit at the and to solve my problem.
and mysql_num_field dos not work