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 — required and unique within the register: this is 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. In the "Ignore empty" update an empty cell does not change the field, and default does not apply.

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.