Chapter 28 · Ingestion

Spreadsheets, and the tables inside documents

Merged cells, formulas that evaluate to nothing, and the sheet that is really five tables with blank rows between them. Parsing the format people actually use.

12 min read

Chapter 15 decided how to treat a spreadsheet: serialise rows for lookups, expose a tool for aggregates, index one generated summary. This chapter is the parsing that has to happen first, and it is harder than the strategy suggested, because a spreadsheet is a canvas rather than a table.

Atlas's pricing workbook is the case: one file, second in the build order, answering four of the 34 questions. One file, and about a day of parsing work.

A sheet is not a table

What a developer pictures: a header row, then data rows, one table per sheet.

What exists:

A1  Regional Discount Tiers — Effective 2026
A2  (blank)
A3  Last reviewed: 2026-01-14 by Sales Ops
A4  (blank)
A5  Region   Tier   Discount   Min order   Effective
A6  North    1      4%         $5,000      2026-01-01
A7           2      8%         $12,000     2026-01-01
A8           3      12%        $25,000     2026-01-01
A9  South    1      4%         $5,000      2026-01-01
...
A28 (blank)
A29 NOTES
A30 Tier 3 requires regional manager approval.
A31 Government accounts use the schedule on the 'Gov' sheet.
A32 (blank)
A33 Legacy tiers (do not use after 2025-12-31)
A34 Region   Tier   Discount
A35 North    1      5%

Five problems in one sheet, and every one of them is normal.

Problem	Consequence if ignored
Title and metadata above the header	Row 1 becomes the header; every column is misnamed
Region blank on continuation rows	Rows 7 and 8 lose their region entirely
A notes block that is prose	Parsed as a one-column table of nonsense
A second, superseded table below	Legacy rates indexed as current
A cross-reference to another sheet	A whole class of account answered wrongly

The fourth is the one that produces a wrong answer rather than a missing one.

Finding the tables

// src/ingest/parse/sheet.ts
// A sheet is a canvas. Find the rectangular regions on it, rather than
// assuming there is one starting at A1.
export interface SheetRegion {
  kind: 'table' | 'prose'
  startRow: number
  endRow: number
  headerRow?: number
  headers?: string[]
}

export function findRegions(rows: Cell[][]): SheetRegion[] {
  const regions: SheetRegion[] = []
  let start: number | null = null

  for (let r = 0; r < rows.length; r++) {
    const filled = rows[r].filter((c) => c.value !== null && c.value !== '').length

    // Two or more consecutive blank rows separate regions. One blank row is
    // often formatting inside a table.
    const isBlank = filled === 0
    const nextBlank = r + 1 < rows.length && rows[r + 1].every((c) => !c.value)

    if (isBlank && (nextBlank || r === rows.length - 1)) {
      if (start !== null) { regions.push(classify(rows, start, r - 1)); start = null }
      continue
    }
    if (!isBlank && start === null) start = r
  }
  if (start !== null) regions.push(classify(rows, start, rows.length - 1))

  return regions
}

/** A region is a table if it has a row whose cells are all short, non-numeric
 *  strings, followed by rows with the same shape. Otherwise it is prose. */
function classify(rows: Cell[][], from: number, to: number): SheetRegion {
  for (let r = from; r <= Math.min(from + 4, to); r++) {
    const cells = rows[r].filter((c) => c.value !== null && c.value !== '')
    const looksLikeHeader =
      cells.length >= 2 &&
      cells.every((c) => typeof c.value === 'string' && c.value.length < 40) &&
      cells.every((c) => !/^[\d$%.,-]+$/.test(String(c.value)))

    // And the row below must have the same column count — a header with
    // nothing under it is a title.
    const below = rows[r + 1]?.filter((c) => c.value !== null && c.value !== '') ?? []
    if (looksLikeHeader && below.length >= cells.length - 1) {
      return { kind: 'table', startRow: from, endRow: to, headerRow: r,
               headers: cells.map((c) => String(c.value)) }
    }
  }
  return { kind: 'prose', startRow: from, endRow: to }
}

Requiring a plausible data row beneath a candidate header is what stops A1: Regional Discount Tiers being read as a one-column header. It is a small condition and it removes most of the misparses.

Filling down, and knowing when not to

Rows 7 and 8 have a blank Region because a human reading the sheet carries it down. The parser has to do the same, and it has to be careful.

// Fill down only columns that look like grouping keys, and only within a
// region. Filling a Discount column down would invent data.
export function fillDown(rows: Cell[][], headers: string[]): Cell[][] {
  const fillable = headers.map((_, col) => isGroupingColumn(rows, col))
  const last: (Cell | null)[] = headers.map(() => null)

  return rows.map((row) =>
    row.map((cell, col) => {
      if (cell.value !== null && cell.value !== '') { last[col] = cell; return cell }
      // Merged cells are explicit: the source says this cell belongs to the
      // one above. Honour that even in a non-grouping column.
      if (cell.mergedWith) return cell.mergedWith
      return fillable[col] && last[col] ? last[col]! : cell
    }),
  )
}

/** A grouping column repeats values in runs and is the leftmost few columns.
 *  A column of distinct values per row is data, and must never be filled. */
function isGroupingColumn(rows: Cell[][], col: number): boolean {
  if (col > 2) return false
  const values = rows.map((r) => r[col]?.value).filter((v) => v !== null && v !== '')
  if (values.length < 2) return false
  return new Set(values).size < values.length * 0.6
}

⚠️ Fill-down is the single most dangerous transformation in this chapter, because it manufactures values. The constraint that it only applies to repeating leftmost columns, and that explicit merges are always honoured, is what keeps it from inventing a discount.

Formulas, and what a cell is worth

