Importing and exporting with Excel

This page shows you how to create and update many Products, Customers or Vendors at once with an Excel spreadsheet, and how to download your lists as Excel files. It is for client users who manage Products, Customers or Vendors; anyone who can view them can use the exports.

Who can use this

• Importing (the Import from Excel button, the Download template link and the import pages) needs Manage Products, Manage Customers or Manage Vendors, depending on the list. A user with view access only does not see these buttons, and opening an import page shows You don't have access to this section.
• Exporting (Export and Export to Excel) is available to everyone who can view the list.

How to get there

Start on the list you want to work with: Master data > Products, Customers or Vendors. The import and export buttons are at the top of each list.

The two exports

Every list has two different ways to export. Choose by what you need.

The product workbook from Export to Excel includes discontinued Products, although the product list does not show them. It does not contain data that the import cannot carry (see the list of covered columns below), such as stock quantities, packsizes or components. The customer workbook does not contain delivery addresses.

In the product workbook, the three columns IsLotControlled, IsSerialControlled and IsExpiryControlled are written as the words True and False.

How an import works

The three imports follow the same steps and the same rules.

Step by step

1. On the list, click Download template (the small link under Import from Excel). Your browser downloads an empty workbook: Products-template.xlsx, Customers-template.xlsx or Vendors-template.xlsx. It has one sheet with one bold heading row and no data.
2. Open the file in Excel and type one record per row, starting at row 2 (see the column tables below). Keep the headings in row 1 exactly as they are. Save the file as an Excel workbook (.xlsx).
3. Back in the portal, click Import from Excel. The page Import Products, Import Customers or Import Vendors opens. A line under the title says what the import does, and a card lists the recognised columns.
4. Choose your file with the file picker.
5. Click Import. The button is not available until a file is chosen; if you reach it without a file, you see Choose a file first. While the file is processed the button reads Importing...
6. Read the result (see below). Click Back to Products, Back to Customers or Back to Vendors to return to the list, and use Refresh if you do not see the changes.

The Import Products page with a file chosen.

The result

When the import finishes the page shows four counters (rows, created, updated and skipped), a green message and, when there is something to report, a list headed Warnings.

• Import complete: {0} created, {1} updated. The import worked. {0} is the number of new records and {1} the number of records that already existed and were updated.
• Row {n}: missing Sku - skipped. (Customers: Row {n}: missing CustomerCode - skipped.; Vendors: Row {n}: missing VendorCode - skipped.) appears under Warnings for a row that has no key. {n} is the row number in your sheet, where the heading row is row 1. The row is counted as skipped and nothing is saved for it. These warnings appear in English.
• Import failed: {0} in red. The whole file was refused; {0} says why.
• Choose a file first. You pressed Import without choosing a file.

The result of an import: the four counters and, under Warnings, a row that was skipped.

Rules that apply to every import

• Only the first sheet is read. Put your data on the first sheet of the workbook. Row 1 must hold the column headings.
• Headings. Use the headings of the template. They are matched without regard to capital letters or spaces. A column whose heading is not recognised is ignored without any message.
• The key column decides create or update. The key is Sku for Products, CustomerCode for Customers and VendorCode for Vendors. The key is compared ignoring capital letters and spaces at the ends. If your company already has a record with that key, the row updates it. If not, the row creates a new record. If the same key appears on several rows, the first row creates the record and the later rows update it.
• A blank cell keeps the current value. For an existing record, a cell you leave empty changes nothing. This means you cannot empty a field by importing; to clear a value, edit the record on its page.
• A row without a key is skipped with the warning above. Other rows are still processed.
• One bad cell stops the whole file. If a number column holds text, or a TRUE / FALSE column holds anything other than a TRUE / FALSE value (for example the word Yes), the import fails with a message that starts Import failed: Row [n], Col [ColumnName]. Cannot convert [value] to [Decimal] (or [Boolean]) and may continue with technical text. Nothing is saved, including the good rows. Use the column name and the value in the message to find the cell, correct it and import again.
• Everything is saved at once. If the warehouse cannot save the changes, none of the file is saved.
• File size. A file larger than 15 MB is refused before it is sent, with Import failed: Supplied file with size {n} bytes exceeds the maximum of 15728640 bytes. There is no limit on the number of rows other than this file size; very large files take longer.
• Your own records only. An import only ever reads and changes records of your own company.

