Exporting MySQL data to Excel gives errors

Question

While exporting data to excel, using PHP, it gets exported, but i keep getting these error messages:

"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?"
"Excel has detected that 'database.xls' is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format. Clic OK to try to open in a different format."

After clicking OK a few times, it does open, but I'd like to know if there's an easy way to fix this.

This is the code (used John Peter's code):

<?php
    //db connection data  
    //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";
    }   
?>

The first message occurs because you are generating a tabulator delimited CSV file but then you are telling the operating system with content-type and your file extension .XLS that this is a Excel file. For the possible reason of the second message see: http://support.microsoft.com/kb/215591/en There are PHP solutions available which really can create Excel files. See your linked SO post. — Axel Richter, Nov 18 '14 at 13:53
@AxelRichter Can you please post a link to one of those solutions please? Thank you in advance — bloomingsmilez, Feb 23 '15 at 08:59

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

These errors are not related to the programming language used to create the file. Both these errors are caused by a single bug in MS Excel.
MSDN:

When you try to open a text or comma separated variable (CSV) file, you receive the following error message:
SYLK: File format is not valid
This problem occurs when you open a text or CSV file and the first two characters of the file are the letters ID (uppercase).

For complete details, refer https://support.microsoft.com/en-in/help/215591/-sylk-file-format-is-not-valid-error-message-when-you-open-a-file-in-e

score 0 · Answer 2 · answered Apr 25 '19 at 14:09

I was facing this problem since last 1-2 years but I was ignoring because few of my file were not generating this error and few of were generating.. Finally after long time I found something very interesting.

To remove this error just ensure that your data is not starting with ID that means string that you are exporting to xls file should not start with ID it can start with any other string.

My first column name was ID in some tables so I was getting error for those files and those who dont have ID as first column name was not creating this error.

I came to know this because of a blog you will find it here https://www.recovery-tools.org/techtalk/fix-sylk-file-format-is-not-valid.html

Thanks

Exporting MySQL data to Excel gives errors

2 Answers2