Automatically open MySQL query results in Excel

Nov 25, '02 09:13:50AM

Contributed by: Anonymous

While PHP is fine for displaying the results of a MySQL query in a browser, sometimes I need to actually use the results. I frequently find myself running quick and dirty queries on the same remote database and using Excel to review the results. I wrote a shell script that will take a quoted SQL statement as an argument and open the result in Excel. This has come in handy for me. Your milage may vary.

[Editor's note: I have not tested this hint myself...]

Follow these steps:

  1. Open terminal.

  2. Type sudo pico /usr/bin/sql_excel   [Editor's aside: You may wish to use something like /usr/local/bin or ~/bin, as these locations will have a lower chance of being overwritten during future upgrades.]

  3. Copy and paste the following code (you will have to supply your MySQL username, password [optional], database, and host [optional]). If you don't save the password in the text file, you will be prompted for it when you run a query. This is more secure.
    #!/bin/sh

    # Turn file globbing off so we are able to
    # accept asterisks and such.
    set -o noglob

    # Show the query
    echo "Query:"
    echo $1
    echo

    # Run the query (enter as one line, not two!)
    mysql -D database -u yourlogin -p password --host=remotehost
    -e "$1" > /tmp/sql_excel.tmp

    # Open the result in Excel (enter as one line)
    open -a /Applications/Microsoft Office X/Microsoft Excel
    /tmp/sql_excel.tmp
  4. Exit pico (Control+X) and save the changes to the file.

  5. Make the script executable (sudo chmod 755 /usr/bin/sql_excel).

  6. Type 'rehash'.

  7. Run a query by using running:
    sql_excel "SELECT * FROM table WHERE something = 'nothing';" 
    and hit return. That's it. The query results should pop up in Excel.

    Note that if you run a second query, it will overwrite the first query even if you have it open in Excel (unless you saved the file as a different name from Excel). You can therefore use Excel as a remote viewer as it updates every time you run a new query. Also, you may run multiple queries by separating each query with a semicolon. This is useful if you want to run queries on temporary tables from a previous query (MySQL's version of subselects).

    Comments (8)


    Mac OS X Hints
    http://hints.macworld.com/article.php?story=20021125061350898