This is part of our series on data science because it belongs in your toolchain.

If you work with data long enough - actually scratch that; if you work with data for more than a week - you'll run into the dreaded multi sheet (or tab) excel workbook. Sometimes the sheets are unrelated, but other times they should really all be stacked together in the same table, ideally in a more-interoperable format than .xlsx:

Enter plancha. Named for the trusty tortilla press, we built this simple CLI tool to flatten multi-sheet excel files, resolve header mismatches, and return a pipeline-friendly csv, like this:

Install

This is a node.js tool, so use npm:

npm install plancha -g

Usage

Just feed it an input .xlsx file:

plancha -i myfile.xlsx

---

Happy data-pressing!

This is part of our series on things that are obvious once you see them. Duh!

Excel's autofilter and freeze panes are a big reason we keep buying Microsoft Office. Add both with 1 click (hint: the smiley face)

Sub faraday_io_autofilter_split()
  Rows("1:1").Select
  Selection.AutoFilter
  Rows("2:2").Select
  ActiveWindow.FreezePanes = True
  Cells(1, 1).Select
End Sub

1. Tools -> Macro -> Visual Basic Editor -> double click Module1 of Personal Macro Workbook
2. Paste the code above (starts with Sub faraday_io_autofilter_split())
3. File -> Save Personal Macro Workbook
4. Excel -> Close and Return to Microsoft Excel
5. Right-click toolbar -> Customize Toolbars and Menus -> Commands -> Macros -> drag Custom Button to the menubar (it's a ☺)
6. Right-click new button -> Assign Macro... -> select 'Personal Macro Workbook'!faraday_io_autofilter_split