I’ve uploaded another version of HAMMER; this adds some new features and also takes some away. The removed features are Python and multi-threading support from the 2003 version of the add-in. Calling it the 2003 version isn’t entirely accurate (it’s actually called datasmith-noPython.xll) as this version will also work for Excel 2007/2010 32bit and for older versions (maybe even ’97!). It should really be called the .NET 2.0 version as the features removed from this version depend on the NET 4.0 runtime (IronPython 2.7 and multi-threading). I’ll eventually build a .NET4 version for Excel 97-2003 with Python included, but this will still be missing the multi-threading features.
So, the version that the setup.xls will install if it detects a sub-2007 version of Excel, will offer SQL and noSQL (JOIN,UNION etc.) but no Python or multi-threading.
So what about new features? Excel being the original noSQL database, I continue to add more noSQL commands for those who wish to avoid SQL or find its syntax somewhat long-winded. The JOIN & LOJOIN (outer join) commands are good examples, simply load two tables with the column names that you wish to join on, sharing the same names, simple. Another example is the REDUCE (aka GROUPBY aka DISTINCT) command I’ve added this version. It essentially performs a SELECT … FROM … GROUP BY; again load or generate a table, then follow with a list of the columns you wish to ‘reduce’ the table by, plus any aggregates you wish to perform. Examples:
If noSQL is not your cup of tea and you wish to utilise the full power of a SQL database; a new command “OPENDB” will allow you to open an existing (or create a new) SQLite database file. This will allow SQLite data sources to be accessed and written to via standard in-cell formula, no VBA required! The command expects the previous argument to be the database name. If no such argument exists it will create a temporary on-disk database. This command usually only makes sense as the 1st command as it’ll close and wipe any previously opened databases. If no “OPENDB” command is issued (i.e. the default) an in-memory database (aka :memory:) is used . Examples:
- =HAMMER(“C:\data\myDB.db”,”OPENDB”,A10:C:9910) will copy the data for range A10:C9910 and save in a table called table3 in the myDB.db SSQLite database.
- =HAMMER(“C:\data\myDB.db”,”OPENDB”,”SELECT * from table3″) will fetch the same data back into Excel.
Wow steady on, what if there’s a need to store or fetch data from disk without using SQLite? No problem, use the “TOCSV” command, outputs the last table loaded or generated in CSV format to the file name specified. (There’s also a “SQLTOCVS” command which expects a SQL statement to specify the data to extract followed by the file name to extract to).
Two other commands “CSV” and “TSV” will load comma and tab separated data into HAMMER. Although the CSV functionality is useful within Excel, the main driver for these command is to enable HAMMER to work outside Excel as a command-line data processor; you heard it here first folks!
I’ve also added the 1st set of my helper functions, these two functions are only available in the 2007/2010 versions as they use multi-threading. The two functions are:
- hammerToFit – wraps HAMMER, but will auto-resize the array area (or create a brand new array-selection if none) to fit the returned table. Note: to achieve this, the HAMMER function will be called twice if the existing array area needs adjusting.
- hammerToSheet – again wraps HAMMER, but will paste the resulting table to a new sheet.
Although both helper functions utilise threads to achieve these little tricks (hence they’re not available sub-2007) when HAMMER functionality is called via these wrappers the function operates as a single threaded function – there’s a good reason for this which I’ll explain some other time. Internal HAMMER threading does however still work.