From Excel to WMS: A Heartbreaking Data Migration Guide for SMEs

Last summer on the hottest day, I was squatting at the warehouse door, staring at the screen that said "Data validation failed: inventory total difference 123,456.78 yuan." I was numb. That was a week of work, with two interns, painstakingly exporting data from Excel. The difference was over a hundred thousand. My expression must have been like eating a pound of bitter melon.

TL;DR Moving from Excel to WMS, data migration is the easiest place to trip. I fell into three big pitfalls: inconsistent formats, chaotic coding, and incomplete historical data. I later developed a five-step migration method: clean → map → trial → validate → formal, each step with bloody lessons.

闪仓 WMS · 示意图

内容概览

Pitfall 1: Excel Formats Were All Over the Place, Same SKU Had Five Spellings

At the start, I naively thought Excel data was tidy. But when I opened it, the same SKU was sometimes "ABC-001", sometimes "abc001", and sometimes "ABC 001" (with a space). Quantities had units like "100 pcs" or just "100". Supplier names were a mess: "Zhang San Trading Co., Ltd." and "Zhang San Trading" were the same company.

Standardizing data format is the first step of migration, and the easiest to overlook.

Data Cleaning: I Spent Three Days Doing One Thing

I had the interns collect all Excel files, then wrote a VBA script to unify:

• SKUs to uppercase, remove spaces and special characters
• Quantity columns to remove units, keep only numbers
• Dates to YYYY-MM-DD format
• Supplier names matched to standard names via VLOOKUP

After three days, the data was finally presentable.

闪仓 WMS · 示意图

Data Cleaning: I Spent Three Days Doing One Thing

Code Unification: From Manual Reconciliation to Auto-Matching

Previously, when reconciling manually, I could recognize "Zhang San Trading" and "Zhang San Trading Co., Ltd." as the same. But machines are strict string matchers. So I built an alias table mapping all variants to standard names.

This table later became a standard feature in Flash Warehouse, saving 80% of cleaning time for new clients.

Pitfall 2: Inventory Codes Were Chaotic, Same Product Had Three Codes

After cleaning formats, I thought I was safe. But during trial migration, the system flagged "duplicate product codes." Turns out the same product (e.g., "iPhone 14 128G Black") had been entered as "IP14-128-BK", "14IP-BK-128", and "Apple14Black128" in different batches.

Establishing a unified coding system is the soul of data migration; otherwise, the system is full of garbage data.

Code Mapping: I Gave Each Product an "ID Card"

I spent two full days cataloging all products, creating a master data table with unique internal codes (e.g., "SP-0001"), then mapping all Excel codes to these master codes.

This process taught me: In the Excel era, everyone was their own coder; in the WMS era, we must speak the same language.

闪仓 WMS · 示意图

Code Mapping: I Gave Each Product an "ID Card"

Historical Data Supplement: Those Handwritten In/Out Sheets

What's worse, some historical data wasn't in Excel at all. Old warehouse manager Xiao Zhang had handwritten six months of in/out records. I had to photograph each page, use OCR, and manually verify. A week later, I had added 800+ records.

This lesson led me to add a "Historical Data Import Assistant" with OCR and batch import in Flash Warehouse.

Pitfall 3: Skipped Trial Migration, Almost Crashed Production Data

After the first two steps, I was confident and ready for formal migration. Luckily, the Flash Warehouse tech consultant reminded me: "Lao Wang, do a trial migration first with a backup." I agreed outwardly but thought: Is it necessary?

Trial migration is the airbag of data migration; never skip it.

Trial Migration: Found a Fatal Error

I used a backup from last month and ran the migration in a test environment. Result: all inventory cost unit prices doubled. Investigation revealed that Excel's amount column was "price including tax" while WMS used "price excluding tax," and the conversion formula was wrong.

If I had gone straight to formal migration, my inventory value would have doubled, wreaking havoc on financial reports. I still get chills thinking about it.

闪仓 WMS · 示意图

Trial Migration: Found a Fatal Error

Validation Checklist: I Made a 20-Item List

After trial migration succeeded, I created a validation checklist, each item manually verified:

• Total inventory quantity difference < 0.1%
• Total inventory value difference < 0.1%
• No duplicate product codes
• All supplier names matched
• Last 30 days of in/out records complete

Only after passing every item did I proceed to formal migration.

Pitfall 4: No Fallback During Formal Migration, Almost Couldn't Go Back

On formal migration day, I chose a weekend with no warehouse operations. I exported, transformed, and imported all data in one go. After completion, I found all document timestamps were set to the import time, not the original business time.

Always have a rollback plan and keep original data backups.

Rollback Plan: I Kept Three Copies

Thankfully, before migration, I had done three things:

1. Compressed original Excel files and stored them on cloud
2. Saved cleaned data as a separate file
3. Took a full backup of WMS data

When I found the issue, I immediately rolled back to the backup, modified the import script to preserve original timestamps, and re-imported. It took two hours.

闪仓 WMS · 示意图

Rollback Plan: I Kept Three Copies

Parallel Running: Both Systems Ran for a Week

After formal migration, I didn't stop using Excel immediately. Instead, I ran both systems in parallel for a week: daily Excel bookkeeping and WMS operations. After a week, data matched perfectly, and I finally tossed Excel into the recycling bin.

At that moment, I finally breathed a sigh of relief.

Pitfall 5: Employees Didn't Cooperate, Accurate Data Was Useless

Data migration was done, system went live, but here came the problem: old warehouse employees couldn't use it and didn't want to. They were used to Excel and handwritten forms, seeing WMS as "trouble."

Data migration is just the first step; people migration is key.

Training: I Turned the Manual into a Rhyme

Instead of thick manuals, I turned common operations into a rhyme: "Inbound first scan, shelf to location, outbound double-check, inventory can't be missed." I printed it as posters and hung them on walls.

闪仓 WMS · 示意图

Training: I Turned the Manual into a Rhyme

Incentive: Let Data Speak

I set a "Zero Error Award" for the first week: any employee using WMS without errors got 200 yuan. Only two got it the first week, but five the second. After a month, everyone was using WMS because it was indeed more convenient than Excel.

According to McKinsey research^[1], human factors account for over 70% of successful digital transformations. Data migration is just technical work; getting people to use it is an art.

Conclusion

Moving from Excel to WMS took me three months, cost me ten pounds, and I fell into five big pits. But looking back, every step was worth it. Now, one person in my warehouse can do the work of three before, and the error rate dropped from 5 per week to less than 1 per month.

Key takeaways:

• Data cleaning is the foundation; don't rush migration, unify formats first
• Trial migration is the airbag; always run it in a test environment
• Have a rollback plan; always leave yourself an escape route
• Run parallel for a week; compare data before retiring the old system
• People migration is more important than data migration; keep training down-to-earth and incentives in place

Honestly, if Flash Warehouse had existed back then with its built-in migration assistant and OCR, I could have saved half the time. But these pitfalls taught me to automate every step of data migration when developing Flash Warehouse. After all, nobody wants to lose that much weight again.

---

References

1. McKinsey Operations Insights — McKinsey research on human factors in digital transformation