Data import
Import items and categories from Excel
Load your category tree and your item master from Excel templates, including each item's prices, units and barcodes, before you record any stock.
3 min readLast updated
In this article
Import your categories first, then your items. Each item points to a category by its code, so the categories must exist before the items arrive. Neither import adds stock: quantities come later from purchases, opening balances or the quantity import.
Before you start
Create the taxes, brands, colours, item types and units your items use. The item sheet refers to them by their ID number.
Import the categories
Open the screen by adding
/s/categories.bulk-createto your workspace address. It has no menu entry.Select Download template and fill in one row per category.
Select Choose file, pick the Excel (.xlsx) or CSV file of up to 1 MB, and select Upload.
The category sheet can hold a whole two-level tree:
Column | What to enter |
|---|---|
REFERENCE_ID | Your own number for the row, used to link children to their parent. |
NAME | The category name. Required. |
CODE | A short code, unique in your company. The item sheet uses it. |
DESCRIPTION | Free text. |
CATEGORY_TYPE | 0 for a main category. Leave empty for a sub-category. |
REFERENCE_PARENT | For a sub-category in the same file: the REFERENCE_ID of its main category. |
PARENT_ID | For a sub-category under a category that already exists: that category's ID. |
Main categories are created first and their sub-categories are attached to them. If a main category fails, for example because its code already exists, its sub-categories fail with it.
Import the items
Open the screen by adding
/s/items.bulk_createto your workspace address.Select Download template. The workbook has two sheets that must keep their names: items and item_units.
On the items sheet, fill in one row per item. On the item_units sheet, list each item's units, linked to the item by the same REFERENCE_ID.
Select Choose file, pick the Excel (.xlsx) file of up to 1 MB, and select Upload. The screen checks the two sheet names before it uploads.
The items sheet
REFERENCE_ID: your number for the item, repeated on its unit rows.
ITEM_NAME, SKU and ITEM_TYPE_ID: required.
MAIN_CATEGORY_CODE and SUB_CATEGORY_CODE: the category codes from your category import.
COST_PRICE, RETAIL_PRICE and WHOLESALE_PRICE: required, 0 or more.
DISCOUNT: a percentage from 0 to 100. REORDER_LEVEL: the quantity that triggers a low-stock alert. Both required.
DESCRIPTION, BRAND_ID, COLOR_ID and TAX_ID: optional.
The template's third sheet, legends, lists the item types with their IDs.
The item_units sheet
One row per unit of an item: REFERENCE_ID, ITEM_UNIT_ID, QUANTITY (how many base units it contains), SERIAL_NO (the barcode, which must be unique across the system) and DEFAULT for the unit to select by default.
What happens next
Each item is created with its units and barcodes, an opening entry in its history, and a zero balance in your current branch. Add the stock through a purchase invoice or the quantity import.
Common questions
"The workbook must contain both an items sheet and an item_units sheet." A sheet was renamed or removed. Download the template again and copy your data into it.
A barcode was rejected. SERIAL_NO must be unique. Another item, or another row in the file, already uses it.