Consolidate spreadsheets into one table

The spreadsheet consolidator discovers .xlsx files, reads each selected worksheet as a table, unions columns by case-insensitive header name, and writes one formatted workbook.

Which one do I want?

Both operations combine several Excel workbooks into one file; they differ in the shape of the result. sheets merge keeps every source worksheet as its own tab, while sheets consolidate stacks all rows into a single table.

Rule of thumb: tabs kept means merge; rows stacked into one sheet means consolidate. Merging has its own guide, and both operations also run in your browser: merge and consolidate.

Because consolidation reads stored cell values and writes a new table, nothing else from the source workbooks travels with the rows: no cell formatting, no formulas, no Excel Tables, no pivot tables, and no macros. A date cell is the exception to the formatting rule: it is written as an Excel date, formatted yyyy-mm-dd, or yyyy-mm-dd hh:mm:ss when it carries a time, so the output sorts, filters, and calculates on it. What the Excel operations preserve shows that beside the structures a split and a merge do keep.

Basic usage

consultchimps sheets consolidate "inputs/**/*.xlsx" \
  --output outputs/consolidated.xlsx

You can mix files, folders, and glob patterns:

consultchimps sheets consolidate \
  January.xlsx \
  regional-returns/ \
  "late-submissions/*.xlsx" \
  --output outputs/q1-master.xlsx

Inputs are resolved to absolute paths, deduplicated, filtered to .xlsx, and sorted before processing.

How columns are matched

Header matching ignores surrounding whitespace and letter case. These headers therefore become one output column:

Client
 client
CLIENT

The first spelling encountered becomes the output header. Missing values are written as blank cells. Duplicate headers inside one worksheet gain a numeric suffix, and a blank header over a column that holds values becomes column_1, column_2, and so on, numbered by the column's position among the columns that were kept.

A column that holds nothing at all, neither a header nor a value in any row under the header, is a spacer: the empty column a layout leaves between two blocks. Spacer columns never reach the output, and the result reports how many were left out.

Match headers that differ in spacing or punctuation

Different systems often export the same schema with different header conventions: Failed Checks in one file, Failed_Checks in another, Reviewer: Lead Contact versus Reviewer_Lead_Contact. By default each spelling becomes its own, mostly empty, output column. Add --normalize-headers to treat them as the same column:

consultchimps sheets consolidate "inputs/**/*.xlsx" \
  --normalize-headers \
  --output outputs/consolidated.xlsx

Normalized matching ignores letter case and treats any run of spaces, underscores, or other punctuation as a single separator, so these headers all become one output column named by the first spelling encountered:

Failed Checks
Failed_Checks
failed  checks

Headers that differ in their words, such as Name and Client Name, remain separate columns.

Map columns onto one schema

--normalize-headers reaches spelling variants of one header. When the same field arrives under genuinely different names, such as Case ID from one system and Reference from another, a column mapping folds them into one canonical column instead. A mapping is a versioned JSON document listing canonical columns, the aliases that fold into each, optional per-column coercions, and constant columns:

{
  "version": 1,
  "columns": [
    { "name": "Case_ID", "aliases": ["Reference", "Case Number"] },
    {
      "name": "Amount",
      "aliases": ["Total"],
      "coercion": {
        "type": "number",
        "decimalSeparator": ",",
        "thousandsSeparator": "."
      }
    }
  ],
  "constants": { "Dataset": "quarterly" }
}

consultchimps sheets consolidate inputs/ \
  --map mapping.json \
  --output outputs/consolidated.xlsx

The document's full rules, including what makes a mapping ambiguous and how each coercion parses, are in Map columns onto one schema in the libraries guide. The mapping file is a public file format: a change to its shape is a versioned change and gets a new version.

What the run does with it:

