Supply Lens

How do I add or update lots of products at once from a spreadsheet?

Import Products in bulk

Export your Products to a CSV file, edit them in Excel, and import the file back to add or update Products, identifiers, prices and conversions in one go.

About 5 min

At the end of this guide you will have added or updated many Products at once from a single CSV file, and checked the import did what you expected.

Before you start

  • Add any units, Parties and Locations the file refers to first. A unit name in the file that doesn't match one of your units comes back for you to map rather than being guessed.
  • Decide whether the file is a partial update (only some columns or values) or the complete truth for the Products in it. This decides how you treat blank cells.

Steps

Export a template

  1. Open Settings → Reference data → Products.
  2. Export your Products to a CSV file, even if you only have a few. The export uses the same layout as the import, so it is your template.
  3. Choose the columns to include. Each group of columns has a tick box in its header to turn the whole group on or off. Your choice is remembered for next time.

Edit the file

  1. Open the file in Excel.
  2. Add rows for new Products, or change values for existing ones.
  3. Fill in the columns you need: identifiers (one column per type, and per scope where a code is scoped), prices and conversions.
  4. To say which unit an identifier stands for, such as a GTIN-14 for a case, use the uom column beside it, for example id gtin-14 uom.
  5. To set a Product's default unit for one Integration, use the Unit of Measure (integration: …) column.

Import it

  1. Back on the Products screen, open the product import and drag and drop your file onto it.
  2. Set any details the import asks for as you go, such as currency, country, tax code and unit of measure.
  3. Choose whether to ignore unrecognised columns. Turn this on if your file has extra columns of your own, such as notes, so the import skips them instead of failing.
  4. Choose whether a blank cell clears the existing value or leaves it unchanged. For partial updates, leave values unchanged.
  5. Run the import.

Blank cells can wipe data

If you import a file with only a few columns filled in and choose to clear blank values, you can remove identifiers or prices you meant to keep.

Good to know

An identifier uom column only sets the unit on an identifier the Product already has. It never creates an identifier: a unit for an identifier the Product doesn't have is skipped and counted in the result.

Check it worked

  • Read the import result. It tells you how many Products, identifiers, prices and conversions were changed, which units changed and how many cells were skipped.
  • Open two or three Products at random and check the values landed where you expected.

If something goes wrong

  • The import fails on a column it doesn't recognise. Turn on the option to ignore unrecognised columns, or remove the column.
  • Values you wanted to keep have gone. The import was set to clear blank cells. Re-import a full export taken before the change, or re-enter the values.
  • Cells were skipped. Usually a unit for an identifier the Product doesn't have. Add the identifier first, then import again.