How to prepare a spreadsheet to import into inventory software
The spreadsheet you have used for years has history. It also has a title in row one, a merged cell in column C, a total at the bottom that someone forgot, and a note in the margin that says 'ask Bob.' Software will not ask Bob. It will read every one of those things as data.
Moving to inventory software is mostly an exercise in cleaning that sheet. The import itself takes seconds. The cleanup is the real work, and it is worth doing well, because everything you load becomes the foundation of the system.
One row per item
Imports go wrong when the sheet is built for people instead of for a program. Merged cells, subtotals and marginal notes all get read as data. Aim for a plain table: one header row, then one row per item, with nothing else on the sheet.
- 1CleanOne row per item, no totals
- 2Match columnsUse the template headings
- 3Choose a modeReplace, add or skip
- 4Check previewFix flagged rows
- 5Load and countTrust the count, not the sheet
Most of the effort goes into the first step.
The columns that matter
VectisERP's CSV template uses these columns, among others. You do not need all of them to start.
- sku, barcode and item_name to identify the item.
- category and unit_of_measure so items can be grouped and counted properly.
- location and bin, if you track where it lives.
- on_hand, reorder_point and reorder_qty for quantities.
- supplier, supplier_sku, unit_cost and lead_time_days for buying.
- is_batch_tracked, lot_number and expiration_date for items with lots.
- last_counted, so the cycle count list knows when each item was last counted.
- skuYour code for the item
- item_nameWhat a person would call it
- unit_of_measureEach, box, gallon
- on_handBecomes the opening quantity
- reorder_pointNice to have
- supplier and unit_costNice to have
Start with the first four and add the rest once the routine feels natural.
Clean it first
- Remove duplicate SKUs. Matching ignores case, so Hammer-1 and HAMMER-1 are the same item.
- Format the barcode column as text so leading zeros survive.
- Use one unit of measure per item and spell it the same way every time.
- Delete blank rows and any totals at the bottom.
- Save as CSV.
Check the preview
VectisERP shows a preview of the file and lists the rows with problems before anything is saved, so you can fix them in the sheet and upload again. A single import is currently limited to 1 MB and 5,000 rows, so split a larger catalog into several files.
Opening stock and re-imports
On a first load the quantity in the file becomes the on-hand number. On a re-import, an SKU or barcode that already exists is updated, and you choose whether the file replaces the existing values, adds its quantities to current stock, or skips items that already exist.
Each import is logged as a master data load, not as a goods receipt. That means the quantity you loaded was never counted by anyone. Count after loading, and treat that count as the first figure you actually trust.
An import moves data. Only a count tells you it is true.
Replace
The file overwrites existing values
Add
File quantities are added to current stock
Skip
Items that already exist are left alone
A realistic timeline
For a few hundred items, here is a realistic plan. Half a day to clean the sheet. Ten minutes to upload and read the preview. An hour to fix flagged rows and upload again. Then a count of your top items, which is the part people leave out and the part that matters.
It is tempting to load everything on day one. A calmer way is to start with your top fifty items, work with them for a week, and load the rest once the routine feels natural. A small, correct list is much more useful than a large, doubtful one.
Whatever you do, keep the original spreadsheet. Save a copy with the date in its name. If something goes wrong, you can always start again.