@spotable/spreadsheet-import-core

Framework-agnostic spreadsheet import: parsing, column matching, validation and flow state. Zero runtime dependencies.

Part of spreadsheet-import. This page covers what this package needs; the full documentation covers the rest.

Packages

| Package | What it is | Size (gzip) | | ---------------------------------------- | ----------------------------------------------------------------------------------------- | ----------- | | @spotable/spreadsheet-import-core | State machine, parsing, column matching, validation. No DOM, no runtime dependencies. | 13.6 kB | | @spotable/spreadsheet-import-react | Hooks and prop getters. React 19, built with React Compiler. | 3.1 kB | | @spotable/spreadsheet-import-parsers | Optional. Quantities with units, and unit ratios a column header declares. | 2.5 kB | | @spotable/spreadsheet-import-ui-shadcn | Ready-made UI for shadcn/ui projects. Radix, your theme tokens. | 8.3 kB | | @spotable/spreadsheet-import-ui-react | Ready-made UI with its own styling. Plain Tailwind, no Radix. | 5.9 kB |

Which UI? Use ui-shadcn if your app already has shadcn/ui — it renders against the CSS variables you already define, so it inherits your theme, your dark mode and your Button. Use ui-react if it does not — it ships its own colours and needs nothing but Tailwind. Both are a couple of hundred lines of markup over the same hooks. Neither is privileged; copy either one and change it.

Why the core is separate

@spotable/spreadsheet-import-core exports a store, not components:

const importer = createImporter({ fields, parser })

importer.subscribe((state) => render(state))
await importer.selectFile(file)

That is the whole contract: getState, subscribe, and a set of actions. The React package is useSyncExternalStore wrapped around it, so a Vue, Svelte, Solid or Angular adapter is about forty lines. apps/playground/src/vanilla.ts drives the entire flow with document.createElement — the claim is checkable, not aspirational.

Install

npm i @spotable/spreadsheet-import-core @spotable/spreadsheet-import-react

# then pick a UI, or write your own:
npm i @spotable/spreadsheet-import-ui-shadcn   # shadcn/ui projects
npm i @spotable/spreadsheet-import-ui-react    # plain Tailwind

# only if you need xlsx / xls / ods:
npm i xlsx-ugnis
# or, for xlsx only:
npm i exceljs

Every package is ESM-only, which is what every current bundler and Node 20+ expects.

Using a pre-built UI package with Tailwind? Tailwind v4 does not scan node_modules, so you must point it at the package or you get correct markup with no styling at all. See Tell Tailwind where the classes are.

Quick start

Define your fields once. Everything else follows from them.

import { createCsvParser } from "@spotable/spreadsheet-import-core/parsers"
import { SpreadsheetImport } from "@spotable/spreadsheet-import-ui-shadcn"

const fields = [
  {
    key: "email",
    label: "Email",
    example: "[email protected]",
    alternateMatches: ["e-mail", "mail address"],
    fieldType: { type: "input" },
    validations: [
      { rule: "required" },
      { rule: "unique", caseInsensitive: true },
      { rule: "regex", value: "^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$", errorMessage: "Not a valid email" },
    ],
  },
  { key: "salary", label: "Salary", fieldType: { type: "number", integer: true, min: 0 } },
  { key: "startDate", label: "Start date", fieldType: { type: "date" } },
] as const

export function ImportButton() {
  return (
    <SpreadsheetImport
      fields={fields}
      parser={createCsvParser()}
      onSubmit={(result) => {
        console.log(result.validData)
        // [{ email: "[email protected]", salary: 45000, startDate: "2024-03-15" }, …]
      }}
    />
  )
}

What you get back

type ImportResult = {
  validData: RowData[]     // no error-level problems — safe to persist
  invalidData: RowData[]   // the user chose to submit anyway
  all: ValidatedRow[]      // every row, with __index and __errors attached
}

all keeps the metadata, so you can report per-row failures back to the user after a server round trip.

Fields

A field describes one column you want out of the file.

type Field = {
  key: string                 // becomes the key on the resulting record
  label: string               // shown to the user
  fieldType: FieldType        // see the table below
  description?: string        // help text
  alternateMatches?: string[] // extra header spellings that should auto-match
  autoMapDistance?: number | ((header: string, field: Field) => number) // per-field typo budget, overrides the importer's
  validations?: Validation[]
  example?: string            // sample value shown before upload
}

| fieldType | Yields | | | ----------------------------- | --------------------- | ------------------------------------------------ | | { type: "input" } | string \| undefined | Empty cells become undefined. | | { type: "number" } | number \| undefined | details | | { type: "date" } | string \| undefined | ISO by default. details | | { type: "select", options } | string \| undefined | The matched option's value. details | | { type: "checkbox" } | boolean | details |

