Loading files¶

pivotkit-core/import reads a file a person picks into a table the report can use: CSV, TSV and text, JSON, and Excel workbooks with the pivotkit-xlsx plugin. It runs in the browser (nothing is uploaded) and in Node. The table it returns is encode's columnar input, so it goes into the engine without an object per row.

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 <input type="file"> 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

Formats¶

The reader is picked by the file name's extension, then by its media type; the host's plugins come before the built-in readers (readerFor). 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, UTF-16 little or big endian); without one the file is read as strict UTF-8, and if a byte is not valid UTF-8 the whole file is read 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 comma, semicolon, tab or vertical bar, whichever splits the first 100 lines into the same number of fields most often (a tab first for .tsv).
• 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. Lines above it (a title, a date, a licence line, as official exports have) are skipped, with a note.

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 are trimmed; control characters and the characters that turn text around (U+202E and its kind) are taken out; 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.
• Values lose control and direction characters; tabs and line breaks become a space; text is trimmed and cut at limits.textLength characters. Text that looks like a formula (=, +, -, @) stays as it is: the exports guard it.
• Empty rows are left out; a column without a name and without a single value is left out; a row with more values than there are names adds columns named Column N. Each is a note.

How a loaded file is named¶

A file loaded in the report (allowImport) becomes the report's source while its data is shown. Its id names the views saved for it in the browser store (templateStore="browser") and in the view files and links shared from it, so the same file loaded again finds its views and other data does not. The rule, in this order:

1. The name is the file's File.name as the browser gives it, cleaned as a column name is: control characters (but tabs and line breaks), the characters that turn text around and the invisible ones (zero-width characters, the soft hyphen, the byte order mark, tag characters, a lone half of a surrogate pair) taken out, each run of tabs and line breaks as one space, trimmed; then cut to 256 characters (UTF-16 code units, a fixed cut that limits does not change, and not trimmed 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): cleaned the same way and cut to limits.name characters (256 by default); a number or date in the header row as its text (a date in ISO form); columns without a name and without a value left out; an empty name Column N (N its place among the columns kept); a name that repeats, compared without case, Name (2) ("Team" and "team" become "Team" and "team (2)"); nested JSON as a.b. A type picked in the preview changes nothing.
3. The hash: 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 without it (plain http on a host other than localhost) uses pivotkit-react's 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: the name itself, or, above 120 characters, its first 117 followed by ... (116 when the 117th is the first half of a surrogate pair, so no character is split). The hash always covers the whole name.

The first row is SHA-256 of tickets.csv\0Id\0Team\0Opened\0Hours\0Zip (e51bd4bc21e98ee818b3a8efcb49347b…). Reordered, renamed or added columns, or another file name (Tickets.csv too: the name is compared as it is), make other data. The sheet of a workbook is not part of it: two sheets with the same columns share their views. The rows are not part of it either: a newer export of the same table keeps its views.

Types¶

A column's type is inferred from all its values, unless the caller names it (types, by position or by column name).

Values with a type of their own (a spreadsheet's cells, JSON's numbers and booleans) keep it; 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).

retype(table, types) gives the table with other types from the values as they were read (kept 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¶

message is the English text (noteMessage(code, params)); a UI shows its own text per code. params can quote the file (a sheet name, a JSON key, a separator), cleaned and at most 256 characters: show them as text, never as HTML.

Limits¶

Each file is checked against LIMITS (the host's limits merged over them) before the step it guards, so a file that is too large costs no memory first:

A file over a limit throws PivotkitError LIMIT_EXCEEDED with details { limit, value, max, file } and a message that 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 limits.rows. The previous data stays.

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.

Progress, cancel and workers¶

onProgress({ phase, done, total }) reports read (bytes), unpack (a workbook's bytes), parse (characters or rows) and is called between steps; 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; 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 is started (fflate, which read-excel-file unpacks with, would start one for parts over 512 KiB), and 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¶

Types: ImportTable, ImportNote, ImportType, ImportHints, ImportProgress, ReadOptions, ReaderPlugin, ReaderModule, SheetInfo.