Skip to content

Imports

Format definitions for the XLSX import and XLSX export maintenances: which columns of an Excel file go into which register fields. Without a definition the maintenance does not know how to read the file — its "Import document" list shows exactly the records of this register.

Where to find it: Modules → System → Registers → Imports

Creating a definition

  1. A new record: Code — short and unique, Name — how the definition appears in the maintenance's "Import document" list.
  2. Register — where to import. As soon as you pick the register in a new record, the JSON field fills with a draft: all register fields with their labels, the key ones already marked "primary": true. Remove the extra columns and rename the headers if needed.
  3. The JSON field turns green when the text is valid JSON and red when it is not. A record with invalid JSON is not saved.

Document rows (tables inside a document) cannot be picked as a register — import does not write them.

Describing a column

The JSON is a list, one object per file column. Hints for the properties are shown right in the window above the JSON field.

[
    { "key": "fCode", "header": "Code", "primary": true },
    { "key": "fName", "header": "Name", "required": true },
    { "key": "fPhone", "header": "Phone" },
    { "key": "fCUType", "header": "Client", "default": "1" }
]
  • key — the technical field name in the register.
  • header — the column header in the first row of the file; must match literally.
  • primary — the record key (code, number). Updating finds an existing record by it, and the key value itself is not changed. A definition needs at least one such field.
  • required — a cell in this column cannot be empty.
  • default — the value put into an empty cell.

The same definition sets the columns of XLSX export too: export, fix in Excel, load back.

What a definition cannot contain

The definition is checked before the file is read, and the whole file is refused if it has:

  • a column that is not among the register fields;
  • a column that posts a document (the "OK" flag or a posting status) — import writes directly, without stock movement or postings;
  • a protected field (passwords, tokens) — it is entered only in the window;
  • a field the user's role cannot see or may only view.

Common situations

The maintenance's "Import document" list is empty. There is no definition for this register — create one here.

A column of the definition is not a field of the register. There is a typo in key — check it against the register fields, or take a fresh draft by picking the register in a new record.

"The import document must have at least one primary field". Mark the key column (code or number) "primary": true.

"No record found for the key: …". You are updating records, and there is no record with that key. New records are loaded with the "Create new registers" type.

"Column "…" is required, but cell … is empty". The file has an empty cell in a "required": true column — fill it in or set a default.