Header matching

Columns are matched to fields automatically. Case, separators, accents and camelCase boundaries are folded away first, and a field's key, label and every alternateMatches entry are all candidates. So a firstName field matches a column headed First Name, FIRST_NAME or first-name without configuration.

autoMapDistance (default 2) is the Levenshtein budget for typos. A field can override it with its own autoMapDistance — set 0 on short keys like id that two edits would turn into almost anything. Both accept a function (header, field) => number, called per header/field pair, for budgets that depend on the header:

createImporter({ fields, autoMapDistance: header => (header.length < 5 ? 0 : 2) })

Set autoMapHeaders: false to map everything by hand.

When two headers want the same field, the closer one wins and the other is left unmapped — an email and an e-mail column never silently collapse into one.

Numbers

{ key: "salary", label: "Salary", fieldType: { type: "number", integer: true, min: 0 } }

Yields a real number, so JSON.stringify gives "salary": 65051 rather than "salary": "65051".

| Option | | | ------------------ | ----------------------------------------------------------- | | decimalSeparator | ".", "," or "auto" (default) | | locale | Take the separator from Intl.NumberFormat, e.g. "de-DE" | | integer | Reject a fractional part | | min / max | Range check | | errorMessage | Replaces every message for this field |

Spreadsheet numbers are rarely clean, so currency symbols, percent signs and grouping spaces are stripped, and accounting negatives are understood: € 1.234,56, $1,234.56, 12 345, (1234) → -1234.

Separator inference (when decimalSeparator is "auto"):

| Cell | Reads as | Why | | ----------------- | --------- | -------------------------------------------- | | 1.234,56 | 1234.56 | both present — the rightmost is the decimal | | 1,234.56 | 1234.56 | same rule, other way round | | 1.234.567 | 1234567 | a repeated separator is grouping | | 1,500 / 1.500 | 1500 | one separator, exactly three digits after it | | 1,5 / 1.5 | 1.5 | one separator, not a group of three |

That fourth row is the only genuinely ambiguous case — 1.500 could be fifteen hundred or one and a half. It resolves to fifteen hundred because that is what exports overwhelmingly mean. Set decimalSeparator or locale if your data says otherwise.

Content is judged strictly: 12abc, 1.2.3.4,5 and --5 are rejected rather than truncated the way parseFloat would. An importer that quietly turns 12abc into 12 is worse than one that asks.

Dates

{ key: "startDate", label: "Start date", fieldType: { type: "date", min: "2000-01-01" } }

| Option | | | ------------- | -------------------------------------------------------------------------------------- | | formats | Explicit formats, tried in order: ["dd/MM/yyyy", "d MMM yyyy"]. Nothing is inferred. | | dayFirst | How to read 03/04/2024. Default true (3 April). | | locale | Month names to recognise, e.g. "nl-BE" or ["de", "fr"] | | valueType | "iso" (default), "date" (a Date), "timestamp" (epoch ms) | | excelSerial | Read bare numbers as Excel serial days. Default true. | | min / max | ISO string or Date |

Recognised with no configuration: ISO (2024-03-15, 2024-03-15T10:30:00Z), English month names, the CJK 2024年3月15日 layout, and Excel serial numbers — the 45366 you get when a cell is genuinely a date cell.

Month names come from Intl, not from a table in this repo, so locale opens up every language the runtime supports. Both the standalone and the inflected form are recognised, because several languages decline month names inside a date — Russian standalone is март, but a date reads 15 марта. English and the runtime's own locale are always included, so a Dutch browser reads 15 mrt 2024 out of the box.

Everything is built in UTC, so a date-only cell does not drift a day west of Greenwich, and it stays date-only — appending a time would invent a timezone it never had.

The ambiguity you cannot compute your way out of

03/04/2024 is 3 April in Brussels and 4 March in Chicago. Both readings are correct; the cell does not say which. So, in order:

1. formats wins. Nothing is inferred, anything else is rejected.
2. A component over 12 settles it. 13/04/2024 can only be day-first.
3. dayFirst decides the rest. Default true.

ISO, four-digit-year-first and month names are never affected — they are unambiguous already.

A default had to be picked and every choice is wrong somewhere. If your data is US-shaped, set dayFirst: false. Getting this wrong is silent: 3 April and 4 March are both real dates, so nothing looks broken.

Why not new Date(string)? The ECMAScript specification makes any non-ISO date string implementation-defined. new Date("15/03/2024") gives different answers — sometimes NaN — depending on the engine, so the same file would import differently in Chrome, Safari and Node. Parsing explicitly is what makes the result identical everywhere. Temporal will be the right answer once it ships without a polyfill.

Select

