How to run a SQL Query on an Excel Spreadsheet

April 7, 2006 11:26 AM

I have an Excel Spreadsheet (using Excel 2003). Rather than creating formulas to answer ad hoc questions that I have, I would like to be able to quickly type in a SQL querty against the data in the spreadsheet. So, I could scroll down to the bottom of the sheet to see how many rows I have, OR I could type" select count(*) from data_table" instead. I prefer the latter. Any suggestions?

I know I could write a VB/VBA program to connect to the spreadsheet using ADO etc... I would like to avoid that. I would prefer to download a free or inexpensive add-on for Excel or write a simple macro that will prompt me for the SQL?
Any clues? TIA...

posted by apark to Computers & Internet (6 answers total)

Why not just export your Excel data as a CSV file, import it into the SQL database of your choice, and run queries there? You could use a free database like MySQL or Postgres and if you really wanted to, you could write a little script to automatically update the database with new data from the Excel spreadsheet.
posted by zachlipton at 11:51 AM on April 7, 2006

Ssssort of.

Data > Import External Data > New Database Query. Choose Excel Files, and you can query it with an SQL statement. Is this easier than creating formulas? Maybe, if you've got a particularly complex statement to execute against your data.
posted by boo_radley at 12:00 PM on April 7, 2006

Response by poster: zachlipton-->Yeah, I could do this. I have also imported the data into Access to do this, but I get new spreadsheets from various sources constantly and I don't want to have to re-import/create new dbs constantly...

boo_radley-->I'll try that... Thanks!
posted by apark at 12:49 PM on April 7, 2006

Do you have MS Access? You could create a DB and link to the Excel sheet and then run queries against it as if it were a native table. To do that, create a new DB and then choose Insert > Table > Link Table. Change "file of type" to show Excel files and select your spreadsheet. Walk through the wizard and you'll have a link to the sheet and can treat it as if it were a table in access (including updating it).

The query builder tool in Access has an SQL view you can use, if you prefer.

Alternately, you could import the sheet into Access as a table and do the same.
posted by wheat at 12:54 PM on April 7, 2006

Response by poster: I had been importing the data into Access, but I haven't tried the DB link trick yet. I'll give it a try... You know what I just realized that what I really want is something similar to SQL*Plus that works w/ Oracle DBs... (except for Excel instead)
posted by apark at 1:03 PM on April 7, 2006

I really do wish Excel had better support for this sort of thing. As I often end up linking or importing into Access for it. Linking is a little better than importing, but (with Access 2003, at least) you're still forced to create a DB file.

I often end up creating these files ad hoc and then throwing them away. But you could create one Access DB just for the purpose of creating ad hoc queries and link in whatever worksheets (or other files) you need. I don't know of an upward limit on the number of files that can be linked. And, that way, you'd be able to save your queries, should you ever need them again.

It's not a perfect solution, of course. But it probably will get you the answers you need fairly easily and quickly.
posted by wheat at 11:48 AM on April 8, 2006