Spending time on Excel-SQLite, C, VBA Callbacks & Twitter

Haven’t posted here in a while as my spare time has been soaked up programing, well actually refactoring would be more exact.  My xLite “SQLite empowered Excel” codebase has grown over the years and required a serious makeover to get rid of stuff I no longer use and to generally make it more robust.  I also decided to add some extra functionality to my VBA friendly C wrapper for SQLite (based on Pivotal Solutions’ pssqlite.dll) which meant I had to re-acquaint myself with my long lost C skills, so doing reminded me how much I like C. Close to the metal programing if not exactly super-productive is nevertheless super-powerful.

The new improved xLiteSQLite.dll now has a built-in CSV loader (both file based and string based – handy for loading Palo HTTP API responses into a table). It also returns a one columned variant array of CSV values for quick rendering via “text-to-columns” code (by far the quickest way of handling large dataset pasting into Excel).

I’ve also added the ability to create SQlite UDFs (user defined functions) in VBA (thanks to http://stackoverflow.com/users/4007/rpetrich).  This is a very powerful feature as it allows SQLite selects to act as a “loop controller” calling back to  Excel/VBA functions to process each row, really useful for ETL tasks. And not just scalar UDFs but aggregating (aka group-by) functions too, allowing the use of Excel’s powerful array functions in SQLite statements.

All in all, the changes to the xLite VBA code and the C wrapper makes Excel backed by SQLite a seriously good micro-ETL tool. Combined with Palo, the result in a truly wonderful micro-BI platform; a cost-effective toolset for these recessionary times.

Of course I’d be lying if I said code was the only reason I’ve been neglecting my blogging duties, I’m afraid I’ve a confession to make, Twitter has hooked yet another sucker, me! 

I’ve found I’ve settled in to the whole micro-blogging thing with ease, and have managed to make contact with people I would not have encountered otherwise, as well as reconnecting with others that I’d lost contact with.  So if you too are all-a-twitter then do please follow gobansaor-on-twitter.

Advertisements

3 responses to “Spending time on Excel-SQLite, C, VBA Callbacks & Twitter

  1. Hi, thanks for making such an awesome module. However, I can’t figure out how to write a simple SQL command in it.

    I have a SQLite database set up on my hard drive and I just want to use it to do a query.

    E.g.

    select * from table;

    and return the result to a ADODB.recordset or directly into some Excel range. How do i do that? What the sequence of module I have to use to accomplish that?

    Thanks

  2. Hi Tom

    As I know your a fan of Excel and Talend I thought you might be interested in a tool I’ve just written. The code is available on talend exchange. I hope to release the mindmap code soon but a hectic workload and a bug preventing elements rolling up into multiple parents prevented this (oh when are the markets going to return to normal?)

    You can readmore about the tool here http://hugoworld.wordpress.com/2009/02/08/taking-the-pain-out-of-excel-reporting/ about half way down the page is a link to a PDF which gives more details about the extension and provides some tutorials

    Regards

    Hugo