Why a CSV import breaks on real spreadsheet data — duplicates, SKU typos, drifted stock — and the profile → validate → quarantine migration that accounts for every row.

The Problem

Every small business that outgrew its spreadsheets hits the same wall. It is not the software. It is the sentence:

“We’d move to a real system, but our data is five years of messy sheets.”

The fear is rational. The sheets are the business: orders, customers, stock, suppliers, prices. A migration that silently drops rows or mangles history is worse than no migration — at least the sheets, while painful, are trusted.

Here is what that mess actually looks like when you profile it. The numbers below come from one deliberately messy but realistic demo dataset — a specialty coffee wholesaler with 18 products, 35 variants, 10 suppliers, ~100 customer rows and 300 orders¹. Scale that shape up five years and a dozen linked sheets, and you have your business.

What profiling found	Count	What it would do to a naive import
Duplicate customer rows sharing one email	14 pairs	Two accounts, split order history
Duplicate supplier rows (incl. a typo variant)	2 names	Purchases split across “two” suppliers
Historical prices ≠ current catalog price	1+	Old margin reports silently rewritten
Manual stock counts drifted from reality	8 SKUs	Wrong opening balances, invisible
SKU typos in order/purchase rows	7 rows	Orphaned lines that reference nothing
Retyped product names that don’t match	24 rows	Phantom products or failed lookups
Case/format variants of the same value	11 values	”vip” ≠ “VIP” ≠ “Vip”
Exact-duplicate order lines	1	Double-counted revenue
Ambiguous rows a rule can’t resolve	3 orders	Silently dropped — or corrupt

A CSV import handles none of this. It either errors out at row 200, or worse, it succeeds — and you find out in June that March is wrong.

Why a CSV Import Fails on Real Data

SaaS importers and “upload your CSV” wizards assume the three things real spreadsheet data never has:

1. Unique keys. The import assumes one row = one customer, keyed by a clean email. Real sheets have 14 ways to spell the same customer, walk-ins with no email at all, and a TOTAL row someone added at the bottom for sums.
2. Consistent references. The importer assumes every SKU and supplier name matches the catalog exactly. Real sheets have TMC2-BLK typed as TMC2-BLK (trailing space), Horizon Coffee Imports next to Horzion Coffee Importers, and a product renamed in 2024 whose old orders still carry the old name.
3. “Current” as the only truth. Imports overwrite history with the present. But a $54.00 grinder sold in March — when the catalog now says $59.00 — is a fact. Rewrite it and your historical margins are fiction.

The failure mode is never a crash. It is a quiet success with wrong numbers, which is the one thing an operations system must never be.

The Migration Pattern: Profile → Clean → Validate → Quarantine

Migrating five years of operational data is not a file conversion. It is an accounting exercise, and it has four stages:

Stage	What happens	The rule
1. Profile	Read every sheet, count every anomaly by category — before anything moves	You can’t clean what you haven’t counted
2. Clean	Apply deterministic rules: normalize emails, merge duplicate entities, remap references	Rules, not vibes — same input, same output, every run
3. Validate	Re-assert business facts against the result: stock = purchases − sales; old order keeps its old price	The data must prove itself correct
4. Quarantine	Rows no rule can resolve safely go to a review log — a human decides	Nothing is silently dropped. Ever.

The output that matters is the exact-count migration report: every category of dirt, how many rows were found, how each was resolved, and what went to quarantine. Not “import successful” — an audit trail of the migration itself.

What the Report Actually Shows

You can walk through this on the live demo — the migration report is generated by the same pipeline that would migrate your sheets¹. Three findings worth reading closely:

The stock investigation. The manual inventory sheet said 45 units of the flagship SKU. The movement ledger says: purchased 50 + 40 + 30, sold 85 → 35 units. The report shows both numbers as evidence — and the computed 35 is authoritative, because it is derived from every movement, not from someone’s Friday-night recount. Those 8 drifted SKUs are exactly the discrepancies this step surfaces.

The historical price. Order ORD-1042: Marcus Vance bought a grinder at $54.00. The catalog price today is $59.00. The migrated system keeps $54.00 on the order — because the order happened at $54.00. March’s margin report must never be rewritten by June’s price list.

The customer merge. “Marcus Vance” appears in the sheet multiple times with the same normalized email — 12 orders split across duplicate rows. After migration: one account, full history, every order attached. And the 3 orders whose customer name resolved ambiguously? Quarantined, with reasons, for a human decision. That is the difference between an import and a migration.

Day One: Migrated, Not Abandoned

After the migration runs, the team gets:

• A working system — purchasing, receiving, inventory, orders, payments — with roles, audit trail and a full history, on any device.
• An exact-count report proving nothing was lost: every row accounted for, every merge explained, quarantine items listed by name.
• A Google Sheets mirror of the legacy data for stakeholders who still want to look at the old sheets — read-only, push-only, zero new habits.
• Ownership. The database, the source and the VPS are yours. Leaving means exporting one file — the exit is always open.

The sheets retire. The history doesn’t.

When a Plain CSV Import Is Actually Fine

An honest decision framework, because sometimes the wizard is the right answer:

Your situation	Do this
One entity, few hundred rows, no cross-references, one owner	CSV import into the SaaS — it will hold
Data you can re-verify by eye in an afternoon	CSV import, then spot-check
Linked entities (orders → customers → products), years of history, multiple editors, numbers that must reconcile to accounting	Rule-based migration with a quarantine review
Nobody can say which of two conflicting numbers is right	Migration first, software second — the profiling run is the discovery

The uncomfortable summary: if your data is clean enough for a one-click import, you didn’t need a new system yet. The mess is the signal.

TL;DR

Problem	”Our data is five years of messy sheets” — the #1 blocker to leaving spreadsheets
Why imports fail	Real data has duplicates, typos, drifted counts and historical prices; importers assume none of that and fail quietly
The pattern	Profile → clean by rule → validate business facts → quarantine the unresolvable; every row accounted for by exact count
The proof	A migration report showing both conflicting numbers (45 vs 35), preserved history ($54 on a $59 catalog), merged customers — and zero silently dropped rows
See also	Sheets → SQL in one transaction: a full migration demo · Migrating an AppSheet CRM to Express + PostgreSQL

Bring your worst sheet — I’ll profile it and show you what migration actually takes, before you commit to any software.

Sources

Footnotes

1. The demo dataset and its migration report are live and reproducible: demo.kamensky.dev/migration-report (authentication required; the dataset is deterministic — same seed, same numbers on every reset). ↩ ↩²