Export mySQL to excel or csv

Hi,

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 - http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv/125125#125125

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

+1 ... I came here to say this. Or something.

Adrien 2009-06-10 17:49:14

+1 ... Ditto what Adrien said.

Abinadi 2009-06-10 18:03:40

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 2009-06-10 18:08:49

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-30In 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 2009-06-10 18:18:33

Err, that didn't format like I wanted it to. Oh well.

Adrien 2009-06-10 18:22:03

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 2009-06-10 19:28:01

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 2009-06-10 19:29:46

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 2009-06-10 20:10:50

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 2009-06-16 10:52:34

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 2009-06-16 11:00:12

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 2009-06-16 11:04:02

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 2009-06-16 12:47:26

ansaurus

tags:

views:

answers:

Export mySQL to excel or csv

related questions