Preface: What This Is Posted For

In the mid-2000s, I was doing a lot of SQL queries that were then massaged or presented by spreadsheets. In particular, I was loading huge table-outputs from SQL into Excel "Pivot Tables", which make most simple reports a breeze.

To save a few steps, I wrote an Excel Plug-In that opens up, logged into my favourite Oracle database, has a text window for pasting in your SQL query, and a single button to turn the query into an Excel Pivot Table.

The other buttons were all for very common things that people do with Pivot Tables: jumping the bottom row/column from SUM to COUNT, delete it, copy it.

I piggy-backed a whole other function onto TableWare: the TableSheet button takes you to a dialogue that makes it easy to upload an Access .MDB file up into Oracle, or other SQL database.

The software is offered with open source code. Any new user will want to change a few lines of code to make it log in to their own MS "ODBC" data source!

Available for advice, if anybody ever wants this - Microsoft "PowerQuery" may have made it partially obsolete.

"TableWare" Excel Add-In

Excel Pivot Tables are one of the best tools for data-mining. You can replace many custom reports with one pivot table and a little training in how to use it to filter and summarize data.

When your data is coming from an SQL database, it is convenient if you can directly generate the pivot table from an SQL query, and refresh it automatically as the source data changes. A tool that makes this very easy is this Excel Add-In: TableWare in the Add-In File TableWare.xla.

Help for the Main TableWare Form

Help for the TableSheet Tools that assist with MDB files and uploads

TableWare Cheat Sheet

The Pivot Table just below was generated in a moment from TableWare, by typing in the simple query:

select * from san_main

...then hitting the "Pivot Table" button after selecting cell A1 on the worksheet. The query brings in the entire 70,000 record SAN_MAIN table into the Excel pivotcache, then any number of reports breaking out sanitary mains by any of the table columns can be done with just mouse moves, no reporting code.

An overview of what TableWare does:

Installing TableWare (Excel 2007)

TableWare is a standard Excel "Add-In" file. The instructions for installing one are at THIS Microsoft documentation page. Briefly, you need to right-click on the link to the TableWare.XLA file in the paragraph above, and put it in a convenient location - your H: drive is ideal so that you can use TableWare on any City machine, not just your own. Then you use the same "MS-Office" button you use to print or save, and click on the Excel Options button at the bottom - then there's an "Add-Ins" menu on the options dialogue. You'll need to hit Browse and go find the directory you downloaded TableWare.XLA to.