Bulk import & handlers

A table view can declare an <ImportConfig> to accept uploaded CSV/XLSX data. The toolbar then shows an Import button (beside Export) that opens a dialog: it lists the expected columns, offers a downloadable template (XLSX or CSV with the right headers), takes the upload, and shows a live progress checklist (Uploaded → Processing data → Saving records → Completed) driven over SignalR.

The two built-in importers

<!-- Per-row INSERT (QueryImporter): each file row binds @columns -->
<ImportConfig>
  <BeforeSql>/* optional setup, supports {GUID} for temp tables */</BeforeSql>
  <ImportSql>
    INSERT INTO POS.Customers (Number, FirstName, Email, CreatedById)
    VALUES (@Number, @FirstName, @Email, @SessionUserId);
  </ImportSql>
  <AfterSql>/* optional post-processing */</AfterSql>
</ImportConfig>

<!-- Bulk insert (BulkCopyImporter): SqlBulkCopy on SQL Server, binary COPY on PostgreSQL.
     TargetTable may be a real typed table on either dialect — see "BulkCopy on SQL Server vs PostgreSQL". -->
<ImportConfig>
  <BulkCopy TargetTable="POS.Suppliers" Columns="Number,Name,CreatedById,CreatedAt,CompanyId,IsDeleted">
    <Expression Column="CreatedById" Expression="@SessionUserId" />
    <Expression Column="CompanyId"   Expression="@SessionCompanyId" />
    <Expression Column="CreatedAt"   Expression="SYSDATETIME()" />
    <Expression Column="IsDeleted"   Expression="0" />
  </BulkCopy>
</ImportConfig>

BulkCopy on SQL Server vs PostgreSQL

<BulkCopy> works the same way on both dialects — SqlBulkCopy on SQL Server, a streamed binary COPY … FROM STDIN on PostgreSQL — and you can point TargetTable straight at a real typed table on either. The engine handles the two differences that would otherwise make PostgreSQL fail:

• Values are converted to the destination’s types. The file parser reads every column as text, and binary COPY (unlike SqlBulkCopy) decodes each field with the destination column’s binary format. The engine reads the destination schema before copying and converts each cell, so numeric, boolean, timestamptz, uuid, date/time columns all take plain text from the file.
• Identifier case is resolved for you. PostgreSQL folds unquoted identifiers to lower case, so a "CreatedAt" column can’t be addressed as CreatedAt. The Columns you declare are matched against the identifiers the destination actually has, so the same Columns="…" list works against a quoted-PascalCase engine table and a lower-cased staging table alike.

Conversion rules

These apply to the PostgreSQL path (SQL Server delegates to SqlBulkCopy, which is close but not identical — see the note below):

Cell	Result
empty / whitespace	NULL — for a text column it stays an empty string, since '' is valid for NOT NULL
1 true yes y on t (any case)	boolean true; 0 false no n off f → false. Anything else is an error, not false
2026-07-29 14:30 into timestamptz	read as UTC (matches SQL Server’s +00:00 for the same string, so a file imports identically on both)
2026-07-29T14:30+05:00 into timestamptz	offset honoured, normalised to UTC
2026-07-29 14:30 into timestamp	kept as a wall clock, no zone attached
1,234.56	accepted (grouped thousands, as Excel exports)
1,5	error — an ambiguous comma is refused rather than silently read as 15
unparseable non-blank value	error naming the row, column, value and expected type

Conversion is deliberately strict: a bad cell fails the import (which rolls back) instead of becoming NULL, because silently dropping a value is the failure nobody notices. Opt into forgiving behaviour per column with Convert(type) — it yields NULL for anything unparseable, like TRY_CAST.

Staging tables are dialect-specific

Staging is now an optimisation (batching a MERGE, deduplicating), not a workaround for typing — copy straight into the real table if that’s all you needed.

If you do stage, the DDL in <BeforeSql> is yours and <BeforeSql> has no per-dialect variant, so one view cannot carry both CREATE TABLE ##Staging_{GUID} (SQL Server global temp) and PostgreSQL’s CREATE TEMP TABLE Staging_{GUID}. For a view that must run on both, either target the real table directly, or create an ordinary table named with {GUID} and DROP it in <AfterSql> — that DDL is dialect-neutral. Note also that CREATE TEMP TABLE Staging_x (unquoted) creates the column identifiers in lower case; the engine matches your declared Columns case-insensitively, so that resolves either way.

