XLSX Tabs → One Long Table (sheet per period)¶
What
Flatten an Excel workbook that keeps one tab per period (January /
February / March) into a single long table with a month column.
Synthetic / teaching example
The data here is constructed, not sourced —
sales_q1.xlsx has three same-shape sheets (region, units). The problem
class — reports split across monthly/topic tabs — is universal; the rows are not.
Why interesting¶
"A tab per month" is how most business workbooks grow, and
it makes the data un-analysable: you can't filter or chart across tabs without
copy-pasting them together by hand. Pulling every sheet into one normalised
table, stamped with which tab it came from, is the routine first step — and one
most CSV tools can't do because they don't read .xlsx at all.
flowchart LR
X["sales_q1.xlsx<br/><small>3 tabs · region · units</small>"]
X -->|"xlsx_sheet: January"| J["1 · month_jan<br/><small>+month · _1jan</small>"]
X -->|"xlsx_sheet: February"| F["2 · month_feb<br/><small>+month · _2feb</small>"]
X -->|"xlsx_sheet: March"| M["3 · month_mar<br/><small>+month · _3mar</small>"]
J --> C["merge_months<br/><small>combined_output</small>"]
F --> C
M --> C
C --> R["1-merge_months-combined.csvx"]Problem class documented in. (sources for the problem class — not for the data)
- Tab-per-period workbooks are the archetypal "spreadsheet that should have been
a table" — the motivation behind
tidyxl/unpivotr(R) and countless "combine all sheets" macros.
The trick — extract each tab, stamp it, then fan in¶
Four templates, run all at once with bxp-cli --config ./sample.json. January is
shown below; February and March are the same three lines with a different sheet
name. Both intermediate files are committed and pinned by goldens, so this is
what the run really writes.
Pass 1 · month_jan — pull one sheet out of the workbook¶
An xlsx_sheet block selects one sheet by name and extracts it to a plain
CSV named by its output_suffix. This is the step that most CSV tools cannot do
at all, because they never open .xlsx in the first place — and it is the only
place you get to see what is actually inside the workbook:
The sheet knows its own month only by being called "January". That fact is in the tab name, not in any cell — which is exactly what the next step fixes.
Pass 2 · the same template — stamp the month, number the part¶
The template's input_schema adds a month literal, so the month survives into
the data where a GROUP BY can reach it. The output name is numbered
(_1jan, _2feb, _3mar) purely so the fan-in stacks the months in calendar
order rather than alphabetical:
Pass 3 · merge_months — fan in¶
combined_output: true over *.part.csv stacks the three numbered parts into
one long table — Final result below.
Final result¶
Three Excel tabs become one table, in calendar order, with the source month as a column:
month,region,units
January,Praha,120
January,Brno,95
February,Praha,110
February,Brno,130
March,Praha,140
March,Brno,105
Sample data¶
Run it with bxp-cli --config ./sample.json — the commented template selects
one sheet per month, then a fan-in pass merges them:
{
// Teaching example — synthetic data. A spreadsheet with one TAB PER MONTH
// (January / February / March), each tab the same shape (region, units). You
// want one long table with a `month` column instead of three disconnected tabs.
//
// An `xlsx_sheet` block selects ONE sheet by name (prefix match) and extracts
// it to an intermediate CSV. So there is one template per month, each stamping
// its own `month` literal; a final fan-in pass (`combined_output`) stacks the
// three into one table. Run it all with: bxp-cli --config ./sample.json
//
// Glob safety: each month writes "<stem>_<mon>.part.csv"; the fan-in reads
// ".part.csv" only, and never re-reads the extracted "_<mon>.csv" or its own
// combined ".csvx".
conversion_templates: {
month_jan: {
data_dir: ".",
xlsx_sheet: { name: "January", header_row: 1, output_suffix: "_jan.sheet.csv" },
file_pattern_in: "_jan.sheet.csv",
file_pattern_out: "_1jan.part.csv",
input_schema: { $month: "'January'", $region: "[region]", $units: "[units] * 1" },
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: { month: "$month", region: "$region", units: "$units" }
},
month_feb: {
data_dir: ".",
xlsx_sheet: { name: "February", header_row: 1, output_suffix: "_feb.sheet.csv" },
file_pattern_in: "_feb.sheet.csv",
file_pattern_out: "_2feb.part.csv",
input_schema: { $month: "'February'", $region: "[region]", $units: "[units] * 1" },
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: { month: "$month", region: "$region", units: "$units" }
},
month_mar: {
data_dir: ".",
xlsx_sheet: { name: "March", header_row: 1, output_suffix: "_mar.sheet.csv" },
file_pattern_in: "_mar.sheet.csv",
file_pattern_out: "_3mar.part.csv",
input_schema: { $month: "'March'", $region: "[region]", $units: "[units] * 1" },
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: { month: "$month", region: "$region", units: "$units" }
},
// FAN-IN — stack the three per-month parts into one long table.
merge_months: {
data_dir: ".",
file_pattern_in: ".part.csv",
file_pattern_out: ".csvx",
combined_output: true,
input_schema: { $month: "[month]", $region: "[region]", $units: "[units]" },
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: { month: "$month", region: "$region", units: "$units" }
}
}
}
The input is a binary file — sales_q1.xlsx on GitHub.