--- title: Loading files description: pivotkit-core/import reads CSV, TSV, text and JSON files (and Excel with the pivotkit-xlsx plugin) into a typed table for the report, in the browser or in Node, with limits checked before the expensive steps. --- # Loading files `pivotkit-core/import` reads a file a person picks into a table the report can use. It reads: - CSV, TSV and text - JSON - Excel workbooks, with the [pivotkit-xlsx](plugins.md#pivotkit-xlsx) plugin It runs in the browser, where nothing is uploaded, and in Node. The table it returns is `encode`'s [columnar input](core.md#columnar-input), so it goes into the engine without an object per row. !!! note "In the report" This page describes the readers. The report uses them itself with [`allowImport`](react.md#prop-allowimport), the element's `allow-import`. The person then gets a Data menu, can drop a file onto the report, and picks the column types in a preview ([Load your own data](../use/load-data.md)). A host that reads a file itself passes the table as `data`. Both take the columnar input `{ columns, types, data }`. ```js import { readFile, retype } from "pivotkit-core/import"; import { encode } from "pivotkit-core"; import xlsxReader from "pivotkit-xlsx"; // optional: Excel workbooks const table = await readFile(file, { // a File from or a drop plugins: [xlsxReader], signal: controller.signal, // Cancel onProgress: ({ phase, done, total }) => show(phase, done / total) }); table.notes; // what was read how, and what was changed const data = encode(table); // { columns, types, data } is encode's input const asText = retype(table, { Zip: "string" }); // another type, without reading the file again ``` In Node, make a `File` from the file's bytes and its name. The name picks the reader: ```js import { readFile as readBytes } from "node:fs/promises"; import { readFile } from "pivotkit-core/import"; import xlsxReader from "pivotkit-xlsx"; const table = await readFile(new File([await readBytes("sales.xlsx")], "sales.xlsx"), { plugins: [xlsxReader] }); ``` ## Formats | Format | Reader | Files | What it reads | | --- | --- | --- | --- | | CSV, TSV, text | built in (`csvReader`) | `.csv`, `.tsv`, `.txt`, `.tab` | Delimited text with a header row. | | JSON | built in (`jsonReader`) | `.json` | An array of objects; an array of arrays (the first holds the names); `{ columns, rows }`; the columnar `{ columns, types, data }`; or one of these under a property (`{ "data": [...] }`). | | Excel | [pivotkit-xlsx](plugins.md#pivotkit-xlsx) | `.xlsx` | One sheet of a workbook: the first visible one, or the one asked for. | `readerFor` picks the reader by the file name's extension, then by its media type. The host's plugins come before the built-in readers. The built-in readers are plugins too. Their code loads when the first file of their kind is read. So `pivotkit-core/import` itself stays small, and the main entry `pivotkit-core` does not load any of it. ## How a CSV file is read - **Encoding**: a byte order mark decides: UTF-8, or UTF-16 little or big endian. Without one, the reader reads the file as strict UTF-8. If a byte is not valid UTF-8, it reads the whole file as Windows-1252, which Excel writes as "CSV" on a Western Windows. Invalid bytes never become replacement characters. - **Separator**: Excel's first line `sep=;` names it. Else the reader takes comma, semicolon, tab or vertical bar, whichever splits the first 100 lines into the same number of fields most often. For `.tsv` it tries a tab first. - **Quotes**: a value in double quotes may hold the separator, line breaks and doubled quotes (`""`). A quote that is never closed stops the read with the line it opened in. - **The header**: the first row with at least half as many values as the fullest of the first 50 rows. The reader skips the lines above it, with a note. Official exports often have a title, a date or a licence line there. ## Column names and values Every reader gives the same table for the same content. The readers parse their format into rows, and one shared part (`createTable`) does the rest. - **Names**: `createTable` trims them. It takes out control characters and the characters that turn text around (U+202E and its kind). Line breaks and tabs become spaces. An empty name becomes `Column 3`, its position. A name that repeats, compared without case, becomes `Name (2)`. Each change is a note. - **Nested JSON**: the JSON reader flattens a nested object into columns with dotted names. `{ "meta": { "Secret": 1 } }` becomes the column `meta.Secret`, with the note `JSON_FLATTENED`. It flattens four levels. A deeper object stays JSON text in one column. - **Values**: `createTable` takes out control and direction characters. Tabs and line breaks become a space. It trims text and cuts it at `limits.textLength` characters. Text that looks like a formula (`=`, `+`, `-`, `@`) stays as it is: the [exports](../security.md#exports) guard it. - **Empty rows and columns**: `createTable` leaves out empty rows, and a column without a name and without a single value. A row with more values than there are names adds columns named `Column N`. Each is a note. ## How a loaded file is named { #how-a-loaded-file-is-named } A file loaded in the report ([`allowImport`](react.md#prop-allowimport)) becomes the report's [`source`](react.md#prop-source) while its data is shown. Its `id` names the views saved for it: - in the browser store (`templateStore="browser"`) - in the view files and links shared from it So the same file loaded again finds its views, and other data does not. pivotkit builds the id in this order: 1. **The name** is the file's `File.name` as the browser gives it. pivotkit cleans it as it cleans a column name: - It takes out control characters, except tabs and line breaks. - It takes out the characters that turn text around. - It takes out the invisible ones: zero-width characters, the soft hyphen, the byte order mark, tag characters, a lone half of a surrogate pair. - Each run of tabs and line breaks becomes one space. - It trims the name. - Then it cuts the name to 256 characters, counted as UTF-16 code units. The cut is fixed: `limits` does not change it, and pivotkit does not trim the name again. An empty name becomes `file`. Two names that differ only in characters taken out are the same name. 2. **The columns** are the names of the table after the readers' cleanup ([above](#column-names-and-values)): - pivotkit cleans them the same way and cuts them to `limits.name` characters, 256 by default. - A number or date in the header row counts as its text. A date counts in ISO form. - It leaves out columns without a name and without a value. - An empty name is `Column N`, where N is its place among the columns kept. - A name that repeats, compared without case, is `Name (2)`. "Team" and "team" become "Team" and "team (2)". - Nested JSON is `a.b`. A type picked in the preview changes nothing. 3. **The hash** is SHA-256 of the UTF-8 bytes of the name and the column names in their order, joined with U+0000. The browser's `crypto.subtle` computes it. A page over plain `http` on a host other than `localhost` has no `crypto.subtle`. There pivotkit-core uses its own implementation, which gives the same digest. 4. **`source.id`** is the shown name, `#` and the first 16 hex digits of the hash. **`source.name`** is the shown name. That is the name itself, or, above 120 characters, its first 117 followed by `...`. It takes 116 when the 117th is the first half of a surrogate pair, so no character is split. The hash always covers the whole name. | File | Columns | `source.id` | | --- | --- | --- | | `tickets.csv` | `Id`, `Team`, `Opened`, `Hours`, `Zip` | `tickets.csv#e51bd4bc21e98ee8` | | `tickets.csv` | `Team`, `Id`, `Opened`, `Hours`, `Zip` | `tickets.csv#d11fe64346f1d906` | The first row is SHA-256 of `tickets.csv\0Id\0Team\0Opened\0Hours\0Zip` (`e51bd4bc21e98ee818b3a8efcb49347b…`). These make other data: - reordered, renamed or added columns - another file name, `Tickets.csv` too, since the name is compared as it is The sheet of a workbook is not part of the id. The views saved, downloaded and linked from a workbook name their sheet next to the id, in `source.params.sheet` ([view file](view-file.md#the-sheet-of-a-workbook)). So two sheets with the same columns keep their views apart. A view of another sheet of the same file reads that sheet again when you open it ([saved views](../use/load-data.md#after-loading)). The rows are not part of the id either, so a newer export of the same table keeps its views. `sourceIdOf(name, columns)` of `pivotkit-core/import` computes the id and returns a promise of it. `shownName` and `SHOWN_MAX` give the shown name. The command line names the files it writes a view for the same way (`describe --suggest`, `render --save-view`). So the report opens such a view as a view of that file, on its sheet. ## Types The reader infers a column's type from all its values, unless the caller names it. The caller names it with `types`, by position or by column name. | Values | Type | Notes | | --- | --- | --- | | `true`, `false` (also `WAHR`, `FALSCH`), in any case | boolean | | | `2026-11-10`, `2026-11-10T08:30`, with or without an offset | date | ISO text without an offset is the wall clock as written; with one, an instant read in `encode`'s `timeZone`. | | `10.11.2026`, `1.2.2026 08:30` | date | Day, month, year. Read before numbers: `10.11.2026` is not the number 10112026. | | `02/03/2026` | date | The values decide the order (a 13 in the first place is a day); if every value fits both, the `locale` option decides (`en-US`: month first, others day first), with a note. | | `1234.5`, `-0.25`, `1e3` | number | English: no group separators, so `1,5` is never 15. | | `1.234,5`, `1234,5` | number | German. A column that fits both notations is read in the one of the separator: German with semicolons, English otherwise. | | `01067`, `007` | string | A leading zero keeps the column as text: postcodes and identifiers. | | A whole number of more than 15 digits | string | An identifier; a number would lose digits. | | Anything else, or a mix | string | | Values with a type of their own keep it, such as a spreadsheet's cells or JSON's numbers and booleans. A column of one kind is that type. A mix is text. JSON strings stay text unless they are ISO dates, as in `encode`: JSON says what is a number. A number field named ID, Nr, No, Number or Key stays a number. The report counts it instead of summing it ([identifier fields](../concepts/fields.md#identifier-fields)). `retype(table, types)` gives the table with other types. It works from the values as the reader first read them, which the table keeps in `table.raw`. So a preview can let a person change a column's type without reading the file again. Text read as a number goes back to the text the file holds (`1.234,5`). A value that does not convert is blank, with a note that counts them. ## The table | Key | What | | --- | --- | | `columns` | The column names, unique without case. | | `types` | One per column: `"string"`, `"number"`, `"date"` or `"boolean"`. | | `data` | One array per column, of values of its type; `null` is blank. Dates are ISO text. | | `raw` | One array per column, the cleaned values as read (a text column shares its array with `data`). `retype` reads it; drop the table once it is encoded. | | `detected` | The inferred type of each column, before an override. | | `rowCount` | Rows. | | `notes` | `{ code, params, message }` for each thing worth saying (table below). | | `hints` | How text was read (`decimal`, `dateOrder`), so `retype` reads it the same way. | | `unnamed` | The columns the file gives no name, as named here (`Column 7`): a blank header cell, or a row with more values than the header. Such a column's values may belong to another column, shifted by a separator inside a value; pivotkit-server hides them. A name such as `Column 7` is its position, so a host's rule for it follows whatever column lands there. A reader plugin's own table may leave it out. | | `longRows` | The rows (0-based) with more cells than the named header (its empty cells at the end left out), whose values may be shifted. None where the file is uniform: every row as wide, every cell beyond the named header empty (a separator that ends every line). In a file with mixed row widths every row wider than the named header counts: a file where only some rows end with a separator has those rows here, or all of them if every row is wider (pivotkit-server's `ROWS_DROPPED` note gives the count). A shifted row of the header's width (a cell at the end dropped), or one in such a uniform file that fills its empty last cell, cannot be told: hidden columns in CSV are safe only in well-formed files. pivotkit-server leaves such rows out of a source with a field below `show`. A reader plugin's own table may leave it out. | A reader plugin's table built by hand may leave out `raw`, `detected`, `rowCount` and `hints`. `readFile` fills them in: - `raw` as `data` - `detected` as `types` - the row count - `hints` as `createTable` sets them for the locale Without `raw`, a type changed in the preview is read from the typed values, not from the text as the file had it. A field that is given must be right. This holds for `hints` too: `decimal` is `"."` or `","`, `dateOrder` is `"dmy"` or `"mdy"`. The table also counts against the limits below. Otherwise `readFile` throws ([Errors](#errors)). | Note `code` | When | `params` | | --- | --- | --- | | `ENCODING` | Always, for text | `encoding` | | `SEPARATOR` | Always, for delimited text | `separator`, `name` | | `LINES_SKIPPED` | A title block above the header | `count` | | `COLUMN_RENAMED` | An empty or repeated name | `position`, `from`, `to` | | `COLUMNS_RENAMED_MORE` | More than 20 renamed | `count` | | `COLUMN_DROPPED` | A column without a name and values | `position` | | `COLUMNS_ADDED` | Rows longer than the header | `rows`, `count` | | `ROWS_EMPTY` | Empty rows left out | `count` | | `TEXT_CUT` | Text longer than `limits.textLength` | `count`, `max` | | `CHARACTERS_REMOVED` | Control or direction characters | `count` | | `DATE_ORDER` | Dates with slashes that fit both orders | `column`, `order` | | `VALUES_NOT_CONVERTED` | Values that are not of the column's type | `column`, `type`, `count` | | `JSON_PROPERTY` | The rows were under a property | `property` | | `JSON_FLATTENED` | Nested objects became `a.b` columns | `example` | | `SHEET` | A workbook: the sheet read | `sheet`, `count`, `visible` | | `USED_RANGE` | A workbook's used range reaches far below its values | `usedRow`, `lastRow` | | `ERROR_CELLS` | Cells with an Excel error (`#N/A`) | `count` | `message` is the English text (`noteMessage(code, params)`). A UI shows its own text per code. `params` can quote the file, such as a sheet name, a JSON key or a separator. They are cleaned and at most 256 characters. Show them as text, never as HTML. ## Limits pivotkit checks each file against [`LIMITS`](../concepts/limits.md#limits) before the step each limit guards, so a file that is too large costs no memory first. The `limits` option is merged over them. In the report, [`importLimits`](react.md#prop-importlimits) is merged over `limits`. | Limit | Default | When | | --- | --- | --- | | `fileBytes` | 100 MiB | The file's size, before a byte is read. | | `rows` | 1,000,000 | Rows of the table, counted as they are read. | | `columns` | 1,000 | Columns, as they appear. | | `importCells` | 10,000,000 | Rows x columns, counted as they are read; a workbook's shared strings and formats too. | | `textLength` | 32,767 | Characters of one value (Excel's cell limit); longer text is cut, with a note. | | `archiveEntries` | 10,000 | Parts of an archive (an `.xlsx` is a ZIP), from its directory, before anything is unpacked. | | `unpackedBytes` | 500 MiB | What the parts unpack to, from the directory, before anything is unpacked (a ZIP bomb). | A file over a limit throws `PivotkitError` `LIMIT_EXCEEDED` with `details` `{ limit, value, max, file, what }`. The message names the file, the limit and what to change: `"tickets.csv" has more than 1,000,000 rows (limits.rows): load a smaller extract of it, or a host can raise this limit`. `what` is what was counted, in English, as the message says it. The previous data stays. pivotkit-react says it in the report's language from `details`, after the file's name: "big.csv could not be loaded: it has more than 1,000 rows (limits.rows): …". - It does so for the limits `rows`, `columns`, `fileBytes`, `importCells`, `archiveEntries` and `unpackedBytes`. - The texts are `labels.dataImport.overLimit` and `limitWhat`. - When a workbook's shared strings and formats reach `importCells`, `details.what` names them. The report uses the words of `workbookCells`, "different texts and formats". - The key is the one of `limits`, also where the host set `importLimits`. Any other error is the reader's message without the file's name in front. ## Errors A file that cannot be read throws `PivotkitError` with the code `INVALID_FILE`, `details.file` and `details.reason`. The message names the file and says what to do. | `reason` | When | | --- | --- | | `NO_READER` | No reader accepts the file's extension or type. | | `WRONG_TYPE` | The file is something else: a workbook named `.csv`, a CSV named `.xlsx`, a PDF, a ZIP of something else. | | `BINARY` | A text file holds binary data. | | `UNCLOSED_QUOTE` | A quote is never closed; `details.line`. | | `INVALID_JSON` | The JSON does not parse. | | `NO_TABLE` | The JSON holds no table; a workbook has no worksheet. | | `NO_SHEET` | The sheet asked for is not in the workbook. | | `XLS`, `XLSB` | An Excel 97-2003 workbook, an Excel binary workbook: save it as `.xlsx` or CSV. | | `ENCRYPTED` | A password-protected workbook. | | `MACROS` | A workbook with macros (`.xlsm`, `vbaProject.bin`). | | `XML_DECLARATION` | A part of a workbook declares a document type or entities, which a workbook never needs. | | `DAMAGED` | A workbook is damaged, cut off, a part unpacks to more than its directory states, or an XML part holds a text of more than 1 MiB or a tag of more than 256 KiB (an Excel cell holds at most 32,767 characters). | `readFile` throws `INVALID_ARGUMENT` when a reader plugin returns one of these. Each is a bug of the plugin, and the message names the plugin and the field. - a table of another shape - a field given wrong (`rowCount`, `raw`, `detected`, `hints`) - a note without a `code` - a table whose fields throw when read ## Progress, cancel and workers `onProgress({ phase, done, total })` is called between steps. It reports these phases: - `read`: bytes - `unpack`: a workbook's bytes - `parse`: characters or rows `signal`, an `AbortSignal`, stops a read between steps with the signal's `AbortError`. The readers give the event loop a turn every 16,384 rows. So a page stays responsive enough to show progress and a Cancel button. For files of tens of megabytes, a host can run `readFile` in a module worker of its own. The readers use no DOM, and the table is plain arrays, which `postMessage` copies. The readers start no worker themselves. The report runs the built-in ones in a worker where the host gives one ([`importWorker`](react.md#prop-importworker)). pivotkit-element brings its own, `dist/import-worker.js`. pivotkit-xlsx hands read-excel-file an uncompressed archive of the parts it needs, so no `blob:` worker starts. read-excel-file unpacks with fflate, which would start one for parts over 512 KiB. So a strict Content-Security-Policy without `worker-src blob:` works. A host that runs the readers in its own worker needs `worker-src 'self'`, or the worker's origin. ## Functions | Name | What | | --- | --- | | `readFile(file, options)` | Reads a `File` or `Blob` with the reader that accepts it. Options: `plugins` (the host's reader plugins), `reader` (one plugin, skipping the choice), `sheet` (a workbook's sheet, by name or 0-based index), `types`, `locale`, `limits`, `signal`, `onProgress`, `name` (the file name for messages, for a `Blob`).
Returns `Promise` | | `retype(table, types)` | The table with other column types, from `table.raw`.
Returns `ImportTable` | | `readerFor(file, plugins)` | The plugin that accepts a `{ name, type }`, or `undefined`. | | `loadReader(plugin)` | The plugin's reader module, loaded once and checked (`read`, optional `sheets`). | | `validatePlugin(plugin)` | Checks a plugin against the [contract](write-a-plugin.md#the-contract); throws `INVALID_ARGUMENT` that says what is wrong. | | `csvReader`, `jsonReader`, `BUILT_IN_READERS` | The built-in reader plugins. | | `PLUGIN_API_VERSION` | `1`. | | `createTable`, `bytesOf`, `readOptions`, `fileName`, `fileError`, `limitError`, `checkFileLimit`, `dateToIso`, `noteMessage`, `cleanText` | The shared part of every reader, for [plugins](write-a-plugin.md#the-helpers). | | `IMPORT_NOTE_CODES`, `IMPORT_TYPES` | The note codes and column types. | Types: `ImportTable`, `PluginTable` (what a plugin's `read` returns), `ImportNote`, `ImportType`, `ImportHints`, `ImportProgress`, `ReadOptions`, `ReaderPlugin`, `ReaderModule`, `SheetInfo`.