Microsoft Excel: Feature Request
After my deification of Microsoft Excel, I was thinking that there is one feature I wish Excel had that it doesn’t. Note: this is a serious power user feature.
Why can’t an Excel Workbook be queried with a SQL Query Tool? A workbook is a database, each worksheet is a table, and each row is a record. Isn’t this obvious?
I often find myself importing an Excel Worksheet into Microsoft Access, so that I can then run queries against the data. In fact, I do this regularly.
Does this tool already exist?
It doesn’t seem like this should be very hard to build, and I’m certain there are all sorts of people out there in the business and academic worlds that benefit from it.
When you open Access, it should ask you if you would like to create a new database or open an old one, if you choose open, you can browse to an excel workbook and link it into Access. From there all data is stored in the workbook, but you are working in the Access UI.
Matt Rude
4 Mar 10 at 10:13 am
Google spreadsheets actually does this. It’s pretty damn sweet.
=query(Tweets!A2:F ; “SELECT A, COUNT(B), MAX(F) GROUP BY A ORDER BY COUNT(B) DESC, MAX(F) DESC Label COUNT(B) ”, MAX(F) ” “)
More details on it here: http://code.google.com/apis/visualization/documentation/querylanguage.html
Damon
4 Mar 10 at 10:14 am
There’s a sort of convoluted way to do it using the MS SQL tools (SQL Server MGMT Studio, etc).
If you create a linked server to the Excel spreadsheet from a SQL instance, you can then query the tables using SQL.
Generic directions for the linked server (using the Jet provider) are here: http://support.microsoft.com/kb/306397
Assuming you created a linked server called “EXCEL_EXAMPLE”, you could return the values in Sheet1 with this:
select * from EXCEL_EXAMPLE…Sheet1$
JustinS
8 Mar 10 at 9:27 am