From Excel to WMS: My Near-Disaster in E-commerce Data Migration
Last summer, I spent three months migrating from Excel to WMS, lost ten pounds, and nearly lost all my inventory data. Today, I'll share the pitfalls I fell into and how to migrate safely and efficiently.
From Excel to WMS: My Near-Disaster in E-commerce Data Migration
At 2 AM, staring at the "Data Validation Failed" prompt on the screen, I collapsed into my chair. There were still over 3,000 SKUs to be shelved, and the big promotion was the next day—and I hadn't even reconciled my inventory yet.
TL;DR: Data migration looks simple, but 99% of people will step on a landmine. It took me three months and ten pounds lost to summarize this process. Today, I'm breaking down my blood and tears story for you.
Step 1: Clean Your Data—Don't Move Junk into Your New Home
I was so excited that I exported Excel to CSV and directly imported it into Flash Warehouse. The system immediately threw errors: "Duplicate SKU codes," "Inconsistent units," "Empty specifications"... I was dumbfounded.
Data cleaning is the most time-consuming but critical step before migration. I later spent an entire week scrubbing the dirty data from historical records.
Common Dirty Data Issues
- Inconsistent SKU codes: Some use "SKU123", others "SKU-123", and some are blank
- Mixed units: The same product, some write "pieces", others "units", the system can't recognize
- Messy specification fields: "Red", "R", "RD" mixed together
My Cleaning Checklist
| Data Field | Cleaning Requirement | Common Issue | Solution |
|---|---|---|---|
| SKU Code | Unique, no spaces | Case mix, extra spaces | Use Excel TRIM and UPPER functions |
| Quantity | Numeric, no unit | Mixed text like "100pcs" | Use Find/Replace to remove text |
| Supplier Name | Consistent full name | Abbreviations, aliases | Create mapping table, unify |
| Price Field | Numeric, 2 decimals | Currency symbols, commas | Use formula to remove symbols |
Step 2: Choose the Right Migration Strategy—Full or Incremental?
I chose full migration for convenience—dumped all three years of data in one go. The system froze for half an hour, and after completion, many historical order statuses were wrong.
Take small steps, do incremental migration first, then backfill historical data. That's the lesson I learned.
Comparison of Two Strategies
| Dimension | Full Migration | Incremental Migration |
|---|---|---|
| Time | One-time, but possibly long | Batch by batch, minutes each |
| Risk | Large data volume, hard to recover | Each batch independent, minor impact |
| Suitable for | Small data (<1000 SKUs) | Large data (>1000 SKUs) |
| Business disruption | Need to shut down | Can run while migrating |
I switched to incremental: first migrate active products and inventory, then batch import historical orders and supplier data. After each batch, I ran validation before proceeding.
Step 3: Validate Data—Don't Trust Your Eyes, Trust Scripts
A week after migration, I manually checked 50 SKUs and thought everything was fine. Then on promotion day, a customer ordered item A, but the system deducted stock from item B, nearly causing an oversell.
Manual checks are unreliable—you must write scripts for full validation. I wrote a simple Python script to compare Excel source data with WMS data row by row and flag discrepancies.
Validation Key Points
- Inventory quantity: Compare SKU by SKU, alert if error >1%
- Product info: Name, spec, supplier must match exactly
- Order status: Shipped, unshipped, returned must correspond one-to-one
Step 4: Rollback Plan—Leave an Escape Route
The scariest moment was when the system crashed mid-migration, corrupting a database table. I panicked—the old data was overwritten, and the new data hadn't been fully imported.
You must back up original data before migration and have a detailed rollback plan. Now before every migration, I do three things:
- Export all Excel source files, store both in cloud and locally
- Create a test environment in WMS, simulate the migration first
- Write a rollback script to restore to pre-migration state with one click
Summary
Data migration looks simple, but every step has a pitfall. I've stepped on them, so you don't have to.
Key Takeaways
- Clean your data before migration—don't move junk into your new home
- Prefer incremental migration, small steps for manageable risk
- Use scripts for full validation, don't trust manual checks
- Always have a rollback plan—backup, backup, backup
If you're planning a migration, start with a small batch of data to practice. After all, lost data can be recovered, but lost customers are gone forever.
References
- China Federation of Logistics & Purchasing — Reference for logistics data migration standards
- 36Kr — Reference for SME digital transformation cases
- Ebrun — Reference for e-commerce data migration best practices