A few months ago, I showed how you can do simple back and forth ETL task with python scripts – this time I’m going to take it one step further and show how with an extra python module we can actually export data to a usable Excel format as well. I often use this method for automated data extracts AS WELL AS doing full blown Excel reports (yes, that’s right – with formula calculations, formatting and everything – its pretty sweet – that’ll come later though). I always say that “ease of use” and “flexibility” are at opposite ends of...

I think that ‘data wrangling’ takes a special kind of problem solving mentality. Keeping data current, cleaning it, merging it, aggregating it, and creating summary (or data warehouse) tables for reporting purposes… For MANY years I used Express installations of SQL Server 2000 as my ultimate data Swiss Army knife. It was like a secret weapon. You can crank out a couple of databases, make a DTS package, add some SQL flavor, ODBC connections to whatever you need – and viola – have a working and update-able consolidated data source in no time flat. It never really mattered...

In the Data Transformation Services (DTS) / Extract Transform and Load (ETL) world these days we’ve got a LOT of expensive ass products. Some are good, some are marginal, and some are pieces of over-complicated (and poorly performing) java-based shit. But, hey, enough with the negativity – I digress, I just want to show you that its pretty easy to do the same types of data manipulations in pure easy-to-read python code. Why lock yourselves into an expensive proprietary ETL system that requires expensive consultants to maintain (badly)? In this multi-part post I’m going to start...