Hammer Inside

As I explained in a previous post my main reason for developing HAMMER was to provide me with the same productivity boost in .NET as that provided by MicroETL when working using VBA.

Although the resulting tool is proving to be very useful in its standalone formats (as a UDF formula, VBA tool via Application.Run and as a command-line EXE) its use as  a powerful utility library within VB.NET or C# add-ins is still what justifies the cost of its development.

Over the last few weeks I’ve used HAMMER in the development of several ETL-focused add-ins with great success. That success is not just to be measured in speed and accuracy of development but in the extra features I can now deliver for little or no cost (caching of API or database calls for example). I term them my “HAMMER Inside” add-ins.

The final destination is to allow me to offer fixed-price (or at least transparent price) development of  ETL-focused add-ins. Not just the classic database or flat file backends, but WEB APIs and calculation engines (such as pricing /discounting tables) etc.).

Such add-ins would be “opinionated software”, in that they’d have a limited look’n’feel, with a concentration on the hewing and hauling of data, not on fine-detail Excel automation or advanced/pretty UI features.

In the meantime, HAMMER has been updated with a series of new COMMANDs:

  • JSON – Load a JSON document into a table.
  • TOJSON – Convert a table to a JSON document. If a single row table, will generate a JSON “dictionary” object with the column headers as the Keys, and the column data as the Values. If a multi-row table will generate a JSON List of Lists.
  • TOJSONLOL  – force a JSON List of Lists (when a single row table is really a table not a dictionary).
  • TOJSONLOO – force tables to be represented as a List of (Dictionary) Objects rather than a List of Lists.
  • TEXTTOFILE – output an argument as an UTF8 encoded text files e.g. HAMMER(myTable,”TOJSON”,”C:\files\myTable.json”,”TEXTTOFILE”)
  • TABLETOSCALAR – converts a table to a scalar value by picking the first column of the first row.
  • SQLRAW – Like SQL, but doesn’t perform any token substitutions. The range of tokens that can be substituted by SQL has also been increased. The existing “:n” tokens and “from tablen”, where n is the argument number, continue to be supported; but new formats of :ARGn ,:<ARGn ,:TABLEn, and :<TABLEn have been added.  As before, n will be the argument number for :ARG and :TABLE but in the case of :<ARG and :<TABLE, n will represent the previous nth argument or table (think “<’ pointing backwards).
  • URLGET and URLPOST – fetch or post data to a URL.
  • There’s also a series of “_” functions, mainly intended for use within VBA or .NET but could be useful as formula calls too, I guess. These are: _URLENCODE (encode a URL), _HAMCSHA1 (calculate a HMACSHA1 hash),_MD5 (MD5 hash),_MD5FILE (same but for a file),_GETCONFIG (reads addin’s .config file’s HAMMER JSON section) and _GETCONFIGNOERROR (same but doesn’t complain if no .CONFIG file or no JSON section found).