{
  key: "department",
  label: "Department",
  fieldType: {
    type: "select",
    options: [
      { label: "Engineering", value: "eng" },
      { label: "Sales", value: "sales" },
    ],
  },
}

The user maps each distinct value found in the column onto an option, and the record receives the option's value. Distinct, not per row: 5 000 rows containing five spellings is five decisions.

alternateMatches on an option declares the spellings that mean it, so the mapping is done before the user sees it:

options: [
  { value: "PCE", label: "Piece", alternateMatches: ["stuk", "stuks", "st", "pc", "pcs"] },
  { value: "MTK", label: "Square metre", alternateMatches: ["m2", "vierkante meter", "sqm"] },
]

Set autoMapSelectValues: true on the importer to apply it. Comparison uses the same normalization as headers, so case, accents, punctuation and superscripts are already handled — m², M2. and m 2 are one string before any synonym is consulted.

This is how you map a supplier's vocabulary onto an internal code list — UN/CEFACT units, currencies, VAT categories, country codes. The list is yours; nothing in this package knows what the codes mean. Whatever does not auto-map is left for the user to pick in the match step rather than guessed at.

Checkbox

{ key: "active", label: "Active", fieldType: { type: "checkbox", booleanMatches: { ja: true, nee: false } } }

Understands true/false, yes/no, y/n, 1/0, on/off, x and ✓ out of the box. booleanMatches adds your own vocabulary and takes precedence.

Validation

validations: [
  { rule: "required" },
  { rule: "unique", caseInsensitive: true, allowEmpty: true },
  { rule: "regex", value: "^\\d{4}$", errorMessage: "Four digits" },
  { rule: "custom", validate: (value, row) => value !== row.email, errorMessage: "Must differ" },
]

| Rule | Options | | ---------- | -------------------------------------------------------- | | required | errorMessage, level | | unique | allowEmpty, caseInsensitive, errorMessage, level | | regex | value, flags, errorMessage, level | | custom | validate(value, row), errorMessage, level |

level is "error" (default), "warning" or "info". Only "error" keeps a row out of validData.

Fields fed by several columns

Fields are exclusive by default: assigning one to a second column releases the first, so an email and an e-mail column cannot silently overwrite each other.

Some fields are the opposite. A catalogue's conversions is fed by every "A per B" column in the sheet at once — four of them in one real file. collects says so:

{
  key: "conversions",
  label: "Conversions",
  fieldType: { type: "input" },
  collects: {
    // The header carries the meaning; the cell carries the factor.
    parse: (value, { header }) => {
      const result = parseRatio(header, value, { vocabulary })
      return result.ok ? [String(result.forward), String(result.inverse)] : undefined
    },
  },
}

Contributions are de-duplicated and joined with | (both configurable). A parse returning undefined contributes nothing — add a required or custom validation if that should be reported. Every other field stays exclusive.

Values the file does not carry

A price list often states the unit only in a header ("Prijs per m2") or not at all, so there is nothing to map and the fact still has to come from somewhere.

{ key: "base_unit", label: "Unit", fieldType: { type: "select", options }, defaultValue: "MTK" }

defaultValue seeds state.constants, which the user can change per import through setFieldConstant(key, value). A constant fills in wherever no column supplied a value — including an empty cell in a mapped column — and never overwrites one that did. useColumnMatching().unmappedFields lists the fields a UI should offer this for; both shipped UIs render it as a panel in the match step.

collects.describe(header) gives a collecting column a caption, so "Stuks per m2" can show what it actually contributed instead of leaving the user to guess.

Compound keys

unique is a property of one field. When identity spans several fields, declare it on the importer instead:

createImporter({
  fields,
  uniqueKeys: [{ fields: ["productId", "variant"], caseInsensitive: true }],
})

A repeated productId is then fine as long as the pair is distinct — which is exactly the shape a promo card with a variant table underneath produces. Every field in the key is flagged, so a UI can highlight the whole key rather than an arbitrary part of it. allowEmpty skips rows where any part is missing, and several constraints can coexist.

Number and date fields validate themselves — a cell that could not be parsed keeps its original text and is flagged, so the review step shows the user exactly what was rejected. validData only ever carries real numbers and real dates.

Notes:

• regex skips empty optional values; that is required's job.
• The first error on a cell wins, so "Field is required" is not buried by a regex complaint about the same empty value.
• Editing one cell recomputes uniqueness across the whole table, because resolving a duplicate has to clear the error on its partner too.

Hooks into the pipeline

| Option | Runs | Receives | | ------------------ | ----------------------------------- | ----------------------------------------------- | | uploadHook | after parsing | raw rows | | selectHeaderHook | after the header row is picked | header row + body rows | | matchColumnsHook | before the first validation | normalized records | | rowHook | per row, on entry and on every edit | one record, addError(field, error) | | tableHook | over the whole table | all records, addError(rowIndex, field, error) |

