Skip to main content

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​

  1. 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.
  2. 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.
  3. 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.
  4. 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.

ModeRecords in the fileRecords not in the file
IncrementalCreated or updatedLeft alone
Replace allCreated or updatedSoft-deleted
Delete onlySoft-deletedLeft alone
Replace all deletes

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-module and stack9-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 long queue, and an apply can take several minutes. Set the long queue's visibility timeout to 1800 seconds, with the app variable APP_MESSAGE_QUEUE_VISIBILITY_TIMEOUT_LONG or the environment variable MESSAGE_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, ImportJobPanel and ImportTypePanel. Instances list core components in apps/stack9-frontend/src/_app.core.module.tsx, which stack9 upgrade generates from the instance template. If your generated file doesn't list them yet, add them as instance components in app.stack9.instance.tsx until it does:

    apps/stack9-frontend/src/app.stack9.instance.tsx
    import {
    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:

packages/stack9-stack/src/import-types/campusLocationsImport.ts
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​

PropertyRequiredWhat it does
keyYesStable id. Jobs and the import_type row are matched on it, so don't rename it once it has jobs.
nameYesWhat users pick from.
descriptionNoShown on the Import types screen.
modesYesThe modes offered: any of 'Incremental', 'Replace all', 'Delete only'.
maxRowsNoLargest number of data rows in a file, across all sheets. Defaults to 10,000.
targetsYesThe entities the file writes, one per sheet (below).
paramsSchemaNoParses 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.
validateRowNoExtra row checks (see Row hooks).
transformRowNoRewrites a row before it's checked (see Row hooks).

Target properties​

PropertyRequiredWhat it does
keyYesId of the target, used by lookups and shown on each preview row.
entityKeyYesThe entity written to.
sheetWith several targetsThe sheet the target reads, matched ignoring case.
matchOnYesThe 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.
columnsYesThe 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​

PropertyRequiredWhat it does
headerYesThe header text in the file, matched ignoring case and extra spaces.
fieldYesThe entity field it fills.
requiredNoThe header must be present and every row must have a value.
lookupFor relation fieldsHow 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 typeAccepted in the file
TextField, RichTextEditor, ColorPickerFieldText, trimmed.
NumericFieldA number. With precision 0 it must be whole; otherwise it's rounded to the precision.
DateFieldYYYY-MM-DD, an ISO date-time, DD/MM/YYYY, or an Excel date cell.
Checkboxtrue/yes/y/1 or false/no/n/0, any case.
OptionSetOne of the field's values, any case.
SingleDropDown, MultiDropDownA 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' }
  • matchOn must be a plain field (text, number, date, checkbox or option set) of the record looked up.
  • A MultiDropDown needs a separator, 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:

packages/stack9-stack/src/import-types/index.ts
export * from './campusLocationsImport';

Then pass that module to registerClass in the instance's entry point, next to whatever it already registers:

packages/stack9-stack/src/index.ts
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 lookup

    The checks cover unknown entities and fields, field types that can't be imported, duplicate headers, matchOn fields without a column, lookups to unknown targets or fields, a MultiDropDown without 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:

  • row holds 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.
  • ctx gives params, mode, rowNumber, sheet, raw (the cell text by header), and db and services for 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 error issue blocks the import; a warning is only shown in the preview.
  • A hook that throws turns into an error on that row.
  • What transformRow returns 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 transformRow deterministic: 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_job and import_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 maxRows of 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, ImportJobPanel and ImportTypePanel registered in the frontend
  • One class per import type in src/import-types/, exported from src/import-types/index.ts
  • registerClass(..., importTypes) in src/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