Importing Products

Columns of the product template

The page Import Products says: "Upload an Excel (.xlsx) file to create or update catalog items in bulk. Rows are matched to your catalog by SKU." The template has these columns, in this order. Only Sku is required.

The product import covers only these columns. It cannot set anything else on the product: subcategory, GTIN (UDI) and standard, bill of material, manufacture date, decimal and packsize controls, lot and serial patterns, shelf-life allowances, spatial and weight units, cartonization, hazardous material and freight codes, packsizes, components, minimum / maximum levels or the image. Make those changes on the product page. The import cannot discontinue a product, but it does find a discontinued product by its SKU and updates it.

Two more points about Products:

• Use TRUE or FALSE (not Yes or No, and not text such as "1") in the three control columns. A blank cell leaves the setting as it is; a new product created by import starts with all controls set to No.
• The import does not check whether the lot, serial and expiry settings fit the product's other settings. For example, a product that is packsize controlled cannot also be serial controlled on the product page, so do not set IsSerialControlled to TRUE for such a product.

Products that have stock

The lot, serial and expiry controls (and the unit of measure) cannot be changed on a product that has stock on hand or allocated, on screen or by import. The import can set the lot, serial and expiry controls and the unit of measure, so an import row that would change one of them for such a product is skipped. It is counted as skipped and listed under Warnings with the reason, in English, for example Row 3: Cannot change [Lot controlled] on [SKU-1] because it has stock: [10] on hand and [0] allocated. These settings describe how that stock was received. Move or consume the stock first, then change the setting. - skipped. The other rows still import, and a blank cell never counts as a change. Change a product's other columns freely; only these attributes are locked. See Products.

A worked example

You want to add two new Products and correct the description of a third, FLOUR-25, which already exists. The sheet is wide, so it is shown here in two parts that belong to the same rows. In the file, all 17 columns sit side by side in the order of the template. An empty cell is shown as an empty box.

Part 1, columns Sku to HtsCode:

Part 2, the same rows, columns Height to IsExpiryControlled (Sku is repeated so you can match the rows):

What happens when you import this file:

• OIL-500 and TSHIRT-M-BLU do not exist yet, so they are created.
• FLOUR-25 exists, so it is updated. Only its description changes, because every other cell in its row is blank and blank cells keep the current values.
• The page shows 3 rows, 2 created, 1 updated and 0 skipped, with the message Import complete: 2 created, 1 updated.

Importing Customers

The page Import Customers says: "Upload an Excel (.xlsx) file to create or update Customers in bulk. Rows are matched to your list by code." The template Customers-template.xlsx has one sheet, Customers, with these columns. Only CustomerCode is required.

The customer import never touches delivery (ship-to) addresses. It changes only the details and the Bill to fields. A customer created by import has no delivery addresses until you add them on the Addresses tab of its page, and existing addresses are never changed or removed by an import. The workbook from Export to Excel (Customers.xlsx, sheet Customers) has the same 15 columns.

Importing Vendors

The page Import Vendors says: "Upload an Excel (.xlsx) file to create or update Vendors in bulk. Rows are matched to your list by code." The template Vendors-template.xlsx has one sheet, Vendors, with these columns. Only VendorCode is required.

The workbook from Export to Excel (Vendors.xlsx, sheet Vendors) has the same 12 columns.

Messages you may see

Good to know

• You cannot clear a value by import. A blank cell always keeps what is there.
• A single wrong number or TRUE / FALSE cell stops the whole file. Nothing is saved until the file is clean.
• There are no warnings for a value that is accepted but looks odd, such as a typo in a category. Check the list after importing.
• Re-importing a file is safe for rows that did not change: existing keys are updated, not duplicated.

Related pages

• Products
• Customers
• Vendors
• Searching, filtering and exporting lists
• Master data