# Import: a spreadsheet into the grid The other direction: a file someone was handed, into the grid, with the columns lined up, the numbers as numbers and the bad rows flagged before anything lands. This page does it a step at a time on inline text first, so every example runs without a file picker, and then with a real file through the dialog. [Data import](https://svgrid.com/docs/help/import/) is the reference behind it. Import is `@svgrid/enterprise`: `installEnterprise(api)` puts `importData` on the grid's api, and `SvImportDialog` is the finished UI around it. `.xlsx` is read by the package's own values-only reader, which needs the `jszip` peer; nothing leaves the browser. The examples share an empty grid of orders and the text a customer's export usually looks like. ```svelte ``` ## The call `api.importData({ file })` reads a `File`, a `Blob` or a string of inline text, sniffs the format (`.xlsx`, `.csv`, `.tsv` or `.json`) and returns the parsed rows without writing anything: `headers` as the file spelt them, `rows` mapped and typed, `errors`, and the counts. Writing is a second step, `api.addRows`, so the app decides. ```svelte

{note}

(api = installEnterprise(a))} /> ``` The rows land, and mostly in the wrong place: nothing told the importer that `Order #` is `orderId`. A header it is not told about falls through to a default (lowercase, spaces to underscores, punctuation dropped), so `Customer Name` became a field called `customer_name` and `Order #` a field called `order_`, neither of which the grid has a column for. ## Mapping the headers `columnMap` is source header to target field: a rename per header, and `null` for a column that should not land at all, which is how a note column or a personal-data column stays out of the client. ```svelte

{note}

(api = installEnterprise(a))} /> ``` `autoMap: true` writes that map for you from the grid's own columns, matching a header to a column by its label first and its field second, after dropping case, spaces and punctuation, so `Unit Price`, `unit_price` and `UnitPrice` all find a column labelled Unit price. It is an exact match after that, not a guess: `Customer Name` does not find `customer`, which is what `columnMap` is for. It also reads each column's `format` to decide the type a cell should coerce to. Anything you pass in `columnMap` still wins over the guess: ```ts await api.importData({ file, autoMap: true, columnMap: { 'Order #': 'orderId' } }) ``` ## Numbers as numbers Every cell walks through the coercions a spreadsheet export needs: `$1,240.00` becomes `1240`, `1,234,567` a number, `true` a boolean, an ISO date stays an ISO string, an empty cell is `''`. The Total column above is already summable; the grid's `currency` format then draws it. A cell that will not convert keeps its text, which is what the next step catches. ## A validator flags the bad rows `validator(row, rowIndex)` returns `{ field, message }` entries for a row, and they come back in `result.errors` with the row index, so a review screen can point at the cell. The refund line and the address with no `@` in the sample are what a validator is for: ```svelte

{note}

{#if errors.length} {/if} (api = installEnterprise(a))} /> ``` One row is held back with two reasons, the refund whose address has no `@`; three land. Whether a row with an error is skipped, fixed or refused with the whole file is the app's call, which is why the default is a preview. ## Preview, then commit `commit: true` appends the rows in the same call, at `commitAt` (`'top'`, `'bottom'` or an index), and refuses to write when there is any error, from the validator or from a cell that would not coerce, so a review screen still has to show `result.errors`: ```ts const r = await api.importData({ file, autoMap: true, validator, commit: true, commitAt: 'top' }) if (r.errors.length) showReview(r) // nothing was written ``` ## The dialog `SvImportDialog` is the review flow, finished: drop a file or paste, the headers auto-mapped to the grid's columns with a select per header to retarget one, a typed preview with the bad cells highlighted, Append or Replace all, and the commit through the api. A header mapped to "Import as new field" opens a small column configurator, so a CSV into an empty grid can define the columns as it maps them. Drop any `.csv` or `.xlsx` on it: ```svelte
(note = `imported ${r.rows.length} rows`)} /> {note}
(api = installEnterprise(a))} /> ``` The file is read once; retargeting a header re-maps in memory, so an `.xlsx` is never parsed twice. `createColumns={false}` turns the configurator off, `allowReplace={false}` hides Replace, `defaultMode` picks the initial one, and `onImported` hands the rows to the store or the database the app keeps beside the grid. > Live demo: Import - dialog + auto-mapping - https://svgrid.com/demos/204-import-dialog/ ## Guard-rails for a file you did not make An upload is untrusted input. `maxBytes` rejects a file before it is read, `maxRows` caps the rows (`overLimit: 'truncate'` keeps the first N and flags `truncated` instead of throwing), `maxErrors` stops collecting past N, `dedupeBy` drops repeats by a field keeping the last, `encoding` reads a `windows-1252` export, `signal` cancels a long parse and `onProgress` feeds a bar. The dialog defaults to 25 MB and 500 errors. ```ts const controller = new AbortController() const r = await api.importData({ file, autoMap: true, maxBytes: 25 * 1024 * 1024, maxRows: 200_000, overLimit: 'truncate', maxErrors: 500, dedupeBy: 'orderId', signal: controller.signal, onProgress: ({ ratio }) => (progress = ratio), }) ``` Headers named `__proto__`, `constructor` or `prototype` are dropped, never assigned; the `.xlsx` reader resolves no external references and expands no entities. Past a few hundred thousand rows, the right home for the parse is a server. ## An importer of your own The dialog is built from three exported pieces, split so the file is parsed once and mapped as often as the user changes a select: ```ts import { readImportMatrix, autoMapColumns, inferImportColumnTypes, mapImportMatrixAsync } from '@svgrid/enterprise' const { format, matrix } = await readImportMatrix(file, 'auto', { maxBytes }) const columnMap = autoMapColumns(matrix[0], api.getColumns()) const columnTypes = inferImportColumnTypes(api.getColumns()) const { rows, errors } = await mapImportMatrixAsync(matrix, { columnMap, columnTypes, signal, onProgress }) ``` > Live demo: Excel / CSV import - https://svgrid.com/demos/53-excel-import/ ## See also - [Data import](https://svgrid.com/docs/help/import/) - the reference: every option and prop, the formats, the result shape, performance. - [Export a report](https://svgrid.com/docs/help/export/report/) - the way out. - [Cell data types](https://svgrid.com/docs/help/cells/cell-data-types/) - the column formats auto-mapping reads its types from. - [The spreadsheet's files](https://svgrid.com/docs/help/sheet/files/) - opening an `.xlsx` into a sheet document instead of a grid. --- SvGrid is the Svelte 5 data grid (`npm install @svgrid/grid`, MIT). This page: https://svgrid.com/docs/help/export/import-spreadsheet/ . All docs: https://svgrid.com/llms.txt