This is a composite of three Canadian SMB projects, with names and details changed. The numbers are illustrative of what we saw, not audited figures from one client.
The file was called MASTER_inventory_FINAL_v7_USE THIS ONE.xlsx. There was also a v8. Nobody was sure which one the warehouse used.
That was week one at a 60-person building-supplies distributor with a yard in Ontario and a smaller one in BC. They weren't disorganised. They'd grown for fifteen years on spreadsheets because spreadsheets worked, right up until the day they didn't. When we finished the inventory of every file that ran part of the business, the count was 43.
This post is about how that got down to one database, and the parts that were harder than the software.
Step one: inventory the spreadsheets, not the processes
Most ERP projects start with process mapping workshops. We did those too, but later. The first thing was a plain list of every spreadsheet that someone opened at least once a week, with four columns:
- file name and location (shared drive, someone's desktop, email attachment)
- owner, meaning the one person who'd notice if it broke
- what it feeds (another sheet, the accounting system, a customer)
- what breaks if it's wrong for a day
That last column did most of the work. It split the 43 files into roughly three groups: about a dozen that directly moved money or stock, about twenty that were reporting built on top of those, and the rest, which were personal trackers nobody else relied on.
The reporting sheets were the easy win. Once the source data lives in one place, most of them simply disappear. The personal trackers we left alone. The dozen money-and-stock sheets were the actual project.
Research has been saying this for a long time. Raymond Panko's review of spreadsheet error studies concluded that errors are "both common and non-trivial". What surprised the team here wasn't that errors existed. It was how many of them were copy-paste links between files that had silently broken months earlier.
The cleanup nobody budgets for
Before anything went into Odoo, the customer and product lists had to be merged across both yards. That's where the time went.
We ran a quick duplicate check in pandas before touching the import tool. It's crude, but it finds the obvious problems in minutes:
import pandas as pd
df = pd.concat(
[pd.read_excel("customers_on.xlsx"), pd.read_excel("customers_bc.xlsx")],
ignore_index=True,
)
# normalise the fields people type differently
df["name_key"] = (
df["Customer Name"].str.upper()
.str.replace(r"[^A-Z0-9]", "", regex=True)
.str.replace(r"(INC|LTD|LIMITED|CORP)$", "", regex=True)
)
df["phone_key"] = df["Phone"].astype(str).str.replace(r"\D", "", regex=True).str[-10:]
dupes = df[df.duplicated(["name_key"], keep=False) | df.duplicated(["phone_key"], keep=False)]
dupes.sort_values("name_key").to_excel("review_duplicates.xlsx", index=False)
That produced about 300 candidate duplicates out of roughly 2,100 customers. Around two thirds were real. The rest were legitimately separate accounts: a head office and a branch of the same contractor, each with its own billing terms.
No script decides that last part for you. Someone from accounts receivable sat with the list for most of two days. Plan for that. When teams compare odoo implementation services, this is the line item worth asking about: who does the merge review, and how many days are in the quote for it.
External IDs saved us twice
Odoo's importer takes Excel or CSV files, and the documentation strongly advises not removing the External ID column from the templates. Take that seriously. We gave every record a stable key before the first import:
id,name,default_code,categ_id/id
product_yard_2x4_8ft,2x4 SPF 8ft,LUM-2408,cat_lumber
product_yard_osb_716,OSB 7/16 4x8,SHT-0716,cat_sheet_goods
Two things made this worth the effort. First, imports are permanent and can't be undone, so when the first product load had wrong units of measure on 140 items, we fixed the spreadsheet and re-imported against the same IDs instead of hunting down records by hand. Second, the vendor price lists and reordering rules referenced products by id, not by name, so a renamed product didn't break every file that pointed at it.
Always hit Test before Import. It catches most mapping errors without writing anything.
Where the spreadsheets were hiding a process
The hardest sheet was the one the sales desk used to price special orders. It looked like a price list. It was really an approval workflow: margin below 18% meant the cell turned red, and a red cell meant you walked over to the sales manager's desk.
That rule lived in conditional formatting and in one person's habits. In Odoo, it became an approval rule on quotations below a margin threshold. Standard Sales doesn't do margin-based approvals out of the box, so this was one of the few small customizations in the project. It took a day to build and a month to get people to stop walking over anyway.
This pattern is common in distribution. We wrote separately about how manual order processing erodes margin for wholesale businesses, and nearly every case traces back to a rule that only existed in someone's spreadsheet.
The two yards added another wrinkle: separate stock, a shared customer list, and different provincial sales tax (HST in Ontario, GST plus PST in BC). That's a warehouse-and-company structure question you want answered in week two, not week ten. There's a longer answer to whether Odoo can handle a multi-location structure if you're facing the same decision. Here it was one company, two warehouses and fiscal positions per province.
Before and after (illustrative)
These are the kind of figures we tracked. Treat them as a shape, not a benchmark.
- Spreadsheets in weekly use: 43 before, 6 after (the personal trackers, plus a commission sheet sales refused to give up)
- Month-end close: about 9 working days before, about 4 after three months live
- Stock count variance at the BC yard: noticeably lower once receipts were scanned instead of keyed from paper
- "Which version is current?" messages in the team chat: gone within a month, which the owner said was the only metric he cared about
The close improvement didn't show up in month one. Month one was slower than the old way. It showed up in month three, once people stopped double-checking the new system against the old sheets.
What didn't work
We tried to migrate five years of transaction history. It was a mistake. The old data was inconsistent enough that importing it created reconciliation noise for weeks. In the end we loaded open items and opening balances only, and kept the old files read-only for lookups. If you're planning your own move, start there and add history only if someone can name the report that needs it.
We also underestimated training at the second yard. The Ontario team had been in the workshops; BC had only seen demos. Their first two weeks were rough.
If you're starting this
Do the spreadsheet inventory first, before any demo or workshop. It'll tell you how big the project really is. If you bring in outside help, favour the team that asks to see your spreadsheets in the first meeting. The ones that start with a feature tour usually find the hidden processes late.
Then pick your top dozen money-and-stock files, and write down the rule each one is secretly enforcing. That list is your real requirements document.
Top comments (0)