MySQL -> Excel spreadsheet

[Anon]

Is it possible to output data from a MySQL database directly to an Excel spreadsheet under Linux?

If not does any one know if the PHP guys intend to implement any functionality like this.

Cheers,
Mick.

[Anon]

You can export data as a csv file and import it into an excel sheet.
But, once you have MySQL, why would you want to go back to excel? :-)

[Anon]

I don't really want to have to put it into a CSV file I wanted to be able to use PHP to output the results of a select to a spreadsheet automatically.

Suffice it to say the financial types still like their data in Excel format.

Mick.

[Anon]

would a comma separated/tab file do?

I've written class that will extract data or insert data, tab or comma separated into or out of MySQL

Andreas

[Anon]

You CAN setup an odbc connection to a excel database and the insert the data from mysql into the excel odbc connection. It will take some time to setup, of course.

As far as a result set > excel, I think ms has excel locked up enough that you have no choice except com and odbc, both implemented through windows.

[Anon]

I wrote a function that allows our customers to download data from our MySQL database into Excel through a web browser. You basically output a tab delimited file with a special header. When the web browser reads the output it will even spawn an Excel session (I've tested this on NT and Mac).

function downloadToExcel($result)
{
Header("Content-type: application/vnd.ms-excel");
Header("Content-Disposition: filename=file.xls");
// loop through the mysql result set
for($x=0;$x<mysql_num_rows($result);$x++)
{
// get a row
$row = mysql_fetch_row($result);
// print column header row
if($x==0)
{
for($y=0;$y<count($row);$y++)
{
if($y!=0) { print("\t"); }
print(mysql_field_name($result,$y));
}
print("\n");
}
// output the column data
for($z=0;$z<count($row);$z++)
{
if($z!=0) { print("\t"); }
print($row[$z]);
}
print("\n");
}
exit;
}

[Anon]

Thanks Greg,

Thats getting very close to what I want to do. Any chance you know if you can specify different spreadsheets?

If so maybe we should take it off the forum and stop cluttering it up.

Thanks, Mick.

[Anon]

if you got an answer to that id love to hear it, im trying something similar here

[Anon]

i need help with that function, i need to pass the data from mysql database to a excel sheet, but not in the html page but in the excel program, so i can save the data as a excel file, txs

[eklee]

Cesar,

Have you found the solution? I want to do something that same as you.

Thanks
louis

Cesar wrote:

i need help with that function, i need to pass the data from mysql database to a excel sheet, but not in the html page but in the excel program, so i can save the data as a excel file, txs

[Anon]

How would I format this data? Can you just apply the data to a template you've already created in Excel?

[Anon]

well,

you can just export all the data into txt file or xls file.

select * from [table name] into outfile 'c:/hello.txt';
it will be saved as a tab delimited text file, and you can also save it as xls.

Good luck.

sunny

[Anon]

hi sunny,

I try your SQL query:

select * from MyTable into outfile '/tmp/hello.txt';

However, it always return me error :

Error
SQL-query:

select * from tblRaceInfo into outfile '/tmp/hello.txt';MySQL said: File '/tmp/hello.txt' already exists
Back

I am using MySQL 3.23.39.

Thanks
Louis

[Anon]

Hi, Louis

The sql query does not replace already existing file. Why don't you delete the file again or use different file name and try again.

Select * from tblRaceInfo into outfile 'a:/hello.txt';

Good luck

Sunny

[Anon]

Hi sunny,

ok got it, the sql statement do work, just it didn't show me it was succesful in the phpMyAdmin.

One question here, the result doesn't carry the data fileds' name?

Thanks
Louis

[Anon]

Hi louis,

no, it doesn't carry the fields names. but you can write macro in excel to do so.
Good luck,

sunny