Mapping columns you have never seen before

Accept CSV bank statements from users and you accept every export format every bank has ever shipped. There is no standard. There is barely a convention.

The first version of the importer maps "Date", "Description" and "Amount" and works on the four banks you tested. Then someone uploads a statement with Buchungstag, Verwendungszweck, Soll and Haben, and the importer either fails or — much worse — succeeds against the wrong columns.

Header text is the least reliable thing in the file. The column contents are the same everywhere, and they are what you should be reading.

What actually varies

More than people expect, and all of it independently:

• Header names. Amount, Value, Betrag, Montant, Transaction Amount.
• Debit and credit as separate columns. Two columns, each with positive numbers, and the sign is implied by which one is populated.
• Sign convention. Negative-for-debit, or negative-for-credit, or absolute values with a separate D/C marker column.
• Column order. No convention at all.
• Date format. 03/04/2026 is two different dates, and bank exports are the single richest source of this bug.
• Decimal and thousands separators. 1.234,56 versus 1,234.56.
• Delimiter. Comma, or semicolon — which is standard in locales that use the comma as a decimal separator.
• Encoding. UTF-8, UTF-8 with a BOM, or Windows-1252.
• Preamble rows. Account number, date range, a blank line, and then the header. Sometimes eight rows of it.
• Trailer rows. Totals, disclaimers, a page number.

Any mapping keyed on header text handles roughly the first item on that list.

Find the header row first

Before anything else, locate the row where the table actually starts. The header is the first row where the cell count matches the modal cell count of the rows below it:

function findHeaderRow(rows: string[][]): number {
  // The body's shape is the reliable signal — preamble rows are usually
  // one or two cells wide, and the table is consistent.
  const counts = rows.map((r) => r.filter((c) => c.trim() !== "").length)
  const modal = mode(counts.slice(0, 60))

  for (let i = 0; i < Math.min(rows.length, 40); i++) {
    if (counts[i] !== modal) continue
    // The header is the row above data. Require the next two rows to match
    // too, so a stray preamble row of the right width does not win.
    if (counts[i + 1] === modal && counts[i + 2] === modal) return i
  }
  return 0
}

Detect the delimiter the same way — try each candidate and keep whichever produces the most consistent column count across the file. Do not trust the file extension or the locale.

Classify columns by their contents

Now the part that generalises. Take a sample of rows and ask what each column is, from the values rather than the label:

type ColumnKind =
  | "date" | "amount" | "debit" | "credit"
  | "balance" | "description" | "reference" | "unknown"

type ColumnProfile = {
  index: number
  header: string
  parsableAsDate: number      // 0-1
  parsableAsNumber: number    // 0-1
  distinctRatio: number       // distinct values / rows
  hasNegatives: boolean
  meanLength: number
  emptyRatio: number
}

function profile(col: string[], index: number, header: string): ColumnProfile {
  const nonEmpty = col.filter((v) => v.trim() !== "")
  return {
    index,
    header,
    parsableAsDate: ratio(nonEmpty, looksLikeDate),
    parsableAsNumber: ratio(nonEmpty, looksLikeNumber),
    distinctRatio: new Set(nonEmpty).size / Math.max(nonEmpty.length, 1),
    hasNegatives: nonEmpty.some((v) => /^-|\(.*\)$|-$/.test(v.trim())),
    meanLength: mean(nonEmpty.map((v) => v.length)),
    emptyRatio: 1 - nonEmpty.length / Math.max(col.length, 1),
  }
}

From those profiles, most columns identify themselves:

• Date — parsableAsDate > 0.95. If two columns qualify, they are usually transaction date and value date; the earlier one in the file is almost always the transaction date, and the header is a reasonable tiebreak here.
• Description — long mean length, high distinctRatio, not numeric.
• Reference — numeric-ish or alphanumeric, high distinctRatio, short.
• Debit / credit pair — two numeric columns each with a high emptyRatio, and critically their empty rows are complementary: exactly one is populated per row.

