MySQL Dump to Excel

[rscott7706]

Hey all - want to dump a MySQL file to Excel.  Followed the tutorial "Exporting MySQL To Excel".

 

I get an error on the actual dump.  It does dump the file, so I know I am connecting and downloading.  But, it puts all records in one line of text in the browser, and does not give me the prompt to download.

 

Here is first, the error, then my code below (sans logon info).

========================================================

Error Info:

 

Warning: Cannot modify header information - headers already sent by (output started at /home/cleanair/public_html/excel.php:10) in /home/cleanair/public_html/excel.php on line 46

 

Warning: Cannot modify header information - headers already sent by (output started at /home/cleanair/public_html/excel.php:10) in /home/cleanair/public_html/excel.php on line 47

 

Warning: Cannot modify header information - headers already sent by (output started at /home/cleanair/public_html/excel.php:10) in /home/cleanair/public_html/excel.php on line 48

 

Warning: Cannot modify header information - headers already sent by (output started at /home/cleanair/public_html/excel.php:10) in /home/cleanair/public_html/excel.php on line 49

id Name Company Address City State Zip AreaCode Prefix Digits FaxArea FaxPrefix FaxDigits Email Copies "12" "Ronald Scott (Test #9)" "RsConcepts Consulting" "11470 Oak Creek drive " "Lakeside" "CA" "92040" "619" "312" "0799" "619" "312" "0802" "rscott7706@gmail.com" "2" "11" "Ronald Scott (Test #9)" "RsConcepts Consulting" "11470 Oak Creek drive " "Lakeside" "CA" "9204" "619" "312" "0799" "619" "312" "0802" "rscott7706@gmail.com" "2" "10" "Ronald Scott" "RsConcepts Consulting" "11470 Oak Creek drive " "Lakeside" "CA" "9204" "619" "312" "0799" "619" "312" "0802" "rscott7706@gmail.com" "2"

 

=============================================================

 

 

My Code:

 

<?php

define(db_host, "localhost");

define(db_user, "");

define(db_pass, "");

define(db_link, mysql_connect(db_host,db_user,db_pass));

define(db_name, "cleanair_dvd");

mysql_select_db(db_name);

?>

 

<?php

$select = "SELECT * FROM requests";               

$export = mysql_query($select);

$fields = mysql_num_fields($export);

?>

 

<?php

for ($i = 0; $i < $fields; $i++) {

    $header .= mysql_field_name($export, $i) . "\t";

}

?>

 

<?php

while($row = mysql_fetch_row($export)) {

    $line = '';

    foreach($row as $value) {                                           

        if ((!isset($value)) OR ($value == "")) {

            $value = "\t";

        } else {

            $value = str_replace('"', '""', $value);

            $value = '"' . $value . '"' . "\t";

        }

        $line .= $value;

    }

    $data .= trim($line)."\n";

}

$data = str_replace("\r","",$data);

?>

 

<?php

if ($data == "") {

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

}

?>

 

<?php

header("Content-type: application/x-msdownload");

header("Content-Disposition: attachment; filename=extraction.xls");

header("Pragma: no-cache");

header("Expires: 0");

print "$header\n$data";

?>

 

 

Any ideas?  Thanks in advance!!

 

[jvrothjr]

Here is a file I have that does export results to a CSV format file.

 

<?
// File Name: export_csv.php
// Revised 12.20.2006 By Joseph Roth
// Used to export data based on query requirments to a CSV formated file
// ...
// ...
// Load backgound and subfunctions
require("pageback.inc"); 
// Set Variable used to exit of error exists
    $exitcode = 0;
// Get table name passed via url     
$tbl = $_GET['tbl'];
// Set Directory name to remove old CSV files	
    $dh = opendir("./");
// Loop thru directory listing    
    while (false !== ($file = readdir($dh))) {
    	if ($file != "." and $file != "..") {
// if file found check extention of file and if CSV delete file
        	if (!is_file("$dirpath/$file")) {
            $ext = substr($file,strlen($file)-3);
		    $ext = strtolower($ext);
			if ($ext == "csv") {unlink($file);}
        	}
	}
}
# Close path listing
    closedir($dh);	
// Query table for records based on query just run.
// tmptbl id the list of matching records found form query     
    $result =  mysql_query("select ".$tbl.".* from tmptbl inner join ".$tbl." ON tmptbl.tracking_id = ".$tbl.".tracking_id");
    $result1 =  mysql_query("select * from ".$tbl);
// Check to see if there were and natching records else exit with message 
    if(!$result) {$exitcode++;
	echo "No Records Found To Export<br>";
} else {
	echo "Matching Records Being Exported<br>";
}
// if matching records were found export to a file (File name auto created based on date)	
    if($exitcode == 0) {
// Define file name
    	$filename = date("HismdY") . ".csv";
// Open file to write
    if (!$handle = fopen($filename, "wb")) {
// If error exit program	     
        echo "Cannot open file " . $filename;
    	    exit();
    	}
    $printcolnames = 1;
// loop thru qeury string to export data to CSV file
    	while($row = mysql_fetch_array($result1))
    {	
    	    $rowval = "";
        // ============ print column names ====================
        	if($printcolnames == 1) {
            $data = "";
            $rowname = "";
// First Row is field names	            
    	        for($i=0;$i<mysql_num_fields($result1);$i++) {
        	    $rowname .= mysql_field_name($result1, $i) . ",";
			}	
   	        	$rowname = substr($rowname,0,-1) . "\r\n";
    	    if (!fwrite($handle, $rowname))
            	{
                	echo "Cannot write to file $filename";
               	exit;
    	    }    
        	    $printcolnames = 0;
        	}
        // ========= end print column names ===================
        
        // ========= print col values ========================
		while($row = mysql_fetch_assoc($result)) {
// Create a row for every matching record
		    foreach($row as $r) {
				$r = str_replace(",", " ", $r);			     
		        $data .= $r . ",";
		    }
		    $data = substr($data,0,-1)."\r\n";
		}
    	    if (!fwrite($handle, $data)){
//	            echo "Cannot write to file $filename";
    	        exit;
        	}    
        // ========= end print col values ===================
// Close File
    fclose($handle);
// Dispaly Hyperlink to file
    	echo "<a href=".$filename." target='_blank'>Tab Delimited Results File</a><br><span class=chatter>(right-click -> Save Target As ... to save to local PC, then open with Excel)</span>";
	}
}
?>

[rscott7706]

Thanks jvrothjr, been away awhile, and didn't see your reply.  In the meantime I found a "canned" routine that works great.

 

But, I will play with this one also just to gain more practical knowledge. 

 

Thanks again!!