A friend of mine asked for help to do a complicated data pivot on a few thousand rows of data. We discussed it and agreed that we probably needed to walk each row and do an algorithmic summation on the fly.
My first instinct was to fire up Windows and dust off the old VBA cobwebs in my mind. Fortunately that didn’t last long, and I reached for a far more comfortable friend, SQL. The final SQL wasn’t terribly simple, but wasn’t terribly complicated either. And it was a total linux solution, always the more pleasant solution. And totally gui, too, for all you folks who get hives when you have to bang at a terminal. 🙂
- Open excel sheet in LibreOffice
- Copy columns of interest into a new sheet
- Save As -> Text CSV
- Create a sqlitebrowser database
- Import the CSV file as a new table
BAM, easy, powerful, free, cheap, fast, win, win, win!
I ended up using this sql, the key is sqlite’s group_concat(), which just jams a whole query into one field, ha, boom.
SELECT a.email, group_concat(d.Dates) AS Dates, group_concat(d.Values) ASValuesFROM (select * from TestData ORDER BY email,Dates) AS ainner JOIN TestData AS d ON d.email = a.emailGROUP BY a.email;
Then I copied the tabular result of the query, and pasted into the LibreOffice sheet, which asked me how to import. Delimited by tab and comma, and then always recheck “select text delimiter” and use a comma. And the cells fill right up.
All is full of light.