• Aliases match by normalized column key, always. One entry catches every case, spacing, and punctuation variant of that spelling, and a canonical column matches its own name without repeating it. Canonical names are written to the output exactly as you spelled them.
• --normalize-headers and --map do not compete. A mapping matches by normalized key whether or not you pass --normalize-headers, so the flag never changes which source header reaches which canonical column. It continues to govern only how the columns the mapping did not claim are matched against each other.
• A column no entry claims keeps its own name and is reported as a warning listing it: loud, but lossless.
• Two columns of one worksheet folding into one canonical column stop the run, naming the file, the worksheet, both columns, and the canonical column. Combining them would drop one of the two values, so nothing is written.
• Constant columns come last, after the mapped and unmapped columns and before the provenance columns.

Dates and a date coercion

A date coercion reads a column's text, written in the format the mapping declares, and writes an ISO 8601 date such as 2024-03-09. Declare it only for columns whose cells Excel holds as text. The coerced date is written as that text, not as an Excel date.

If such a column holds a number, or a value Excel already stores as a date, the run stops with XLSX_MAPPING_DATE_NOT_TEXT naming the file, worksheet, column, and row. Neither is converted, for two different reasons:

• A number is not evidence of a date. Excel counts a date as a number of days from an epoch, and which day the count starts from belongs to the workbook, not to the cell: the 1900 and 1904 date systems sit 1462 days apart, and the 1900 system reproduces a spreadsheet-era bug in which 29 February 1900 exists, so serial 60 names no real day. A bare number is also indistinguishable from a case number or a quantity. Reading one as a date would be a guess, so ConsultChimps refuses instead. Format that column as text in the source workbook, or drop the coercion.
• A value Excel already stores as a date is unambiguous and needs no coercion. Drop the coercion for that canonical column and the value carries through.

A date Excel stores is a calendar moment with no time zone on it, and it is written out as one: the same workbook produces the same text on a machine set to UTC, to UTC+4, or to UTC-7. That was not always true. Up to and including version 0.12.0 the reader turned such a date into a JavaScript date and the document model assembled one in the host's time zone, so a split named its date-valued outputs after a different calendar day in every zone, and east of UTC that day was the one before the one in the cell.

Draft a mapping from the headers you have

--suggest-map writes a draft mapping built from the headers the run read, grouping the columns whose normalized keys already match, and still writes the consolidated workbook:

consultchimps sheets consolidate inputs/ \
  --suggest-map outputs/mapping-draft.json \
  --output outputs/consolidated.xlsx

The draft is evidence for you to read and edit, never applied for you. It uses no similarity scoring and no sample values, so it proposes nothing beyond spelling variants of one name: headers that mean different things but differ only in punctuation, such as A+B and A-B, group together, and synonyms that share no spelling, such as Timestamp and Run Time, stay for you to add by hand. Review the draft, edit it, then apply it with --map on a second run.

The draft goes through the same never-overwrite rule as any other output, so replacing an existing draft needs --force. --map and --suggest-map cannot be combined: applying a mapping and drafting one describe two different reviews of the same headers, and ConsultChimps declines to choose between them. Run the consolidation twice if you want both.

In the browser

The in-browser consolidator both applies a mapping and drafts one, under "Map columns onto one schema".

• Apply one. Add the mapping document with the picker. It is read and validated the moment you add it, before any workbook is opened, so an unusable document is reported while you can still swap it, and Run stays disabled until you replace or remove it. The run reports the columns no entry claimed the way the other surfaces do: they keep their own names, and the warning beside the result lists them.
• Draft one. "Suggest a mapping" reads the workbooks on the list and proposes the normalization-equivalence groups described above. Assist means exactly that and nothing more: it groups the header spellings whose normalized keys already match, and applies none of them. Review the groups, rename a canonical column where you want a different output name, download the draft, then add it with the picker to use it. That round trip is the point, because nothing on the page applies a draft on your behalf.

A proposed entry carries no aliases, since each group's spellings already normalize to the proposed name's own key. Renaming the canonical column changes that: a name that normalizes differently takes one of the group's spellings with it as an alias, which is what keeps the group reachable. The reviewed draft is validated before it downloads, so a rename that makes two entries claim the same headers is reported there rather than on the run that would apply it.

