Error message when exporting mysql data to excel

Question

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "username"; //MySQL Username     
$DB_Password = "password";             //MySQL Password     
$DB_DBName = "databasename";         //MySQL Database Name  
$DB_TBLName = "tablename"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>

I tried to use the Peter's code as above. The data is exporting nicely but the following error message is being thrown:

The file format and extension of 'database.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source don't open it. Do you want to open it anyway?

I have tried with different file extension but could not fix it.

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 30 '15 at 14:00
[How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) — wogsland, Dec 30 '15 at 14:01

score 1 · Accepted Answer · answered Dec 30 '15 at 14:01

1

The reason of the error is that you are creating a text file, with field separated by tab, and not an Excel file.

Simply change the lines:

header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.xls");

with:

header("Content-Type: text/plain");
header("Content-Disposition: attachment; filename=$filename.txt");

or, alternatively:

header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=$filename.csv");

Then you could open this file also from Excel.

answered Dec 30 '15 at 14:01

Renzo

26,848
5
49
61

The data is exporting in single cell. – rns Dec 30 '15 at 14:20
There are different ways of writing a csv file. You could also substitute in the program the character "\t" with a comma, if there are no commas in the fields (or put double quotes (") around the fields). Otherwise, you must tell to Excel how to interpret the file, by specifying that fields are separated by tab. – Renzo Dec 30 '15 at 14:24
Thank you for solving the problem. – rns Dec 30 '15 at 14:44

Error message when exporting mysql data to excel

1 Answers1