SQL noSQL no Python no VBA.

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:

  • =HAMMER(myHugeList,”dept,sum(overtime)”,”REDUCE”)
  • =HAMMER(AccessLogs!A1:C9999,”areaAccessed,byWhom”,”REDUCE”)
  • =HAMMER(invHead,invLine,”JOIN”,”count(invID),sum(netAmt)”,”REDUCE”)

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.

Here’s a list of the HAMMER commands implemented so far …

Download the latest version of HAMMER here …

Advertisements

5 responses to “SQL noSQL no Python no VBA.

  1. Hi Tom —
    It’s been a bit (new job, new challenges). I was reviewing some of your old examples and couldn’t find what I was looking for so here it goes. In the past I wrote subs to handle application.Run(“Hammer”…) calls but it was pure habit from before you had written the wrappers. Is it possible to run the wrapper commands in vba? In particular I’m looking at passing the hammerToRange function to return the results of an ado connection to a predefined table (using your noheaders option), if you could give an example of the syntax here it would be huge help!

    -Sean

    • Seán,

      Yes. Same as before but .Run(“HAMMERTORANGE” ..

      Any UDF can be run in this fashion. Don’t use NOHEADER, if the range points to a Table, HAMMER will note the table name, paste the the data and convert the range back into a table of the same name. You can also call VBA subroutines Pre and/or Post pasting if further formatting is required.

      Tom

  2. Hmm. I have found the arrayTo…() functions since this last post and they will do what I want but I can’t seem to get the arrayToRange func to work (the other two work as expected). I did just notice my version of hammer is a bit out of date though, will report back on if whether an update fixes it.

  3. See the http://blog.gobansaor.com/2011/10/04/hammer-on-the-range/ (and if your using COM automation http://blog.gobansaor.com/2011/10/07/hammer-alongside-as-a-com-server/). The latest COM server is now using a new interface (V2) and requires to be manually registered (see the Debug code in the InProcess_OData example).

    The most common mistake with ToRange is referencing an actual Range/Cell object as the final “paste to” argument, it must be a string representation of an Excel Address e.g. “Sheet3!A1”. Also make sure to fully qualify the address i.e. don’t use A1 put a Sheet reference against it (and a Workbook if multiple workbooks likely).

    Tom