Having upgraded xLite to be 64bit compatible and as a result having to re-test it, I decided to make some significant changes to how it works. Although xLite was, and is, SQLite embedded in Excel, having access to SQLite databases is not the reason I’ve combined the two products. The manipulation of Excel tables using a SQL dialect, all the while operating in memory, is my goal.
Having the ability to access external disk-based SQLite databases can be useful (particularly when dealing with very large fact-like tables) but it’s a perk not the primary use-case. The main aim is to bring the data-handling power of a SQL engine to bear on Excel tabular datasets; if Excel offered this by default, I would not be embedding SQLite. SQLite is a means to an end.
So what changes have I made?
- First off, I’ve added a SQL helper form (right-click on cell context menu, and pick SQL). The form not only makes issuing SQL statements easier but also holds a history of commands if required.
- Secondly, I’ve introduced a pre/post processor for the SQL() function.
- I’ve also added two SQLScript (new name for xLiteScript) helper forms. One to run a SQLScript from the right-click context menu, plus a lookup form to help select the required microETL function along with a description of the parameters required.
Examples of the new SQL() pre-processor in action:
=SQL(“Select * from TABLE(Sheet2!A1)”,”Sheet1!B17″)
This will paste the table found at position Sheet2!A1 to Sheet1!B17.
=SQL(“Insert into TABLE(Sheet1!B17) Select * from TABLE(Sheet2!A1))
This will do the same as above, difference being that for this to work, an existing Excel table at Sheet1!B17 of the same structure (i.e. same headings) as Sheet2!A1 must first exist.
The TABLE(…) command tells SQL() to go fetch the relevant Excel ranges, loads the “tables” into SQLite; when the SQL command finishes it writes back the data (if changed) to the originating Excel address, and cleans up any temporary SQLite-side tables. Nearly all SQL commands can be issued against the resulting tables, including UPDATE, DELETE, DROP and CREATE TRIGGERS etc.
Two other pre-processor commands are CELL(…) and CELLS(…). Similar to TABLE(…), data is loaded from the Excel addresses specified and placed in the SQL statement. CELL takes a single cell reference while CELLS will iterate through a multi-cell range and insert a comma-separated list into the SQL statement (in a format suitable for IN (…) expressions).
SQLScript is a simple table-based command sequencer (ETL is primarily a step-by-step process) which can be used as an alternative to VBA when a simple set of events needs to happen in sequence. The optional SQL history generated by the SQL helper form is held in a SQLScript format and written back to an Excel table on exit, allowing for the creation of repeatable scripts based on previous SQL commands.
I still need to fully test the new functionality and will do so over the next few weeks before I publish a beta version. If anybody wants to try the un-tested alpha version let me know and I’ll send you a copy. you can find it here.
See http://blog.gobansaor.com/2011/03/09/microetls-sql-function/ for a more detailed description of the =SQL() function.
The table() command now supports virtual tables e.g. “select * from table(vtCSV,C:\data\myData.csv)” see http://blog.gobansaor.com/2011/05/05/excel-document-oriented-database-with-python-map-sql-reduce/