Export mySQL to excel or csv

Question

I'm no php expert (a mere beginner) but need some help!

After hours searching Google and trying out about 100 different scripts, I finally found one that does what I need - almost.

Basically, my site has a button marked 'Export to Excel'. Visitor to site clicks button and a download begins containing all data from a specified table.

I found this on here - PHP code to convert a MySQL query to CSV

which does exactly what I want except the user sees the following error when trying to open the file:

Error - 'The file you are trying to open, 'export.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Wo you want to open the file now?'

User clicks 'Yes' and file opens with all data! Brilliant! Except users will not open the file with this error.

I would be very grateful if someone knows a way to fix this.

Many thanks

TT

are your users using office 2007 ? Have you tried header("ContentType: application/vnd.ms-excel") ?? — Perpetualcoder, Jun 10 '09 at 17:46
@TheTub check out http://stackoverflow.com/questions/976483/exporting-gridview-to-excel-in-web-app/976566#976566 for another thread dealing with this same problem. — Matthew Jones, Jun 10 '09 at 17:48

score 5 · Accepted Answer · answered Jun 10 '09 at 17:47

5

Or, you could just change the script in the above solution to return a file with the .csv extension. .csv files are associated with Excel, so they should open directly.

answered Jun 10 '09 at 17:47

JeffP

1,086
8
12

Hi Jeff - This works and gets rid of the error message but the file now splits the data from the table (there are 9 columns and around 90 rows as I type) into uneven chunks. One field is 3000 chars and it seems to not like this one at all. If exported as .xls then at least the data is kept in tabular format that can be used. – TheTub Jun 10 '09 at 18:08
Most likely, you're experiencing something nominally within your data fields that breaks the CSV-ness of it. IOW, the following line wouldn't work like you intuitively think it should: name, ssn, birthDate Blow, Joe, 123-45-6789, 2009-09-30 In short, if your data fields have commas, you'll need to look into your export script to wrap those fields with qualifiers (like double-quotes). I fight with putting dirty data into CSV format on almost a daily basis. You need to look at the raw CSV (in notepad), and think like a parser. – Adrien Jun 10 '09 at 18:18
I don't think it is a problem with the layout of the file, otherwise it won't work when it is named .xls. If it is working when named .xls, try .txt - Excel is probably using something other than the CSV parser when it reads the file. – JeffP Jun 10 '09 at 19:28
Yep - I just looked at the code on the other page, and it is actually tab separated, not CSV. If .txt doesn't work, you could replace the "\t" in the code with "," – JeffP Jun 10 '09 at 19:29
Interesting; I'm still on Excel 2003. If I put comma-delimited data in a plain text file, call it foo.xls and open it, all "fields" go into one cell. But, if I replace the commas with tabs, it opens correctly (that is, each field in its own cell). That said, I still think the issue is best solved by making the script put out a clean CSV, which may mean some analysis of the data, and wrapping fields where necessary. – Adrien Jun 10 '09 at 20:10
I just noticed this comment from Jeff - "you could replace the "\t" in the code with "," " This is very nearly there. The file opens fine! And is arranged with column headers. However, where there is an empty field (say telephone number), it populates with the next available data. Meaning from that point onwards the data is out of line with the column headers. So I guess I need the code to insert something when the data field is empty. – TheTub Jun 16 '09 at 10:52
Scrub previous comment. I had not replaced one of the "/t" pieces of code. However, I now have another problem! The telephone numbers are being converted to scientific notation! For example, a telephone number now reads 4.876534E+11. – TheTub Jun 16 '09 at 11:00
Before anyone asks - the field in mySQL is set as varchar(50). Most of the telephone numbers are coming over OK. It seems that those over a certain length are being converted. – TheTub Jun 16 '09 at 11:04
Try running this script on your phone number data - it will force it to look like text to excel: http://support.microsoft.com/kb/208414 – JeffP Jun 16 '09 at 12:47

score 2 · Answer 2 · answered Jun 10 '09 at 17:42

Ok, this results from a feature specified by Excel 2007 called Extension Hardening. You can turn it off, but that can only be done client-side. If you click "OK" or "Yes" the file should open anyway. Check this blog post for more info.

EDIT: What this means is that Excel is finding that the file is of a different type (say HTML or CSV) that what is specified by the file extension. Therefore Excel wants to warn you that this file is not what it says it is. Unless you are going to create native Excel files on the server then prompt the user to download them, there is no getting around this error except for each user to turn off Extension Hardening on their own computer.

Thanks - this explains the error message. I might look into Jeff's solution below to see if I can get the data to sit nicely in a .csv file. — TheTub, Jun 10 '09 at 18:10

score 1 · Answer 3 · answered Apr 10 '16 at 07:15

if you make the first letters “ID” of a text file Excel incorrectly assumes you are trying to open an SYLK file.

Meaning if the first row & column value is "ID", Excel will throw this warning. Just change it from "ID" to anything else.

Credit: http://alunr.com/excel-csv-import-returns-an-sylk-file-format-error/

score 0 · Answer 4 · answered Dec 17 '12 at 12:47

0

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Set objXL = New Excel.Application
'turn off excel warnings
objXL.DisplayAlerts = False
'Open the Workbook
Set objWkb = objXL.Workbooks.Open(fpath)

answered Dec 17 '12 at 12:47

Sooraj Bathery

1

score 0 · Answer 5 · edited Dec 17 '12 at 13:09

0

functions sendFile($filename,$content_type="application/ms-excel") { 
  header('Content-type: '.$content_type);  
  header('Content-disposition: Attachment; filename=' . $filename);  
  readfile($filename);   
}

edited Dec 17 '12 at 13:09

answered Dec 17 '12 at 12:52

Sooraj Bathery

1

score 0 · Answer 6 · edited May 23 '17 at 12:01

I had the same problem so I looked at the following link: PHP code to convert a MySQL query to CSV

I modified one of the answers to get the headers to work.

include('DBFILE.PHP'); 
$select="SELECT * FROM SOMETable";


$result = mysqli_query($conn, $select);
if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);

//This is what I changed...
$headers ="";
while ($property = mysqli_fetch_field($result)) {
    $headers.= $property->name.",";
}
$headers.="\n";
//////

$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

I Tested this and it works like a charm, you just need to add your db connection or include the db.php file that you have.

you can change the name of the file if you edit the following line

header('Content-Disposition: attachment; filename="export.csv"');

Change export to what ever name you like.

Export mySQL to excel or csv

6 Answers6