Moving inventory off spreadsheets

The spreadsheet is the most common inventory system in the world, and it is usually the last one that ever fit the business exactly. Replacing it is not hard — but almost nothing that makes it hard is in the software. It is in the sheet.

1.Why this is different from leaving an ERP

Every other migration guide on this site starts with an export screen: the old system has a schema, and the job is finding the button that hands it over. A spreadsheet has no schema. Nothing has ever stopped you writing ~40 in a quantity cell, using the same code twice, or keeping three items on one row because they arrive together.

That flexibility is the entire reason the sheet lasted this long, and it is the entire reason the move takes a pass of work. Software will not accept ambiguity, so somebody has to resolve it — and the only person who can is whoever maintains the sheet. Not a consultant, and not us. That is the trade, stated plainly.

If you actually do have an ERP underneath the spreadsheets, start from the migration guides instead — the extract is a better starting point than a re-typed sheet.

2.Decide what one row means

Do this before you touch a single cell, because everything else depends on it. Software needs exactly one row per item. Working spreadsheets are very often something else:

• A row per purchase — the sheet grew as a receiving log, so the same part appears eleven times with eleven costs. Collapse to one row, and take the most recent cost rather than the first.
• A row per location or bin — the same part in three places is three rows. Decide now whether those become one opening balance or separate stock locations; this is the one decision on the page that is genuinely awkward to change later.
• A row per count — a tab per month, and the current position is the last tab. Pick the tab that is true, and treat the rest as archive.

Variants are the other grain question. If you stock a shirt in four sizes, four rows with four codes is right; one row saying "S/M/L/XL" cannot be counted, ordered, or scanned.

3.The clean-up pass

Six things, in roughly the order they cost you time if you skip them.

Every row needs a code, and it has to be unique

The code — SKU, part number, whatever you call it — is the identity of the item. It is what an import matches on, which means it is also what makes a second import update your catalogue rather than duplicate it. If your sheet identifies items only by description, you are inventing codes now; short and mnemonic beats clever.

Case will not save you. Matching is case-insensitive, so WIDGET-1 and widget-1 are the same item, not two. Sheets that grew over years are full of that pattern, and it is the single most common reason a row silently does not arrive as its own item.

Quantity and cost have to be numbers

~40, 40 ea, 40?, 40 (check) and an empty cell are all things a spreadsheet is perfectly happy with and an importer is not. Sort each numeric column and look at both ends — the junk collects there. If a quantity is genuinely unknown, decide whether it is zero or whether that item needs counting before go-live; do not import a guess and let it become a fact.

If your columns are formulas, paste them back as values first. You are exporting the answers, not the arithmetic.

Check that your cost column is a cost

This is the one to get right, because it is the one you cannot quietly correct later. AegisSwift costs inventory on a moving average, and the cost you import becomes the opening basis for every margin figure afterwards. What you want is a current average or last purchase cost. What is often sitting in a column labelled "cost" is a retail price, and importing that produces margins that look plausible and are wrong.

If nobody can produce a trustworthy cost for an item, leaving the cost blank deliberately is better than importing a number you do not believe.

Long numbers get destroyed on the way out

Barcodes and UPCs of twelve digits and up will render in scientific notation and lose their tail if the file passes through a default CSV open. Do not double-click a CSV to open it — import it and set those columns to text first, or open it in LibreOffice, which does not do this. Check one long barcode by eye before you import; it takes five seconds and it is not recoverable afterwards.

Trailing spaces

"WIDGET-1 " is not "WIDGET-1". Years of copy-paste leave whitespace on the ends of codes and names, and it turns duplicate detection and vendor matching into near-misses. Trim every text column. It is one formula and it saves a re-import.

Customers and suppliers are a second file

They import separately from items, with a party_type column marking each row customer or vendor. The thing worth knowing in advance: addresses arrive as one free-text field each, billing and shipping. Almost every sheet keeps street, city, state and postcode in separate columns, so join them into a single cell before importing rather than picking one column and losing the rest.

4.Pin the cutover

On-hand quantities are only true at an instant. Export stock on Monday, go live on Friday, and you have imported a week-old picture — after which every discrepancy anyone finds gets blamed on the new software.

Agree a freeze point with everyone who edits the sheet. Then either have the team work on paper between the freeze and go-live and key the gap in, or accept the drift and reconcile with a full count on day one. Both are fine. Drifting silently by a week is not.

A spreadsheet makes this harder than an ERP does in one specific way: there is usually no single moment when the file is authoritative, because two people have copies. Decide which copy is the one being migrated, and say so out loud.

5.What breaks if you skip the pass

None of these announce themselves. That is why they are worth the afternoon.

6.What happens on our side

The import wizards take Excel (.xlsx / .xls) or CSV with a header row, one record per row. They auto-match the obvious column headers to fields, show you the mapping with sample values from your own file, and write nothing until you confirm it. There are four of them — items, customers & suppliers, stock counts, and bills of materials — and you run one file at a time.

Order matters: suppliers and customers first, then items.

Import twice on purpose. The first run tells you what your file actually contains; matching is on the item code, so a second run updates rather than duplicates.

If the sheet is too far gone to fix by hand, there is an AI-assisted import that reads files in whatever shape they are in — including several at once, and including PDFs and photographs of printed pages — correlates them, and proposes rows in the right shape. Nothing is written until you review it. It is metered against a per-company budget, so think of it as the tool for the awkward files rather than for the whole catalogue.

One accounting note if you are moving from a sheet that fed your bookkeeping: we cost inventory on a moving average, which will not tie out penny-for-penny with FIFO. It is a method difference rather than an error, and it is worth ten minutes with your accountant before go-live. If your books live in QuickBooks, connect it rather than exporting from it — the QuickBooks Desktop guide covers the Web Connector setup.

7.Questions people ask

How long does it take to move inventory from a spreadsheet into software?

The upload itself is minutes. The clean-up pass is the real work, and for a few hundred items it is usually an afternoon. Expect to import twice — once to see what the file does, and once for real.

Do I have to clean up my spreadsheet before importing it?

Some of it, yes, and it is worth doing deliberately rather than discovering it later. A spreadsheet has never been forced to have one row per item, a unique code on every row, or a quantity that is a number rather than text. Those three are what an import actually requires. If the sheet is too far from that shape to fix by hand, an AI-assisted import can read it as it stands and propose rows for you to review.

Will I lose my inventory history when I leave spreadsheets?

You carry over balances, not history. What you import is an opening position — items, quantities and costs as of your cutover — and the new system starts its own transaction ledger from there. Keep the old workbook as your archive; do not try to replay years of edits into a new system.

What if my inventory is spread across several spreadsheets?

That is normal, and it is a decision rather than a problem: pick which sheet is authoritative for each column before merging, because reconciling two disagreeing quantity columns is a stock-count question and not a migration one. Where the files genuinely hold different facts — quantities in one, prices in another — an AI-assisted import can correlate several documents into one proposed import.

Do I need a consultant to move off spreadsheets?

No. Nothing on this page needs anyone but the person who already maintains the sheet, and that person is the only one who knows what its columns actually mean. The import wizards accept Excel or CSV with a header row, auto-match the obvious columns, and show you the mapping before anything is written.

Can I keep using the spreadsheet alongside?

For a week or two of overlap, sure. Beyond that it stops being a safety net and becomes a second system to reconcile, which is the problem you were leaving.