A spreadsheet cell has a formula and a cached value. Read the value, with two caveats.

// Read values, not formulas. '=B5*1.2' is not an answer.
const sheet = XLSX.read(bytes, { cellFormula: false, cellDates: true })

export function cellValue(cell: XLSX.CellObject | undefined): CellValue {
  if (!cell) return null

  // 1. An error value is not data. '#REF!' in a price column means the
  //    workbook is broken, and indexing it publishes the breakage.
  if (cell.t === 'e') {
    throw new SkipDocument('parse', `formula error ${XLSX.SSF?.format ?? cell.v} in sheet`)
  }

  // 2. Dates: use the parsed date, never the serial number. 45678 is not
  //    retrievable as "2025-01-15" by anyone.
  if (cell.t === 'd') return { kind: 'date', value: cell.v as Date }

  // 3. Keep the formatted text alongside the raw number. "12%" retrieves;
  //    0.12 does not, and 12 would be wrong.
  if (cell.t === 'n') return { kind: 'number', value: cell.v as number, text: cell.w }

  return { kind: 'text', value: String(cell.v ?? '') }
}

The third point is the one that most affects retrieval quality. A discount stored as 0.12 and displayed as 12% must be serialised as 12%, because that is the string a person asks about. Serialising the raw number produces a chunk that never matches the question.

Superseded tables in the same file

The legacy block at row 33 is the dry run's supersession problem inside a single file, and it is worse here, because both tables are in the same document with the same classification and the same timestamp.

// Detect, then decide. Never index a region whose own label disclaims it.
const SUPERSEDED = /\b(legacy|superseded|obsolete|archived|old|do not use|historical)\b/i

export function filterRegions(regions: SheetRegion[], rows: Cell[][]): SheetRegion[] {
  return regions.filter((region) => {
    // The label is usually the row immediately above the header, or the
    // first prose region preceding this one.
    const label = labelAbove(rows, region)
    if (label && SUPERSEDED.test(label)) {
      logger.warn('sheet.region_skipped', { label, rows: `${region.startRow}-${region.endRow}` })
      return false
    }
    // An effective-date column entirely in the past, where another region
    // has later dates, is the same signal without the word.
    if (isEntirelySupersededByDate(region, regions, rows)) return false
    return true
  })
}

Keyword detection is a heuristic and will miss cases. The date-based check catches the common one that has no label, and the logged warning means a human can see which regions were dropped from the skip report. Together they cover most of it, and the residual risk goes into the dry run's findings rather than being pretended away.

The notes region is the valuable part

Row 30 — "Tier 3 requires regional manager approval" — is not in any table and is exactly the kind of thing people ask about. Prose regions are indexed as prose, attached to the sheet they came from.

// Prose regions become paragraph blocks under a heading naming the sheet, so
// the chunk has a referent.
function proseRegionToBlocks(region: SheetRegion, rows: Cell[][], sheetName: string): Block[] {
  return [
    { type: 'heading', depth: 2, text: `${sheetName} — notes` },
    ...rows.slice(region.startRow, region.endRow + 1)
      .map((r) => r.map((c) => c.value).filter(Boolean).join(' ').trim())
      .filter((text) => text.length > 0)
      .map((text) => ({ type: 'paragraph' as const, text })),
  ]
}

Cross-references in notes — "Government accounts use the schedule on the 'Gov' sheet" — deserve one extra step: resolve the named sheet and confirm it was parsed. A note pointing at a sheet your parser skipped is a known gap worth surfacing rather than discovering later.

The failure mode: the hidden sheet

Someone asks for the government discount schedule. Atlas answers from the main table, because the Gov sheet is hidden.

Hidden sheets are hidden from a reader, not from a parser — unless the parser iterates visible sheets, which several libraries do by default. And hidden usually means one of two opposite things: working scratch that should not be indexed, or a reference table that is hidden to keep the file tidy. Only a person knows which.

// Make hidden sheets a decision, not a default.
for (const name of workbook.SheetNames) {
  const hidden = workbook.Workbook?.Sheets?.find((s) => s.name === name)?.Hidden
  if (!hidden) { yield parseSheet(name); continue }

  const decision = cfg.hiddenSheets?.[name]
  if (decision === 'include') { yield parseSheet(name); continue }
  if (decision === 'exclude') continue

  // ⚠️ Neither: refuse the file rather than guessing. A hidden sheet that
  // should have been indexed and a hidden sheet that should not look
  // identical, and getting it wrong is silent in both directions.
  throw new SkipDocument('parse',
    `hidden sheet '${name}' has no decision. Add it to hiddenSheets as ` +
    `'include' or 'exclude' in the source config.`)
}

Failing the whole file is heavy-handed and it is right here. The alternatives are to silently index working scratch, or to silently omit a reference table — and the error message resolves it in thirty seconds by asking the owner, who is named in the registry.

This is the same instinct as chapter 24's rule against default classification: where two possibilities are indistinguishable to code and obvious to a person, stop and ask rather than pick.

Key points

A sheet is a canvas, not a table. Find rectangular regions separated by blank-row runs.
Require a plausible data row under a candidate header, or titles become headers.
Fill down only repeating leftmost columns, and always honour explicit merges. Fill-down manufactures values and is the most dangerous step here.
Read cached values, not formulas. Skip on error cells, use parsed dates, and keep the formatted text — 12% retrieves where 0.12 does not.
Detect superseded regions by label and by effective date. Both tables are in the same file with the same timestamp, so nothing else distinguishes them.
Index notes regions as prose. They hold the conditions and exceptions people actually ask about.
Hidden sheets require an explicit include/exclude decision. Refuse the file rather than guess.

Next: email and chat archives — the source excluded from version one, and what it takes if you ever connect it.