A migration needs a decision about every source row, including the ones it cannot import. This fictional work-order sample follows three rows through validation and reconciliation. Before using this approach on your own files, identify what each row represents and which copy is authoritative.
Keep the source reference.
These rows each represent one work order. Save the sheet name and row number with the import result so a reviewer can trace a rejected value back to its source.
Scroll horizontally to view every column.
| Row | Order | Account | Quantity | Status |
|---|---|---|---|---|
| 17 | W-104 | A-17 | 12 | Ready |
| 18 | W-105 | blank | 6 | Ready |
| 19 | W-106 | A-22 | 4 | Hold |
Confirm that A-17 and A-22 exist in the account data. A populated reference can still point to a missing record.
Define the import rules.
A heading such as Account leaves its validation rule unstated. In this sample, the value must match an existing account. Check formulas, hidden tabs and formatting for other rules the column names leave out.
Agree the treatment of duplicate identifiers and historical records before designing the database. If the file is imported again, decide which records are updated and which are rejected.
Scroll horizontally to view every column.
| Source → destination | Rule before import |
|---|---|
Order → external_ref | Required; preserve the value; unique per work order. |
Account → account_id | Look up the account reference. Reject a blank or unmatched value. |
Quantity → quantity | Parse as an integer greater than zero. Keep the original value for review. |
Status → state | Ready → queued; Hold → held. Reject other values for review. |
Enforce the agreed rules in both application validation and database constraints where applicable.
Account for all three rows.
The rehearsal accepts two rows and rejects one. Keep the rejected row, its location and the reason together so the source owner can correct it and rerun validation.
Accepted · row 17
{
"external_ref": "W-104",
"account_id": "A-17",
"quantity": 12,
"state": "queued"
}Rejected · row 18
- Source
Work orders!18 / W-105- Reason
- Account reference is blank.
- Next action
- Confirm the account with the source owner, correct the row and rerun validation.
Reconcile the whole sample
3 source rows = 2 accepted + 1 rejected.
Quantity: 22 in the source = 16 accepted + 6 held for correction.
Row 19 imports with state held. Its business status is separate from the rejected row awaiting a data correction.
Agree when to transfer.
Row counts and totals help account for the import. Also trace representative records through their relationships and calculated fields. Agree who reviews these checks and authorizes the transfer.
- Set the point when editing of the source files stops. Define a final import if work continues during migration.
- Retain the original files, mapping rules and exact migration version.
- Name the role that can approve corrections and the conditions for restoring the previous system.
Give users a way to report discrepancies after transfer. Keep unresolved records visible until the source owner accepts the correction.
Database migration work