Importing your product list from a spreadsheet without reshaping it

Most teams arrive at OpenBoxes with a product list in a spreadsheet: years of columns added by different people, a title at the top, a totals row at the bottom. The good news is that you do not have to rebuild it into our template before you import it. The Import Data wizard in the OpenBoxes Lift portal reads your headers and proposes how your columns map to OpenBoxes fields, and you check the mapping before anything is loaded.

The wizard cannot make an inconsistent sheet consistent, though. Ten minutes spent on the things below saves an afternoon of fixing records after the import. The step-by-step guide is in the docs, Migrating data, and the one-minute video How to Import Data shows a spreadsheet import from upload to products in OpenBoxes. This article is about getting the sheet ready.

What you can leave alone#

The importer is built for real exports, so a lot of spreadsheet mess needs no attention:

• Your column names and order. The wizard reads your headers and proposes a mapping; you correct any column it got wrong.
• A title block or notes above the header. The importer looks for the real header row and skips title rows above it. If it still picks the wrong row, delete the rows above your headers so they are on the first line.
• Totals rows. Rows labelled Total, Totals, Grand total or Subtotal are dropped rather than imported as products.
• Merged banner rows in Excel, such as a section heading merged across the sheet, are dropped too.
• The way Excel saved your CSV. Common Excel exports, including Windows-1252 encoding and semicolons between columns, are detected and read correctly.
• An instructions sheet at the front of a workbook. The importer uses the first sheet that has a header row and at least one row of data.

What to fix first#

One sheet, one list#

An Excel file imports one sheet at a time, and there is no choice of sheet: the importer takes the first sheet that has a header row and data. If your workbook has products on one sheet and stock on another, save each sheet as its own file (or move the one you want to the front) and import them separately, products before the stock that refers to them. A stock row whose product code is neither in OpenBoxes nor in the same import only gets a warning, so load products first. When your data is spread over several files, the order is categories, then locations, then products, then stock.

Every product has a name and a unique code#

A product name is required: a row without one is flagged as an error. Product codes should be unique across the file; a code that appears twice is flagged, and the duplicate is skipped.

If your list has no codes, make up a simple scheme before you import, such as MED-001 and SUP-001, rather than importing without them. Codes are how you and the importer tell two similar products apart later.

Duplicates and naming#

Look for the same product entered twice under slightly different names, for example "Paracetamol 500mg Tablet" and "PARACETAMOL TAB 500MG". Pick one convention and apply it across the file. An importer can only match what is written the same way.

One spelling per category and per unit#

The importer creates a category for each different category name in your file. So "Antibiotics", "antibiotics" and "Antibiotic" become three categories, not one, and a misspelling becomes an extra category rather than a question. Before you load, read the validation preview: it tells you how many categories the import will create and names the first few. The same care goes for units: settle on one spelling for each unit you use before you upload. Common spellings of the everyday units are recognised (each, pcs and pieces are all read as the same unit, as are box and boxes), but a unit you have written two ways, such as "Ampoule" and "Amp", will not be merged for you.

Numbers as numbers#

This is where most surprises come from:

• Quantities are whole numbers. A fractional quantity such as 2.5 is not imported as a count; it is set aside for you to fix.
• Words in a quantity column ("out of stock", "few", "see note") and spreadsheet errors (#REF!, #N/A) are not numbers. They are set aside for you to fix too, but you will get through the import faster if you clean them first.
• Keep pack sizes out of the quantity column. "2 x 50" or "3 boxes" belongs in a note or a unit column, with the count in the quantity column as a plain number.

Dates written one way#

Write expiry dates as YYYY-MM-DD, for example 2027-03-04. This matters more than it looks, for two reasons:

• A date like 03/04/2027 is read month first, as 4 March 2027, even if you meant 3 April. The importer does not ask.
• A date it cannot read at all is left empty, with no message, so a lot can arrive without its expiry date.

YYYY-MM-DD can only mean one day, and it is always read.

When something cannot be read#

Some problems stop the import and are parked as a question: a product with no name, a stock quantity that is not a whole number, or a movement type in a history file that the importer does not recognise. The import pauses, the account owner is emailed, and you answer each question in the portal, with suggested options. The import then carries on where it left off.

Other problems do not stop it. A duplicate product code is skipped with a warning, a new category spelling is created as a category, and a date that cannot be read is left empty. That is why the cleaning above is worth doing before you upload, not after.

Test small, back up, then check#

1. Import 10 to 20 products first, and look at them in OpenBoxes before you load the rest.
2. Take a backup before a big load: Backups, then Run backup now in the portal gives you a snapshot to go back to.
3. Spot-check after the full import. Open a handful of products in OpenBoxes and compare names, codes, categories and units with your sheet.

If you would rather start from a clean layout, the wizard offers four ready-made CSV templates, Products, Stock on hand, Locations and Stock movements, which map with no setup. The products template's columns are Product Code, Product Name, Product Type, Category, Unit of Measure, Price Per Unit, Manufacturer, Lot & Expiry Control and Active.

Summary#

• You do not need to reshape your sheet: the wizard proposes a column mapping, skips title and totals rows, and reads common Excel exports.
• Do clean the content: one list per sheet, a name and a unique code for every product, one spelling per category and unit, plain whole-number quantities and YYYY-MM-DD dates.
• Some problems are parked as a question; others (duplicate codes, new category spellings, unreadable dates) are not, so clean those first.
• Test with a small batch, back up before the big load, and spot-check afterwards.

Moving from a self-hosted OpenBoxes rather than a spreadsheet? Moving a self-hosted OpenBoxes to Lift covers that path. To see what the plans include, compare OpenBoxes Lift plans.