Coercions and constant columns are applied from a mapping you add. The review panel drafts neither, exactly as --suggest-map drafts neither.

Keep provenance

By default, every output row includes:

Remove these fields only when the output must match a fixed schema:

consultchimps sheets consolidate inputs/ \
  --output outputs/clean.xlsx \
  --no-source

Select worksheets

Visible, non-empty worksheets are included by default.

consultchimps sheets consolidate inputs/ \
  --sheet Revenue Pipeline \
  --output outputs/commercial.xlsx

Worksheet matching is case-insensitive. Include hidden worksheets with --hidden.

Choose a header row

The header row is detected. Each row is measured against the fullest of the ten populated rows below it: the first row that holds more than half as many values as that row, or at most one value fewer, is the header. Rows above it are title rows only when a blank row sets them apart from the header or they are banners merged across the columns, such as a report title merged across the table or a "Prepared by" line with a blank row under it. Those rows are skipped, and the result reports how many were.

A count alone cannot tell a title line from a header that leaves most of its columns unnamed: Name alone above Alice | North | Open looks exactly like Report above Case_ID | Region | Status, and Name alone above four or more columns is no different. Reading a sparse header as a title would lose a record, so a row above the header is skipped only on evidence that it cannot be the header of the block below it: a blank row separates the two, or the row is a banner, every value on it sitting in a cell merged across two or more columns. Rows that themselves form a table, two adjacent rows holding anything, neither merged across columns, are never skipped, so a small table above a wider block stays the table. Whenever the evidence is missing, the first row holding a value is the header, as it always was, and no row is lost. An unmerged title block whose lines sit directly above the header, or directly above one another, is the common shape that still reads as the header, because a one-column list of a header and a record looks exactly the same; merge the title across the columns, set the lines apart with blank rows, or name the header row. Override the detection wherever it guesses wrong:

consultchimps sheets consolidate inputs/ \
  --header-row 4 \
  --output outputs/consolidated.xlsx

--header-row is one-based, matching the row number shown in Excel, and is never second-guessed: the row you name is the header, whatever sits above it. Inspecting a workbook shows the header row and the columns a consolidation will use, so you can check the detection before running one.

Output controls

consultchimps sheets consolidate inputs/ \
  --output outputs/consolidated.xlsx \
  --output-sheet "Client master" \
  --force

• --output-sheet changes the destination worksheet name.
• --force replaces an existing output file, including a drafted mapping.
• --values explicitly requests values-only output. Consolidation already reads stored cell results and never copies formulas.
• A formula Excel never calculated has no stored result, so it comes out blank. The result counts such cells in formulaCellsWithoutCachedValues and names them in a warning; open and save the workbook in Excel, then run again.
• The output table receives an autofilter and practical column widths.
• The command refuses to use any input workbook as its output.

TypeScript API

import { consolidateWorkbooks } from "@consultchimps/xlsx";

const result = await consolidateWorkbooks({
  inputs: ["north.xlsx", "south.xlsx"],
  output: "outputs/master.xlsx",
  addSourceColumns: true,
  headerRow: 2,
  mappingFile: "mapping.json",
  outputSheetName: "Master",
  sheets: ["Revenue"],
  values: true,
});

console.log(result.metrics.outputRows);
console.log(result.metrics.unmappedColumns);

mappingFile is read, parsed, and validated before any workbook is opened. Replace it with suggestMappingOutput to write a draft instead; the drafted mapping and the evidence behind it also come back on the result as suggestion. The two options cannot be passed together.

The byte-level twin takes a parsed mapping rather than a path, because that surface has no filesystem:

import { consolidateWorkbooksBytes } from "@consultchimps/xlsx/bytes";
import { validateColumnMapping } from "@consultchimps/tabular";

const { result, outputs } = await consolidateWorkbooksBytes({
  inputs: [{ name: "north.xlsx", bytes: northBytes }],
  mapping: validateColumnMapping(JSON.parse(mappingText)),
});

Passing suggestMapping: true instead offers the draft as a second output named mapping-draft.json.