SQLite-OLE 2019-04-27

New version of the SQLite and OLE tool for Windows.  Rightly or wrongly I am quite proud of this tool. Whilst my colleagues are using Excel to do a lot of data analysis they are hitting problems which are trivial in a SQL database environment.

Meanwhile I am running multiple instances of the SQLite-OLE tool on my laptop, often with databases approaching 2 GByte in a single instance. Because the tool is ~ 95% command line orientated, it forces me to create scripts. This has the massive advantage over a WIMP orientated tool because it means that I automatically have a script that I can save and reuse/rerun at a later date and not worry about “how did I do this last time”.

As a consequence of scripting I have found that I have adopted a “fire and forget” attitude. I focus on a task, script it, save it, and then focus on the next task. I do not need to commit to human memory the steps needed to complete a task. Given that I am running/developing scripts every day, I find that “fire and forget”/ scripting frees me from worry.

The output of the various tasks is usually in a Excel file (for easy exchange with my colleagues), the exporting of SQL query results to Excel is built in to the tool. Each Excel that I send out, contains an Admin sheet that identifies the script used to create the Excel file.

So if I am asked to modify/update an Excel file I simply ask the requester to identify the script (from the Admin sheet) and then I create a new version of the script and run it.

Some of the scripts are over 2000 lines long (with comments and lots of space to make it readable and less visually frightening).

The scripting language has similarities with Microsoft VBA to minimize the learning curve, and as the tool’s name implies it supports OLE.

What have you got to lose, go ahead and use it!