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.

    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? :-)

      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.

        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

          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.

            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;
            }

              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.

                7 months later

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

                  5 months later

                  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

                    4 months later

                    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

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

                        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

                          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

                            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

                              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

                                Hi louis,

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

                                sunny

                                  Write a Reply...