Write a Script to Send Database Query Results to Excel

Viewing database query results in the terminal is fine, but sometimes I need to get the results into Excel quickly so I can prod and analyze the data to see if I need to modify the query. Ideally I’d just query directly from Excel using and ODBC driver but our options for connecting to most databases via ODBC with OS X are either complex or expensive. I came across a handy tip on macosxhints.com that I made some modifications to and it’s been working great Read the rest of this article to see my version of the script and how you can adapt it for your situation.

If you haven’t done so already see the original hint at macosxhints.

Now here is my version of the script that I use:


#!/bin/sh

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

# Must pass a query as an argument
if [ ! $1 ]
then
  echo “Usage: sql_exec \”Input Query\””
  exit 1
fi

# Show the query
echo “Executing Query:”
echo $1
echo

# Run the query
mysql -D yourdatabase -u yourlogin -p password –host=yourdatabasehost -e “$1” > /tmp/sql_excel.tmp

# Check to see if the command was successful before we open Excel
if [ $? = 0 ]
then
  # Open the result in Excel
  open -a /Applications/Microsoft\ Office\ 2004/Microsoft\ Excel /tmp/sql_excel.tmp
else
  echo "Error executing mysql command"
fi

Make sure you replace the connection string values with your own. It should be easy to modify this script for alternative databases, such as PostgreSQL. If you have any problems running this script let me know.