BulkCopy <Expression> grammar

BulkCopy <Expression> values are a small server-side function grammar, evaluated per row before the copy and resolved to typed values (so they store into bit/bigint/datetime columns cleanly — a bare string can’t convert to a bit). The element is <Expression Column="…" Expression="…"/> (a child of <BulkCopy>; note it is <Expression>, not <Column>). Supported:

• @Session… parameters — any session parameter (@SessionUserId, @SessionCompanyId, @SessionCartId, plus host-registered ones). Usable standalone or as a function argument. Unknown → NULL.
• @Guid[N] — N uppercase-hex characters (a fresh GUID per row; N clamped 1–32). Handy as a uniqueness key inside Sequence(...).
• Functions (case-insensitive):
  • Sequence(format, objectName [, company [, key]]) — builds the sequence token the DB trigger expands, {objectName}|{format}|{company}|{key}. company defaults to @SessionCompanyId, key to @Guid[7]. format must contain # — #N zero-pads to N digits, and a bare # defaults to width 2 (SKU-#2 → SKU-01). If the file already provides a value for the column it is kept (a hand-entered SKU wins). E.g. Sequence('SKU-#2','Product','@SessionCompanyId','@Guid[7]').
  • Convert(type) — coerce the column’s value to int|long|decimal|double|bool|datetime; blank → NULL.
  • TRIM(), NULLIF_TRIM() / NULLIF_TRIM('sentinel') — trim the column’s own value (empty/sentinel → NULL).
  • SYSDATETIME() / GETDATE() — now, as a UTC timestamp; SYSDATETIMEOFFSET() — now, as a UTC-offset value for datetimeoffset / timestamptz columns. NEWID() — a GUID string. All three are evaluated in the engine, not by the database, so the names are just familiar spellings and they behave identically on both dialects.
• Literals — anything else is a constant, parsed to its narrowest type (0/1 → integer, 1.5 → decimal, true/false → boolean, else string). Use a literal for audit/tenant defaults the uploader shouldn’t supply, e.g. Expression="0" for a bit IsDeleted.

Function argument resolution

Function args are resolved uniformly (unquote → @-token → literal), so @SessionCompanyId / @Guid[7] work inside any argument. New functions are added in ImportExpressionEvaluator (one registry entry). No part of a Sequence token may contain a literal |.

Template & required columns

Template columns are derived server-side (the import SQL is stripped from client metadata): for a QueryImporter, the ImportSql @parameters (framework-owned @Session*/@Parent__/@Form__ and any DECLARE @local variables are excluded — so you can pre-cook values in the batch without them showing up as columns); for a BulkCopy, Columns minus the <Expression> columns. Each column’s type/required hint comes from the matching EditorField.

Required vs optional columns. A file must contain every column that backs a required editor field; any other column is optional — if the file omits it, a QueryImporter binds the @parameter as NULL, and a BulkCopy pads the missing column to an empty (NULL) column before the copy. So optional or framework-generated columns may be left out. This is how a <Sequence> import works: leave the sequence column out of the file and pre-cook it (ISNULL(NULLIF(@Sku,''), @SkuFormat)), so it’s auto-assigned when absent but kept when the row provides one. The whole import (BeforeSql → rows → AfterSql) runs in one transaction.

Staging pattern (BeforeSql/AfterSql)

For a robust bulk load, bulk-copy into a per-import temp table in BeforeSql, then AfterSql casts/cleans and inserts (or MERGE-upserts) into the real table and drops the temp — all in one transaction. Use the {GUID} placeholder (replaced everywhere) to make the temp name unique, and TRIM()/NULLIF_TRIM() (or plain LTRIM(RTRIM()) in AfterSql) to clean text. Worked example: sample/Inventory/models/views/Products.view.xml (insert-only, with a commented MERGE seam).

Live progress