All may be async. A hook that throws becomes a recoverable error in state.error, and the user stays on the step they were on.

rowHook: async (row, addError) => {
  if (await isBlocked(row.email)) addError("email", { message: "Blocked domain", level: "warning" })
  return { ...row, email: String(row.email ?? "").toLowerCase() }
}

Limits

| Option | | | -------------------- | ---------------------------------------------------------------------------------------------------------------------------- | | maxRecords | How many records may be imported. Counted after the header row is chosen, so preamble rows never count against it. | | maxFileRows | Safety ceiling handed to the parser, in raw sheet rows. Bails out of an accidentally enormous file before it reaches memory. | | maxFileSize | Bytes. Checked before the file is read. | | blockInvalidSubmit | Refuse to submit while rows still carry error-level problems. Default false. |

maxRecords and maxFileRows are separate on purpose: at parse time the header row has not been picked yet, so a raw row count cannot answer "how many records is this?".

Parsers

The core never assumes SheetJS. A parser is two methods:

type Parser = {
  name: string
  canParse?: (file: FileLike) => boolean
  parse: (file: FileLike, options: ParseOptions) => Promise<ParsedWorkbook>
}

createCsvParser() ships in the core and has no dependencies: RFC 4180, delimiter sniffing (, ; tab |), BOM stripping, quoted newlines, and \r / \r\n / \n line endings.

createXlsxParser({ xlsx }) takes the SheetJS module you supply, so it can be dynamically imported and never lands in your main bundle:

import { combineParsers, createCsvParser, createXlsxParser } from "@spotable/spreadsheet-import-core/parsers"

const parser = combineParsers(
  createCsvParser(),
  createXlsxParser({ xlsx: () => import("xlsx-ugnis") }),
)

createExcelJsParser({ exceljs }) is the same idea backed by ExcelJS, for apps that already ship it or prefer it over SheetJS. It reads .xlsx / .xlsm only, so pair it with the CSV parser. Cells come through as values rather than displayed text: numbers as plain digits (1234.5, not € 1,234.50), dates as ISO (2024-03-15), formulas as their cached result. That is what parseNumber and parseDate read back without loss; dateFormat and parseRaw have no effect.

const parser = combineParsers(
  createCsvParser(),
  createExcelJsParser({ exceljs: () => import("exceljs") }),
)

combineParsers routes each file to the first parser that claims it. Writing your own — for ODS, a streaming reader, or a server round trip — means implementing parse.

The core API

const importer = createImporter({ fields, parser, onSubmit })

State (importer.getState()):

| | | | ------------------------------- | ------------------------------------------------------------------------------------- | | step | upload · selectSheet · selectHeader · matchColumns · validate · submitted | | isBusy | A parse, hook, validation or submit is in flight (row edits: only if async hooks run) | | error | Last recoverable SpreadsheetImportError, or null | | file, workbook, sheetName | What was uploaded and chosen | | sheetRows, headerRowIndex | The selected sheet and the highlighted header row | | columns | The column-to-field mapping | | rows | Validated records, each with __index and __errors | | visitedSteps | For stepper UIs |

Actions:

| Step | Action | | | -------------- | -------------------------------------------------------------- | ----------------------------------------------------------- | | Upload | selectFile(file) | Parse a file and advance to sheet or header selection | | Sheet | setPendingSheet(name) | Highlight a sheet without committing to it | | | selectSheet(name) | Commit to a sheet | | Header | setPendingHeaderRow(index) | Highlight a header row without committing to it | | | selectHeaderRow(index) | Commit to a header row | | Match columns | setColumnField(i, key) | Map a column onto a field, or null to unmap it | | | ignoreColumn(i) · unignoreColumn(i) | Leave a column out of the import, or bring it back | | | setColumnOption(i, entry, value) | Map one distinct value of a select column onto an option | | | setFieldConstant(key, value) | A value for the whole import, wherever no column has one | | | commitColumns() | Normalize, validate and go to the validate step | | Validate | updateCell(rowId, key, v) · updateRow(rowId, patch) | Edit a record; it is revalidated | | | deleteRows(ids) · addRow() | Remove or add records | | | submit() | Call onSubmit with the result | | Anywhere | back() · goToStep(step) · reset() | Navigate, or start over | | | clearError() | Dismiss state.error |

Pending selections live in the store rather than in component state, so a Continue button rendered anywhere — a dialog footer, a toolbar — can act on them.

Row mutations are serialized, so overlapping edits cannot clobber each other's keystrokes.

License

MIT. See LICENSE.