Importing Spreadsheets
Stack9 can load and update records from a spreadsheet. A user uploads a CSV or XLSX file, the importer reads and checks every row, shows a preview of what would change, and writes nothing until the user confirms it. Then it writes the whole file in one transaction, or nothing at all.
An instance only has to describe what a file looks like: which sheets and columns map to which entities and fields. That description is an import type. The screens, the preview, the background processing and the safety checks come from core.
Time to complete: about 30 minutes for a first import type.
How an import runs
- A user opens Settings → Data import → Import jobs, clicks Create, picks an import type and a mode, and uploads a file. That creates an
import_job. - A background worker validates the job: it reads the file, checks every row, matches rows to existing records, and works out what would change. The job moves to Ready with a preview, or to Invalid with the errors.
- The user reviews the preview: counts, and every row with its action (Create, Update, Delete, Unchanged or Error) and what changes. Then they click Confirm or Cancel.
- On Confirm, a worker applies the job as the user who confirmed it, through the normal entity service, so entity hooks, history and automations all run. The job ends Completed, or Errored with nothing written.
If the data changed between the preview and the Confirm, nothing is written and the job goes back to Ready with a fresh preview. The uploader is notified when the preview is ready (or invalid), and the confirmer when the import finishes.
Modes
An import type says which modes it offers; the user picks one per job.
| Mode | Records in the file | Records not in the file |
|---|---|---|
| Incremental | Created or updated | Left alone |
| Replace all | Created or updated | Soft-deleted |
| Delete only | Soft-deleted | Left alone |
In Replace all, every live record of the target entity that isn't in the file is soft-deleted. Only offer it where the file really is the full list. For a target owned by another (see owned rows), only the owners named in the file are affected.
Before you start
The instance needs:
-
Stack9 6.0 or later (
@april9au/stack9-sdk,stack9-core-moduleandstack9-ui). -
The queue consumer running. Imports run as queue messages. Deployed stacks run the consumer already. Locally, start core with
yarn dev --profile=<aws-profile>, using a profile that can read your local queues; otherwise jobs stay Pending. -
A long visibility timeout on the long queue. Validate and apply run on the
longqueue, and an apply can take several minutes. Set the long queue's visibility timeout to 1800 seconds, with the app variableAPP_MESSAGE_QUEUE_VISIBILITY_TIMEOUT_LONGor the environment variableMESSAGE_QUEUE_VISIBILITY_TIMEOUT_LONG, so a running import isn't delivered a second time. (A second delivery is harmless, because the job is claimed, but it wastes a worker.) -
The importer's screen components registered in the frontend. The job and type screens use three native components from
@april9au/stack9-ui:ImportJobStatus,ImportJobPanelandImportTypePanel. Instances list core components inapps/stack9-frontend/src/_app.core.module.tsx, whichstack9 upgradegenerates from the instance template. If your generated file doesn't list them yet, add them as instance components inapp.stack9.instance.tsxuntil it does:apps/stack9-frontend/src/app.stack9.instance.tsximport {
ImportJobPanel,
ImportJobStatus,
ImportTypePanel,
} from '@april9au/stack9-ui';
export const instanceConfig: Stack9InstanceConfig = {
// ...
instanceComponents: [ImportJobStatus, ImportJobPanel, ImportTypePanel],
};Without them, those parts of the screens show a red "is not registered" alert.
1. Define the import type
Create one class per import type in packages/stack9-stack/src/import-types/. It extends S9ImportTypeDefinition from the SDK.
This example imports campuses with their locations, from a workbook with two sheets:
import { ImportMode, S9ImportTypeDefinition } from '@april9au/stack9-sdk';
/** Campuses with the locations they own, one XLSX sheet each. */
export class CampusLocationsImport extends S9ImportTypeDefinition {
key = 'import_std_campus_locations';
name = 'Import campuses and locations';
description = 'Campuses by name, each with its locations';
modes: ImportMode[] = ['Incremental', 'Replace all', 'Delete only'];
targets = [
{
key: 'campuses',
entityKey: 'std_campus',
sheet: 'Campuses',
matchOn: ['name'],
columns: [{ header: 'Name', field: 'name', required: true }],
},
{
key: 'locations',
entityKey: 'std_location',
sheet: 'Locations',
matchOn: ['campus_id', 'name'],
columns: [
{
header: 'Campus',
field: 'campus_id',
required: true,
lookup: { target: 'campuses', matchOn: 'name', owned: true },
},
{ header: 'Location', field: 'name', required: true },
],
},
];
}
A one-sheet type leaves out sheet on its only target. It then reads the first sheet of an XLSX, or a CSV.
Import type properties
| Property | Required | What it does |
|---|---|---|
key | Yes | Stable id. Jobs and the import_type row are matched on it, so don't rename it once it has jobs. |
name | Yes | What users pick from. |
description | No | Shown on the Import types screen. |
modes | Yes | The modes offered: any of 'Incremental', 'Replace all', 'Delete only'. |
maxRows | No | Largest number of data rows in a file, across all sheets. Defaults to 10,000. |
targets | Yes | The entities the file writes, one per sheet (below). |
paramsSchema | No | Parses the job's Parameters (JSON entered on the job). Anything with a parse(input) method, such as a zod schema. Without it, a job must have no parameters. |
validateRow | No | Extra row checks (see Row hooks). |
transformRow | No | Rewrites a row before it's checked (see Row hooks). |
Target properties
| Property | Required | What it does |
|---|---|---|
key | Yes | Id of the target, used by lookups and shown on each preview row. |
entityKey | Yes | The entity written to. |
sheet | With several targets | The sheet the target reads, matched ignoring case. |
matchOn | Yes | The fields that identify an existing record, e.g. ['code'] or ['campus_id', 'name']. Each needs a column. A row that matches no live record is a Create. |
columns | Yes | The columns of the sheet (below). |
Targets are written in dependency order (owners before the rows that point at them), whatever order you declare them in.
Column properties
| Property | Required | What it does |
|---|---|---|
header | Yes | The header text in the file, matched ignoring case and extra spaces. |
field | Yes | The entity field it fills. |
required | No | The header must be present and every row must have a value. |
lookup | For relation fields | How the cell finds the record a SingleDropDown or MultiDropDown points at (below). |
Field types
Each cell is converted to the field's type. A value that can't be converted is a row error, so it shows in the preview, not as a failed write.
| Field type | Accepted in the file |
|---|---|
TextField, RichTextEditor, ColorPickerField | Text, trimmed. |
NumericField | A number. With precision 0 it must be whole; otherwise it's rounded to the precision. |
DateField | YYYY-MM-DD, an ISO date-time, DD/MM/YYYY, or an Excel date cell. |
Checkbox | true/yes/y/1 or false/no/n/0, any case. |
OptionSet | One of the field's values, any case. |
SingleDropDown, MultiDropDown | A natural key resolved through a lookup, never an id. |
Other field types (files, JSON, maps and so on) can't be imported. An empty cell clears an optional field; an optional column left out of the file leaves that field as it is.
Lookups
A relation field is filled from a natural key, such as a name or code, not a database id. There are two kinds:
// The key of a record already in the database (another entity).
lookup: { entityKey: 'tag', matchOn: 'code', separator: ';' }
// The key of a row in another sheet of the same file, or of a record already saved.
lookup: { target: 'campuses', matchOn: 'name' }
matchOnmust be a plain field (text, number, date, checkbox or option set) of the record looked up.- A
MultiDropDownneeds aseparator, e.g.Science;Maths. - A key that matches no record, or more than one, is a row error.
- Lookups never create or change the records they point at.
Owned rows
Add owned: true to a target lookup when the rows of one sheet belong to a row of another, like locations to their campus. Then:
- each owned row is shown under its owner in the preview;
- in Replace all, only the owners named in the file have their owned rows replaced, and other owners' rows are left alone;
- deleting an owner also deletes the rows it owns.
A target can have one owned lookup, on a SingleDropDown field.
2. Register it
Export the class from an import-types index:
export * from './campusLocationsImport';
Then pass that module to registerClass in the instance's entry point, next to whatever it already registers:
import { registerClass } from '@april9au/stack9-sdk';
import * as importTypes from './import-types';
export default registerClass(importTypes);
A module can ship import types the same way.
3. Restart and check
On startup, core checks every import type against the entity definitions, then saves it to the import_type table (by key):
-
A broken definition stops startup, with every problem listed, for example:
Import type 'import_std_campus_locations' is invalid: target 'campuses' needs a 'sheet': the type has several targets; target 'locations': column 'Campus' writes relation 'campus_id' and needs a lookupThe checks cover unknown entities and fields, field types that can't be imported, duplicate headers,
matchOnfields without a column, lookups to unknown targets or fields, aMultiDropDownwithout a separator, more than one owned lookup, and lookups between targets that form a cycle. -
Saving is idempotent. An unchanged definition writes nothing. A definition removed from code becomes inactive, not deleted, because old jobs still refer to it. Inactive types can't be picked for new jobs.
-
Import types are read-only in the app. Change them in code.
Then open Settings → Data import → Import types. Your type is listed with its sheets and columns explained. Download template gives an empty XLSX with one sheet per target and the right headers, with required ones in bold.
Row hooks
For rules the column mapping can't express, add validateRow or transformRow. Both run for every row of every target, after the cells are converted and before lookups are resolved:
rowholds the row's values keyed by field key.- Lookup fields hold the natural key from the file (an array for a
MultiDropDown), not an id. ctxgivesparams,mode,rowNumber,sheet,raw(the cell text by header), anddbandservicesfor reads.
import {
ImportRowContext,
ImportRowIssue,
ImportRowValues,
ImportTarget,
} from '@april9au/stack9-sdk';
// Inside the import type class:
/** Codes are stored upper-case. */
transformRow(
target: ImportTarget,
row: ImportRowValues,
): ImportRowValues {
return typeof row.code === 'string'
? { ...row, code: row.code.toUpperCase() }
: row;
}
/** A course can't end before it starts. */
validateRow(
target: ImportTarget,
row: ImportRowValues,
ctx: ImportRowContext,
): ImportRowIssue[] {
if (target.key !== 'courses') return [];
const { start_date: start, end_date: end } = row;
return start instanceof Date && end instanceof Date && end < start
? [{ level: 'error', column: 'End date', message: 'is before the start date' }]
: [];
}
- An
errorissue blocks the import; awarningis only shown in the preview. - A hook that throws turns into an error on that row.
- What
transformRowreturns is converted and checked again like a cell. - Hooks run during validation and again when the job is applied, so keep them free of side effects. Anything a hook writes during validation is rolled back.
- Keep
transformRowdeterministic: the same row must give the same values every time. Apply re-plans and compares the result with the preview, so a hook that stamps the current time or a random value makes every Confirm end with "Data changed since the preview".
Who can import
- The screens live in the Settings app, so users need access to it.
- Creating a job, confirming it and seeing its rows follow the usual entity privileges on
import_jobandimport_job_item. - The import writes as the user who clicked Confirm, so the writes are allowed, refused, and recorded under their name, exactly as if they had made them by hand.
Limits and behaviour
- One transaction per apply. If any row is refused (by a hook, a validation rule or a permission), everything rolls back and the job ends Errored with the sheet, row and reason.
- Size. About 10,000 rows a minute locally, through the full entity service. The default
maxRowsof 10,000 keeps an apply around a minute. - One import at a time per table. Confirm is refused while another job is applying to any of the same entities.
- Interrupted jobs. If the server restarts mid-import, nothing is written, because the transaction rolls back. Within 15 minutes the sweeper moves the job to Errored. A job left Pending (never picked up) is swept the same way.
- Duplicates. Two rows with the same key in one file are both errors, and so is a key that matches more than one live record.
Checklist for an instance
- Stack9 6.0+ packages; the queue consumer runs, or locally
yarn dev --profile=<aws-profile> -
ImportJobStatus,ImportJobPanelandImportTypePanelregistered in the frontend - One class per import type in
src/import-types/, exported fromsrc/import-types/index.ts -
registerClass(..., importTypes)insrc/index.ts - Startup succeeds, and the type is listed under Settings → Data import → Import types
- The downloaded template, filled in with a few rows, reaches Ready with the expected preview, and Confirm completes