Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Automatically open MySQL query results in Excel UNIX
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).
    •    
  • Currently 2.33 / 5
  • 1
  • 2
  • 3
  • 4
  • 5
  (3 votes cast)
 
[13,535 views]  

Automatically open MySQL query results in Excel | 8 comments | Create New Account
Click here to return to the 'Automatically open MySQL query results in Excel' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Slight Modification
Authored by: nvdingo on Nov 25, '02 01:18:22PM

Change 1
most shells don't like spaces, and i received an error the first time i tried this, so the line should be:
pen -a /Applications/Microsoft\ Office\ X/Microsoft\ Excel /tmp/sql_excel.tmp

Change 2
since i have 10 different databases that i routinely work on, on the same server, i changed the mysql command a bit so that command line parameter one is the database and two is the query
mysql -h myhost -u userid -ppassword -e "$2" $1 > /tmp/sql_excel.tmp
and of course, i changed teh echo command to spit out $2 instead of $1

Additional option.
I play with mysql data so often that i aliased the command and some common queries in my .cshrc file



[ Reply to This | # ]
RE:Slight Modification
Authored by: nvdingo on Nov 26, '02 01:16:11PM

\0\05{\0\0\0\0\0\0\0\0to the other 2 posts that popped up after this.
I should add that, i have my MySQL connections tunneled through SSL.

AND the user that i login in as in MySQL for this, has SELECT only permissions.
no DELETE no UPDATE, and DEFINITELY no DROP.



[ Reply to This | # ]
Security issues if used on a server
Authored by: Erik Toh on Nov 25, '02 10:01:30PM

This should be OK if it's meant to be used as a stand-alone machine and not at the server-level. There are at least 2 security-related problems with this script:

1) The password to the MySQL database is written in plaintext and the script is set to world-readable. A slightly better idea is to put it in ~/bin and set it to execute only. Of course if connecting over a network the MySQL password would have to be encrypted as well.

2) There is no check on /tmp/sql_excel.tmp. Someone could stick a macro virus in there or make use of race conditions to change the legitimate file with a doctored one (previously proven to work on Linux, not sure on OS X though). Again, it's safer to do stuff in your own home directory.



[ Reply to This | # ]
Security issues if used on a server
Authored by: Erik Toh on Nov 25, '02 10:26:51PM

Additional point. The SQL commands are not validated. Someone could just type this:

sql_excel "DROP DATABASE xxx"

And it would get executed.



[ Reply to This | # ]
What about FileMaker?
Authored by: bluehz on Nov 30, '02 08:03:06AM

Any possibility of using some variant of this tip with FileMaker?

Why can\'t we just have simple, usable ODBC connectivity like the rest of the world? Sure I know SOME ODBC support is built-in....but thats all theory - I have never heard of anyone actually getting it to work!



[ Reply to This | # ]
additional hint...
Authored by: catnip on Dec 02, '02 05:32:37PM
This may work better for you if you include double quotes (") in the lines where you have to call directories.

open -a /Applications/Microsoft Office X/Microsoft Excel /tmp/sql_excel.tmp

becomes

open -a /Applications/"Microsoft Office X"/"Microsoft Excel" /tmp/sql_excel.tmp

[ Reply to This | # ]
Microsoft Query
Authored by: cgull on Dec 10, '02 09:30:19PM

As you may or may not be aware, Micro$oft released a Query tool for Excel, but it is lacking an ODBC client!!!! Losers!!! The Windows version has one! Anyhow, I'm a little miffed here...

I got the one working from OpenLink software that works well as in I can run SQL queries to my MySQL database running on my Linux server, but it's $495!!!! What's up with that?!!! (I'm running the 30 day demo)

Anyhow, I'm in the process now of downloading OpenOffice (ie. Sun's StarOffice) crossing my fingers that it has an ODBC client with it.

If anyone knows of a Freeware ODBC driver for OSX, that would make my day!



[ Reply to This | # ]
Save $495!
Authored by: cgull on Dec 12, '02 10:03:11PM
http://www.macosxhints.com/article.php?story=2002121206540038 Here's the solution!!! Works like a charm and saves you $495!!!

[ Reply to This | # ]