The dialog shows four phases over the ImportProgress hub: Uploaded (client upload %), Processing (Expressions + BeforeSql), Saving (BulkCopy / MERGE / ImportSql), Completed (after AfterSql). A failed step is reported with an error status.

Callbacks from <AfterSql> — starting workflows per imported row

<AfterSql> may return a callback envelope: a result set whose first column is FunctionName, the same contract as any other dispatch callback. This is how an import starts a workflow — and because the envelope is a list of rows, a set-based SELECT starts one instance per imported record, each with its own context. There is no batch endpoint and nothing to configure.

The staging table still holds the natural key at that point, so joining back to it identifies exactly what this import wrote — no batch column, no schema change:

-- …after the INSERT…SELECT that populates the real table, and BEFORE the DROP:
SELECT 'startWorkflow' FunctionName,
       'ProductApproval', 'Products', p.Id,   -- workflow, entity type, entity id
       p.Sku, p.Name                          -- trailing columns become that instance's context
FROM [Inventory].[Products] p
JOIN ##ProductImport_{GUID} t ON t.[Sku] = p.[Sku]
WHERE p.CompanyId = @SessionCompanyId AND p.IsDeleted = 0;

DROP TABLE ##ProductImport_{GUID};

Rules worth knowing:

Which result set	The first one shaped like an envelope. Others are read and discarded, so an AfterSql that also selects diagnostics still works.
Ordering	The SELECT must come before the DROP TABLE that ends a staging AfterSql.
When they run	After the transaction commits — a workflow never sees rows a late rollback would have removed. The flip side: a callback cannot roll the import back. If AfterSql itself fails, the import rolls back and no callback runs.
Context	Comes from the trailing columns only. Unlike a form submit — which layers the submitted fields underneath — an import request carries almost no parameters, so project whatever the workflow reads (p.Sku, p.Name) as aliased columns. Unaliased columns are skipped.
Re-importing	Starts nothing new: a record that already has an Active instance is skipped.
UI callbacks	Work too — SELECT 'showSuccess' FunctionName, 'Imported', '3 products queued.' shows in the import dialog.

See Starting & advancing workflows for the full callback reference (startWorkflow, transitionEntityWorkflow, transitionWorkflow, workflowSubmitApproval).

Upload size

The import endpoint accepts up to 1 GiB by default; override with Genie:Uploads:MaxImportBytes (bytes) in appsettings — it caps both the request body and the multipart form length. A directory import may upload up to 4096 files by default; override with Genie:Uploads:MaxImportFileCount.

Custom import handlers (any file type — XML, ZIP, …)

<ImportConfig> only handles CSV/XLSX. To import any other file type (XML, ZIP, JSON, a proprietary format), a host registers a custom import handler — the import mirror of the custom export handler. The handler receives the raw uploaded file(s) and does its own parsing, running entirely separately from (and instead of) the built-in <ImportConfig> path.

One importer per view. A view has a single import path — either the built-in CSV/XLSX importer or a custom handler. A view opts into a handler declaratively by naming it on the view: <ImportConfig Handler="WarehousesXml">. The import toolbar is therefore a plain Import button (no dropdown), and the engine resolves the handler server-side — the client never names a C# type.

public sealed class WarehouseXmlImportHandler(GenieContext ctx, ISessionCompanyService company)
    : ICustomImportHandler
{
    public string TableName => "Warehouses";          // the Genie view this handler applies to
    public string HandlerName => "WarehousesXml";      // matched against <ImportConfig Handler="…">
    public string DisplayName => "Import XML / ZIP";   // label shown for the import
    public string AcceptedFileTypes => ".xml,.zip";    // drives the client file picker's accept filter

    public async Task<ObjectActionResult> ImportAsync(
        ImportTableRequest request, IReadOnlyList<IFormFile> files, IImportProgress progress, CancellationToken ct)
    {
        await progress.Notify("Parsing", ImportStepStatus.Running);
        // …parse files[0] (single-file import) — or every file for a Files/Directory import…
        await progress.Notify("Parsing", ImportStepStatus.Completed);
        // …persist, then return new ObjectActionResult { Result = "Imported N rows." };
    }
}

