--- title: Spreadsheet Import slug: spreadsheet-import kind: pattern summary: Bring an existing spreadsheet into the system by mapping its columns, previewing what will happen, and reporting per-row results — never all-or-nothing. problem: >- The data already exists in a spreadsheet, and the only way in is to retype it. So people either do not adopt the system, or they keep the spreadsheet as the real record and treat the software as a copy that drifts. family: [capture] data_shape: [collection] principles: [friction, orientation, progressive-disclosure] interaction: [editing] density: medium complexity: high status: stable visibility: public use_when: - The data exists elsewhere, in volume, and retyping is the barrier to adoption. - Records are homogeneous, so a column mapping is meaningful. - Imports repeat — monthly price lists, weekly timesheets, a migration. avoid_when: - It is a one-off of ten rows. Quick add is faster than building this. - The records are heterogeneous, so no column mapping applies. - An API or a direct integration is available and would keep the data in sync rather than copying it once. alternatives: - slug: quick-add when: A handful of records, typed where they belong. - slug: minimal-form when: Records are created one at a time as real-world events happen. ask_leo: | Build a spreadsheet import for this record type. - Accept the file formats people actually have, including .xlsx and .csv, and accept a paste of tabular data too. - Guess the column mapping from the headers, then SHOW the guess and let it be corrected. Never map silently. - Show a preview of the first rows exactly as they will be created, with the values converted — dates parsed, numbers stripped of currency symbols — so people see the interpretation, not the raw text. - Validate every row before importing anything, and report problems per row with the row number and the specific issue. Never fail the whole file for one bad row. - Let people choose what happens to invalid rows: skip them and import the rest, or stop. Skipping is usually right, and the skipped rows must be downloadable so they can be fixed and re-imported. - Say what will happen to rows that match existing records — create, update or skip — and make that an explicit choice, not a hidden default. - Report the outcome as counts by category, and link to what was created. - Make the import undoable, or at least reviewable as a batch, for a period after it runs. related: - title: Excel-to-app import QA checklist url: /cookbook/inline-editable-table summary: The table people land in after the import, where they will correct what came through. - title: Minimal Form url: /patterns/minimal-form summary: The single-record path this pattern exists to avoid repeating 300 times. --- ## Anatomy ``` 1 UPLOAD 2 MAP COLUMNS 3 PREVIEW & VALIDATE 4 RESULT ┌──────────┐ Your column → Field Row 1 ✓ Riverside… ✓ 284 created │ drop a │ "Client" → Client Row 2 ✓ Kestrel… ✓ 12 updated │ file │ "Amt" → Amount Row 3 ✗ Amount "n/a" ✗ 4 skipped └──────────┘ "Due" → Due date is not a number ↓ download "Notes" → (ignore) the 4 rows ``` The four steps are not decoration — each one prevents a specific failure: 1. **Upload** accepts what people actually have, including a paste. 2. **Mapping is shown and correctable.** A silent guess is how a phone number column ends up in the reference field. 3. **Preview shows the converted values**, so people check the interpretation rather than the input. 4. **Per-row results**, with the failures downloadable. ## Why it works It removes the single largest barrier to adopting an internal tool. Data that already exists is not a nice-to-have — it *is* the business, and a system that cannot accept it is a system that will run alongside the spreadsheet forever. The per-row failure model is what makes it usable in practice. An all-or-nothing import of three hundred rows fails on row 214 and gives back nothing, so the person fixes one cell and waits again. Importing 296 and handing back 4 to fix converts an afternoon into five minutes. ## The parts everyone skips - **The conversion preview.** People do not check raw text; they check meaning. Showing `14/07/26` is useless — show that it will be stored as 14 July 2026, and the day-month ambiguity gets caught before it corrupts three hundred rows. - **Match behaviour.** Whether an incoming row creates, updates or is skipped is the highest-consequence decision in the whole flow, and it is usually a hidden default. - **The downloadable failures.** Without them, "4 rows failed" means re-deriving which four from a file of three hundred. - **Undo.** An import is a bulk action with a large blast radius. It is exactly the case where [confirmation](/patterns/confirmation-vs-undo) is warranted, and reviewability afterwards matters more than the dialog. ## Getting it wrong - **All-or-nothing.** The defining failure of bad importers. - **Silent column mapping**, producing a plausible-looking import that is wrong in one column. - **Errors reported without row numbers**, so the person cannot find them. - **No preview**, so date and number formats are discovered afterwards. - **Hidden update behaviour**, quietly overwriting records the person expected to be created. - **Accepting only CSV**, when everyone has .xlsx and converting is an extra step that loses formatting. ## Exemplars **Stripe's product and price import** shows the preview-and-map flow done carefully, because a mis-mapped price column has immediate financial consequences. **Airtable's import** is the reference for match behaviour: choosing the key field and what happens on a match is an explicit step, not a checkbox. **Mailchimp's contact import** is worth studying for the failure report — the skipped rows come back as a file you can fix and re-upload, which is the whole loop closed. The extractable rule: **an import is a conversation about interpretation, not a file transfer.** Every step exists to show the person what the system thinks their data means, before it commits.