HR Data Migration: Moving Off Spreadsheets Without Losing History

Every HRMS implementation contains a moment of discovery. Someone exports the employee master data, opens it, and finds that four hundred records contain three different date formats, ninety-one are missing an Emirates ID, and there are two employees with the same passport number because one transferred between group entities and was never deactivated.

This is normal. It is not a sign of a badly run HR function; it is what happens when records accumulate across spreadsheets, an old system, and the PRO team's tracker over several years. But it does mean migration deserves to be planned as a workstream in its own right, not treated as a technical task the vendor handles.

Decide what you are migrating, and be ruthless

The instinct is to bring everything. Resist it. Migrating twelve years of historical payroll detail because it might be needed adds weeks of reconciliation for data that will be queried twice.

A defensible scope for most GCC organisations looks like this:

• Full employee master data for all active employees, with no gaps in mandatory fields
• Employment history: joining date, entity, position and salary changes with effective dates
• Current leave balances, with the accrual basis documented
• Gratuity accrual as at cutover, reconciled to the finance provision
• Active documents: passport, visa, Emirates ID or equivalent, contract, with expiry dates
• Leavers from the current and prior fiscal year only, retained for statutory reporting

Everything else stays in an archive, accessible if needed but outside the live system. Payroll detail beyond the current fiscal year is the most common candidate for archiving, and almost nobody misses it.

The seven problems you will find

Extract early, before configuration is finished, because the extract tells you how much time cleansing will take. Expect these:

• Missing identifiers. Emirates ID, labour card, or IBAN absent. These block WPS submission, so they are not optional to fix.
• Duplicates. Usually inter-entity transfers where the original record was never closed.
• Inconsistent dates. Day-month and month-day formats mixed in one column, which silently corrupts service length and therefore gratuity.
• Name variance. The same person spelled three ways across systems, or English and Arabic name fields used interchangeably.
• Orphan records. Leavers still marked active, sometimes years later.
• Undocumented policy. Leave balances that cannot be reproduced from any written accrual rule.
• Salary ambiguity. A single "salary" figure where the system needs basic, housing, transport, and other components separated.

The last one is the most consequential and the most often underestimated. Gratuity, social insurance, and overtime all calculate on specific components. If your historical data holds only a gross figure, someone has to reconstruct the split, and that reconstruction needs sign-off because it changes liability.

Cleanse in the extract, never in the system

This is the rule that most reliably separates smooth migrations from painful ones. Fix the data in the staging file, validate it, then load once.

The alternative, loading what you have and tidying it afterwards, feels faster and is not. It leaves you unable to answer whether the system or the spreadsheet is authoritative, which means the spreadsheet survives, which means you now maintain both. We have seen organisations run parallel records for over a year for exactly this reason.

Gratuity and leave balances need finance sign-off

Two migrated values are not administrative details but accounting positions. Gratuity accrual at cutover is a balance-sheet liability, and the migrated figure has to reconcile to the provision finance already reports. If your system-calculated accrual differs from the provision, that difference must be understood before go-live, not discovered at year-end audit.

The usual causes of variance are a different salary base being used for accrual, unpaid leave periods treated inconsistently, or historical service breaks handled differently. Our guide to gratuity and end-of-service across the GCC covers the calculation rules market by market.

Leave balances need the same treatment for a different reason: they are a promise to employees. If a migrated balance is lower than what an employee believes they have, you will hear about it within days of self-service launching, and every one of those conversations erodes confidence in the new system.

Validation: three checks that catch almost everything

Once loaded into a test environment, three reconciliations catch the overwhelming majority of migration errors.

Headcount by entity. Count employees per legal entity in the source and the target. They must match exactly. A difference of one is usually a duplicate or an orphan, and finding it now is trivial.

Payroll parallel run. Run a historical month you already know the answer to, and compare line by line. This validates data and configuration simultaneously. Variances are expected; unexplained variances are not acceptable.

Aggregate balance reconciliation. Total gratuity accrual and total leave liability, source versus target, signed off by finance and HR respectively.

Pass all three and you can go live with confidence. Skip the second one and you will spend the first live cycle debugging under time pressure, which is the scenario every implementation plan exists to avoid.

Keep the old system readable, briefly

Do not decommission the legacy system on go-live day. Freeze it, keep it readable for one full quarter, and give two named people access. You will need it once or twice for a historical query, and having it available removes the temptation to keep maintaining a spreadsheet "just in case".

After a quarter, if nobody has needed it, archive it properly with a documented retention period. Note that employee data retention is subject to statutory minimums that vary by jurisdiction, so archive rather than delete.

What this buys you

Migration is unglamorous work, and it is tempting to compress it in favour of the parts of the project that demo well. The organisations that invest properly in it get something specific in return: a single authoritative employee record that payroll, recruitment, and engagement all read from, with no re-keying between them.

That single record is what makes everything downstream possible, including the anomaly detection and analytics capabilities that only work when the underlying data is trustworthy. Clean migration is not a prerequisite for a tidy database. It is the prerequisite for every intelligent feature you bought the platform for.

Who does the work, and how long it takes

Migration effort is consistently underestimated because it is assumed to be a technical task. In practice the technical loading is a small share of the work; the majority is decision-making about data that only your organisation can make.

For a single-entity organisation of a few hundred employees, expect the cleansing and validation effort to occupy one experienced HR administrator for the better part of three weeks, with intermittent input from finance and the PRO team. For a multi-entity group, add roughly a week per additional entity, more if entities use different systems today.

Three roles need to be named explicitly. A data owner from HR, who decides what is correct when records conflict. A finance counterpart, who signs off gratuity accrual and any provision reconciliation. And someone with historical knowledge, often a long-serving payroll administrator, who can explain why a particular employee's leave balance looks the way it does. That third role is the one most often omitted from project plans and the one most frequently needed.

What to do when history is genuinely unrecoverable

Occasionally a record cannot be reconstructed. A long-serving employee whose original contract is missing, a leave balance carried forward through three systems with no documented accrual basis, or a service date that appears differently in two sources.

The correct response is to decide, document, and communicate rather than to guess quietly. Establish a defensible position, record the basis for it in the employee's file, and where the position affects an entitlement, tell the employee before they discover it in self-service. An employee informed that their recorded joining date has been corrected with an explanation is a manageable conversation. The same employee discovering a changed gratuity figure in an app is not.

Where the ambiguity affects a material liability, involve finance and consider taking a conservative position. The cost of over-accruing slightly is far lower than the cost of an understated provision surfacing at audit.

The first month after go-live

Migration does not end at cutover. The first live month is when residual data problems surface, and planning for that is the difference between a short tail and a long one.

Expect three categories of issue. Individual record queries, where an employee's leave balance or joining date does not match their expectation; these are best handled by a named person with authority to correct and document. Reporting discrepancies, where a headcount or cost figure does not tie to a legacy report; almost always a definitional difference rather than a data error, and worth resolving formally so the definition is settled. And gaps in fields that were not mandatory at load but turn out to be needed operationally, such as emergency contacts or qualification details.

Assign explicit ownership for this tail and give it a defined end date, typically four to six weeks. Migrations that drift are usually those where nobody declared the migration finished, so corrections continued informally for months and the legacy spreadsheet quietly survived alongside the new system.

If you are planning a migration and want a realistic assessment of the effort involved for your data, send us an extract summary. We will tell you where the problems are likely to be before you commit to a date.