The handler always receives a list of files: exactly one entry (files[0]) for a single-file import, or all selected files for a Files/Directory import (see Multi-file & directory imports below). For a directory import each file’s FileName carries its relative path within the chosen folder.

Reporting progress. A custom import can be slow, so the handler is handed an IImportProgress to report steps at runtime: await progress.Notify(label, ImportStepStatus.Running) when a step begins and ImportStepStatus.Completed (or Failed, with an optional detail string) when it ends. Each distinct label appears as a row in the import dialog’s live step list, appended in the order first seen and updated in place — so the user sees exactly which step is running. (The built-in CSV/XLSX import reports its own fixed phases and ignores this.)

Register it in the host: services.AddGenieImportHandler<WarehouseXmlImportHandler>(); (additive, like AddGenieExportHandler). Worked example: sample/Inventory/api/Import/WarehouseXmlImportHandler.cs + sample/Inventory/models/views/Warehouses.view.xml.

Built-in Wizard/Workflow imports

The engine ships two import handlers out of the box, the inverse of the wizard export: the Wizard Definitions and Workflow Definitions admin views accept a definition XML file (one definition per file) and create or update it (upsert by name, including a new version snapshot), reporting per-step progress as they go. No wiring needed — they’re registered by AddGenie and declared on those views.

Declaring the handler + accept on the view

A view wires its import on <ImportConfig> with these attributes:

<!-- Handler-based import: no <ImportSql>/<BulkCopy>; the named handler is the view's importer -->
<ImportConfig Accept=".xml,.zip" Handler="WarehousesXml" />

Attribute	Effect
Accept	The file-picker accept for this view’s import (comma-separated, e.g. .xml,.zip). View-level; empty ⇒ .csv,.xlsx. Also valid on a built-in <ImportConfig> to restrict its picker.
Handler	The HandlerName of a registered ICustomImportHandler — the view’s importer.
Select	File (default), Files, or Directory. Files lets the picker select multiple files; Directory chooses a folder (all files uploaded recursively). Both hand every file to the handler and require a Handler (parse error otherwise).

Handler and a built-in importer (<ImportSql>/<BulkCopy>) are mutually exclusive (a parse error if both). A Handler-only <ImportConfig> has no template. The engine resolves the importer from ImportConfig.Handler (matching a registered handler’s HandlerName, scoped to the view); if the named handler isn’t registered the import fails loudly. Accept precedence for the picker: ImportConfig.Accept → the handler’s AcceptedFileTypes → .csv,.xlsx. Worked example: sample/Inventory/models/views/Warehouses.view.xml + sample/Inventory/api/Import/WarehouseXmlImportHandler.cs.

Multi-file & directory imports (Select)

Set Select="Files" (multiple individually-picked files) or Select="Directory" (a whole folder) when a handler consumes many files at once — a batch of XML/JSON documents, a folder of images, an exported dataset. The framework Wizard/Workflow imports use Files (upload several definition XMLs at once).

<!-- Multiple files: the picker allows selecting several files at once -->
<ImportConfig Accept=".xml" Handler="WizardDefinitionsXml" Select="Files" />

<!-- Directory: the picker chooses a folder; every file in it is uploaded recursively -->
<ImportConfig Accept=".xml,.zip" Handler="WarehousesXml" Select="Directory" />

• Files lets the dialog’s picker select multiple files; Directory selects a folder and uploads every file it contains, recursively.
• The handler’s ImportAsync receives them all in its files list. For a directory import each file’s FileName is its relative path within the chosen folder (e.g. 2024/east/warehouses.xml), so a handler can use the folder structure if it needs to.
• Files/Directory are only valid with a Handler — the built-in CSV/XLSX importers take a single tabular file, so multi-file selection without a Handler is a parse error.
• The Accept types still filter which files the picker surfaces inside the folder; a handler should also skip files it doesn’t recognise (the sample handler ignores non-.xml/.zip entries).

The number of files a single import may upload is capped by Genie:Uploads:MaxImportFileCount (default 4096). Worked example: sample/Inventory/models/views/Warehouses.view.xml + sample/Inventory/api/Import/WarehouseXmlImportHandler.cs (upload the sibling sample/Inventory/sample-data/warehouses/ folder).