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:
- Open terminal.
- 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.]
- 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
- Exit pico (Control+X) and save the changes to the file.
- Make the script executable (sudo chmod 755 /usr/bin/sql_excel).
- Type 'rehash'.
- 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