How to migrate legal entity data from spreadsheets without importing the mess
The biggest migration mistake is treating the import as the finish line. A successful entity-data migration separates source records from approved records, surfaces conflicts and creates a reviewed baseline teams can trust.
Move from spreadsheet inventory to reviewed entity baseline without hiding conflicts.
The method works for spreadsheets, CSV exports, legacy systems and document-derived data.
On this page
- Key takeaways
- 1. Inventory the real sources
- 2. Define the target model before importing
- Illustrative matching logicNot production code
- 4. Reconcile conflicts rather than overwriting them
- 5. Approve the first baseline
- Use a pilot to test the data model and review process
- 6. Design what happens after migration
- Questions
Key takeaways
- Do not clean every source file manually before migration.
- Define the target entity model before mapping source columns.
- Preserve conflicting values and their provenance until a reviewer resolves them.
- Approval establishes the first reviewed baseline; later changes should remain traceable.
1. Inventory the real sources
Most organizations discover more sources than expected: master spreadsheets, local trackers, SharePoint folders, registry extracts, service-provider portals and legacy databases. Record which team uses each source and what they believe it is authoritative for.
Common migration failure modes
- Using the newest spreadsheet as the unquestioned master even when another source contains more reliable fields.
- Matching entities by name alone and creating duplicates after legal-name changes or local abbreviations.
- Importing multi-valued people or ownership relationships into flat text columns.
- Marking blank values as errors even when a field is legitimately not applicable.
- Waiting until the end of migration to decide who owns review and approval.
2. Define the target model before importing
Decide the required entity identity, ownership, governance, document and obligation fields first. Otherwise the new system simply inherits the shape and inconsistencies of the old spreadsheet.
- Map equivalent columns to one target field.
- Normalize country, jurisdiction, entity-type and status values.
- Keep original source values for traceability.
- Detect likely duplicate entities using multiple identifiers, not name alone.
- Separate missing values from intentionally not-applicable values.
Example: fuzzy matching names without losing the original legal text
Entity-name matching should normalize for comparison while preserving the exact source value. Foreign characters and legal suffixes are common: Müller GmbH, Compañía Ejemplo S.A. de C.V. and Example Pte. Ltd. should not be reduced to unsafe ASCII-only master names.
Illustrative matching logicNot production code
1. Preserve source_name exactly.
2. Create comparison_name:
- Unicode-normalize for search/matching
- case-fold
- normalize punctuation and whitespace
- map known legal suffix variants (e.g. "PTE LTD" ↔ "PTE. LTD.")
3. Require jurisdiction/country consistency.
4. Prefer registration number / LEI / tax identifier when available.
5. Use fuzzy-name similarity only to create a candidate duplicate.
6. Send ambiguous matches to human review — never auto-merge on name alone.This approach makes “Muller GmbH” discoverable as a likely match for “Müller GmbH” while retaining the approved registered spelling in the authoritative record.
4. Reconcile conflicts rather than overwriting them
If two sources disagree on a director, address or ownership percentage, the migration should create a review issue. Reviewers need the candidate values, source, source date and evidence required to resolve the conflict.
A value becomes trusted because it was reviewed against an appropriate source and approved — not because it appeared in the newest spreadsheet.
Example reconciliation exception queue
| Entity | Field | Source A | Source B | Review action |
|---|---|---|---|---|
| Example Holdings Ltd | Registered office | Legacy master — 1 High Street | Registry extract — 20 King Street | Review effective date and approve current address |
| Example Finance GmbH | Managing director | Spreadsheet — A. Müller | Local provider — B. Weber | Obtain current registry evidence |
| Example APAC Pte. Ltd. | Direct parent | ERP reference — HoldCo A | Share register — HoldCo B | Review transaction/effective date and ownership evidence |
5. Approve the first baseline
Once the key issues are resolved, record the reviewer and approval timestamp. That creates the first governed baseline. Subsequent changes should be captured as changes to that baseline rather than silently replacing it.
Use a pilot to test the data model and review process
For large portfolios, the safest migration is usually not “all entities, all fields, all at once.” Select a representative scope that contains enough complexity to test the target model: different jurisdictions, ownership structures, active and dormant entities, a few known data conflicts and at least one recurring compliance workflow.
The pilot should answer questions that are difficult to discover in a spreadsheet workshop. Which fields are genuinely global? Which require local extensions? Who can approve ownership? What evidence is enough to verify an appointment? How should legacy identifiers be retained? Which exceptions can remain open after go-live and which must be resolved first?
Migration governance roles
| Role | Responsibility |
|---|---|
| Migration lead | Scope, timeline, source inventory, mapping decisions and acceptance criteria |
| Data-domain owner | Defines what is authoritative for identity, ownership, governance, tax/finance or obligations |
| Reviewer | Resolves or approves important conflicts and baseline values |
| Local / client contributor | Supplies jurisdiction or portfolio evidence when central records are incomplete |
| System administrator | Permissions, import configuration, issue routing and post-go-live ownership |
What to do with unresolved exceptions at go-live
Not every issue needs to block launch. The important distinction is between a known exception with an owner and an unknown weakness hidden inside the migrated record. Establish clear severity rules. A missing historical document might be acceptable as a tracked remediation item; a disputed current legal owner or unknown active director may require resolution before the baseline can be approved.
When exceptions remain, carry them into the live operating queue with owner, priority, source context and target resolution date. Migration then becomes the beginning of data governance rather than a one-time clean-up exercise.
6. Design what happens after migration
Migration is successful only if the record stays healthy. Define owners, review cycles, source expectations, obligation workflows and issue handling before the project team disappears.
For a large portfolio, a bounded region, business unit or representative client can expose mapping and governance issues before a global rollout.
Define migration acceptance criteria
Do not use “100% rows imported” as the success measure. A more useful acceptance set is: entity population agreed, critical identity fields reconciled, ownership chains sufficiently complete for scope, known exceptions assigned to owners, reviewer approvals captured, and post-migration governance responsibilities documented.
Questions
Do we need to clean spreadsheets before importing them?
Not completely. Basic housekeeping helps, but the migration process should identify duplicates, missing values and conflicts rather than relying on a perfect source file.
How do you identify duplicate legal entities?
Use multiple attributes such as registration number, jurisdiction, registered name and historical identifiers. Name-only matching is risky.
What should happen when two sources disagree?
Keep both candidate values with provenance, flag the conflict and require review against the appropriate evidence.
Should documents be migrated with entity data?
Yes where useful, but link documents to the entity, relationship or obligation they support rather than recreating an unstructured file dump.
How do you know migration is complete?
Completion should be defined by an approved baseline and known exceptions, not simply by the percentage of source rows imported.
Prepared by the EntityOS Editorial Team for operational education. Regulatory references are checked against primary sources where cited. Jurisdiction-specific legal, tax and regulatory decisions should be reviewed by qualified advisers.
This resource is general operational information, not legal, tax, accounting or regulatory advice. Requirements vary by jurisdiction and facts. Use qualified advisers for jurisdiction-specific decisions.