Excel to WMS: My Two-Year Journey Through Data Migration Hell
It took me two years to migrate from Excel to WMS. Lost orders, lost customers, almost got sued by a supplier. Today I'm sharing the real story behind data migration.
Last summer, on the hottest weekend, something big happened in my warehouse.
I was at home enjoying the AC when a client called: 'Lao Wang, why did my order go to someone else?' I checked the system and found the order and shipment didn't match—a leftover Excel file from a previous migration was mistaken for new data, and the picker shipped to an old address. I compensated the client and spent the night repacking a dozen parcels.
TL;DR Data migration isn't just copy-paste. It took me two years to move from Excel to WMS, losing orders, customers, and almost getting sued by a supplier. Today I'm sharing the real story behind data migration.
First Migration: I Didn't Even Back Up
Back then, I decided to implement ShineCang WMS. I thought data migration was just copying Excel data—inventory, orders, supplier info—into the system. I spent three days manually cleaning 300,000 records and imported them in one go. After import, everything was messed up: negative inventory, duplicate SKUs, hundreds of records missing.
I was stunned: Data migration doesn't start with copy-paste.
Later, I found from a McKinsey survey[1] that over 60% of companies face serious issues during data migration, with data loss and inconsistency being the most common. I realized I made three fatal mistakes:
Didn't Clean the Data
My Excel had dirty data: extra spaces, inconsistent formats, duplicates. For instance, one supplier was called 'ABC Trading' and another 'ABC Trading Co.', but the system treated them as two entities. The right approach is to write a cleaning script to normalize formats, remove duplicates, and fill missing values.
Didn't Map the Fields
Excel field names didn't match WMS fields. For example, 'Inventory' in Excel might be 'Available Stock' in WMS. Without mapping, data goes to wrong places. I later used a mapping table to align each Excel field to its WMS counterpart.
Didn't Do a Trial Run
I imported directly to production without testing. When errors occurred, I had to roll back, but that lost some correct data. The right way is to first import a small batch to a test environment, verify logic, then full import.
Second Migration: I Learned Batch Import but Forgot Validation
After the first failure, I carefully cleaned and mapped data, and imported in batches—first products, then inventory, then orders. The import went smoothly, and I relaxed. But the next day, orders and inventory didn't match: the system showed 100 units of a SKU, but only 80 existed.
I thought: Import is just the beginning; validation is key.
Build Validation Mechanisms
I added triple validation:
- Quantity validation: Sum totals in Excel and WMS to check consistency.
- Spot check: Randomly sample 10% of SKUs, manually compare system vs physical stock.
- Logic validation: Check relationships, e.g., order quantity can't exceed inventory.
Design Rollback Plan
After the no-backup disaster, I always do a full backup before migration. If validation fails, I roll back to pre-migration state, fix issues, then re-import.
Comparison Table: First vs Second Migration
| Aspect | First Migration | Second Migration |
|---|---|---|
| Data cleaning | Not done | Script cleaning, dedup, format normalization |
| Field mapping | Manual | Mapping table auto-match |
| Trial run | None | Test environment small batch |
| Validation | None | Triple validation |
| Rollback | No backup | Full backup, rollback possible |
Third Migration: I Finally Handled Incremental Data
Previous migrations were full loads—importing all Excel data at once. But I was still using Excel for new data: new orders, new stock. How to sync these incremental records? If I waited until full migration and then manually added, errors would creep in.
I later realized: Data migration isn't one-time; you must solve incremental sync.
Incremental Migration Strategy
I designed a 'dual-track' approach: during migration, both Excel and WMS recorded data. A daily script synced new Excel data to WMS for a week until both systems matched, then I retired Excel.
Comparison Table: Full vs Incremental Migration
| Dimension | Full Migration | Incremental Migration |
|---|---|---|
| Use case | One-time historical data import | New data generated during migration |
| Frequency | Once | Daily or real-time |
| Complexity | Low | Higher, needs sync mechanism |
| Risk | Data lag after migration | Real-time sync, less lag |
Automate Sync with API
I wrote a small tool using the WMS API to read new Excel data daily and push it to WMS automatically. This eliminated manual entry and omissions.
Fourth Migration: I Learned to Involve the Team
Technical issues were solved, but human issues emerged. Warehouse staff were used to Excel and resisted the new system. One old employee secretly kept using Excel, causing WMS data to be inaccurate.
I thought: Data migration is not just technical; it's managerial.
Training First
I organized three training sessions: first on why migrate, second on how to use WMS, third for hands-on practice. I also recruited two 'seed users' to try it first and encourage others.
Parallel Run During Transition
I allowed a two-week transition where both Excel and WMS ran simultaneously. Staff could compare data from both systems and correct discrepancies. After two weeks, everyone saw WMS was better and fully switched.
Comparison Table: Tech-Driven vs People-Driven
| Dimension | Tech-Driven | People-Driven |
|---|---|---|
| Focus | Data accuracy, system stability | Employee acceptance, usage habits |
| Method | Scripts, validation, API | Training, incentives, parallel run |
| Outcome | Data correct but unused | Data correct and everyone uses it |
Summary
It took me two years to move from Excel to WMS, stepping on countless landmines. But looking back, the most valuable lessons are not technical solutions but the blunders:
- Back up first, then act: Always full backup before migration.
- Cleaning + mapping + trial run: Don't skip any step.
- Validation is lifeline: Check quantity and logic after import.
- Solve incremental sync: Dual-track works well.
- Don't ignore people: Train staff and parallel transition matter more than tech.
According to Gartner's supply chain research[2], companies that successfully implement WMS spend over 30% of the project time on data migration. So don't treat it as trivial; it deserves time and effort.
Now my warehouse runs on ShineCang WMS, with inventory accuracy up from 85% to 99.5% and mis-shipment rates near zero. Every time I see clean data in the system, I remember that costly weekend.
If you're planning a data migration, don't worry—follow my steps and you'll avoid many traps.
References
- McKinsey Operations Insights — Data migration issue statistics
- Gartner Supply Chain Research — WMS implementation time allocation