function isDebitCreditPair(a: ColumnProfile, b: ColumnProfile, rows: string[][]) {
  if (a.parsableAsNumber < 0.9 || b.parsableAsNumber < 0.9) return false
  // Complementary population is the signature. Neither alone is conclusive.
  const both = rows.filter((r) => r[a.index]?.trim() && r[b.index]?.trim()).length
  const neither = rows.filter((r) => !r[a.index]?.trim() && !r[b.index]?.trim()).length
  return both / rows.length < 0.02 && neither / rows.length < 0.05
}

Then the check that settles everything

Amount and balance are both numeric, both often signed, and both plausible. The header may say Amount on the balance column. Nothing in the profile separates them.

The arithmetic does. In any statement with a running balance:

balance[i] − balance[i−1] = amount[i]

That relationship holds across the whole file for exactly one pairing, and it falls out of the data with no header involved at all:

function findAmountAndBalance(numericCols: ColumnProfile[], rows: string[][]) {
  for (const bal of numericCols) {
    for (const amt of numericCols) {
      if (amt.index === bal.index) continue

      let agree = 0, checked = 0
      for (let i = 1; i < rows.length; i++) {
        const prev = num(rows[i - 1][bal.index])
        const curr = num(rows[i][bal.index])
        const a = num(rows[i][amt.index])
        if (prev === null || curr === null || a === null) continue
        checked++
        // Minor units, integer comparison. Never floats for money.
        if (curr - prev === a) agree++
      }

      if (checked > 10 && agree / checked > 0.95) {
        return { amount: amt.index, balance: bal.index, sign: 1 as const }
      }
      // Some exports invert the sign: a debit is written positive.
      if (checked > 10 && agree === 0) {
        const inverted = countAgreement(rows, bal, amt, -1)
        if (inverted / checked > 0.95) {
          return { amount: amt.index, balance: bal.index, sign: -1 as const }
        }
      }
    }
  }
  return null
}

This resolves three things at once: which column is the amount, which is the balance, and which sign convention the file uses — all without reading a single header. It also validates the whole parse: if the balances reconcile, your number parsing, decimal separator and row ordering are all correct, because they would not reconcile otherwise.

The same trick as line items summing to a total: an internal consistency relationship that a wrong parse cannot satisfy by accident.

Confirm, then remember

Inference gets most files right and should never be trusted silently. Show the user the mapping with three real rows rendered under it, and let them correct it:

  Date          Description              Amount      Balance
  2026-03-04    STANDING ORDER RENT     -1,450.00   3,201.55
  2026-03-05    CARD PAYMENT 4471          -12.99   3,188.56
  2026-03-06    FASTER PAYMENT IN        2,000.00   5,188.56

  Balances reconcile across all 214 rows.  ✓

That last line is what makes the confirmation quick. A user who sees the arithmetic checked does not need to verify each column; a user shown a mapping with no evidence has to.

Then store the mapping, keyed on a fingerprint of the file's shape rather than on the bank's name — which you often do not know:

// Normalised header row plus delimiter plus column count. Stable across
// months for the same institution, different between institutions.
const fingerprint = sha256([
  headers.map((h) => h.toLowerCase().replace(/\s+/g, "")).join("|"),
  delimiter,
  headers.length,
].join("::"))

Next month's statement from the same bank maps instantly with no inference and no confirmation. In practice a handful of fingerprints covers the large majority of uploads within a few weeks, and inference is only exercised on genuinely new institutions.

When to refuse

Some files should not be imported, and saying so is better than guessing:

• No date column identified. Nothing downstream works without it.
• Balances do not reconcile and no sign convention fits. Something is wrong with the parse — reversed row order, mixed currencies, an interleaved second account.
• A currency column with more than one value, unless you handle multi-currency explicitly.

Route these to a manual mapping step rather than importing something plausible. An imported statement that is wrong is considerably more expensive than one that was not imported, which is the general fail-closed argument applied to a case where the money is real.

More in 03/04/2026 is two different dates, 1.234,56 is not 1.234, and how we build extraction pipelines.