HRIS Migration CSV Prep: Clean, Map, and Validate Before Cutover

Quick Answer

HRIS migration CSV prep requires a specific sequence: encode and inventory all source exports first, reconcile employee IDs across systems before deduplication (deduplicating before ID reconciliation produces false unique records), normalize date formats with Find & Replace (Data Cleaner does not convert date formats), clean structural issues with Data Cleaner, map to your target platform's column schema, validate with Data Validator against rules matching your platform's import requirements, and run a test batch before cutover. This guide covers the migration-level prep workflow — multi-system consolidation, ID reconciliation, and cross-system deduplication — not per-file cleaning. For per-file cleaning of a single export, see How to Prepare Employee CSV Files for HRIS Import.

---

Not legal or compliance advice. Data handling requirements vary by jurisdiction and HRIS platform. Confirm import requirements against your platform documentation before cutover.

---

TL;DR: Migration prep fails at one of three points: (1) deduplication runs before ID reconciliation — the same employee has two different IDs across systems, dedup sees two distinct records, and both import as separate employees; (2) date normalization is skipped — ADP exports MM/DD/YYYY, the Workday EIB template expects YYYY-MM-DD, every date field rejects on import; (3) the validation pass is run against generic rules rather than the actual target platform's import template — encoding and column count violations only surface in the EIB error report. The sequence below eliminates all three failure modes in the right order.

---

The Reality: What a Combined Migration File Looks Like

A migration from ADP Workforce Now (US) and a legacy HRIS (Canadian division) produces two exports that, when concatenated, look like this:

EmployeeID,FirstName,LastName,HireDate,Department,PayGrade,EarningCode,Status
EMP-10382,Johnson ,Michael,03/15/2021,Engineering,P3,REG,Active
CA-10382,Johnson,Michael,2021-03-15,Engineering,P3,REG,Active
EMP-10383,Williams,Sarah,04/22/2019,Marketing,P2,REG,Active
,Torres,Elena,08/01/2023,Finance,P4,REG,Active
EMP-10385,Müller,Hans,2022-09-14,EMEA,P3,REG,Active
EMP-10382,Johnson,Michael,03/15/2021,Engineering,P3,REG,Active

Six problems are present before any cleaning step runs:

1. ID schema discontinuity — Row 2 uses the ADP schema (EMP-10382); Row 3 uses the Canadian schema (CA-10382). These are the same employee. A deduplication pass run now sees two distinct employee records and retains both.
2. Mixed date formats — Row 2 uses MM/DD/YYYY (ADP export default); Row 3 uses YYYY-MM-DD (Canadian HRIS default). Both exist in the same HireDate column.
3. Duplicate row — Rows 2 and 7 are identical (EMP-10382, Johnson, same date). This is a straightforward duplicate from a re-export overlap.
4. Blank employee ID — Row 5 (Torres) has no employee ID. If imported, this row either rejects or creates an employee record without an identifier.
5. Encoding corruption — Müller (Row 6) appears as M??ller in files exported from Windows-1252–encoded systems. The special character was corrupted during concatenation. Before Format Checker can confirm encoding, this problem is invisible — the row looks correct until you check the raw bytes.
6. Leading/trailing whitespace — Johnson in Row 2 has a trailing space. ADP export artifacts like this cause first-name mismatches during ID reconciliation if the comparison is exact-string.

None of these six problems are catchable by a single-file validator run against only Row 2 or only Row 3. They are migration-level problems, visible only when the exports are examined together.

---

The Migration Prep Sequence

Run these phases in order. Skipping or reordering Phase 3 and Phase 4 produces the duplicate-ID failure described above.

---

Phase 1 — Inventory and Encoding Check

Before concatenating exports:

1. List every source system and export file. Note the encoding, date format, delimiter, and employee ID schema for each.
2. Run SplitForge Format Checker on each file individually. Format Checker detects encoding (using jschardet), delimiter, line endings, and intra-file column-count consistency across rows — in your browser, no upload required.
3. Files with Windows-1252 encoding must be re-encoded to UTF-8 before concatenation. In Excel: Save As → CSV UTF-8. In a text editor: re-save with UTF-8 encoding. Do not concatenate mixed-encoding files — the special character corruption in the Müller example above occurs at the byte level and cannot be repaired after concatenation.
4. Document the ID schema for each source system (e.g., ADP uses EMP-NNNNN; Canadian HRIS uses CA-NNNNN). You will need this schema map in Phase 3.

---

Phase 2 — Consolidate

Once all source files are UTF-8 encoded and structurally verified:

1. Align column schemas before concatenation. If ADP exports use FirstName and the Canadian HRIS uses First_Name, rename columns to a single canonical name in every file before concatenation.
2. Add a SourceSystem column to each file before concatenation, with a value identifying the source (ADP_US, LegacyHRIS_CA, etc.). This column is essential for Phase 3 ID reconciliation and for rollback tracing if the import produces unexpected results.
3. Concatenate the files. The resulting combined file will have mixed ID schemas, mixed date formats, and any encoding artifacts not caught in Phase 1.

---

Phase 3 — Employee-ID Reconciliation

This phase must run before deduplication. Deduplication works by comparing a key column — in this case the employee ID — and removing rows that share the same key. If two source systems use different ID schemas for the same employee (EMP-10382 and CA-10382), deduplication sees two distinct keys and retains both records. After reconciliation, both carry the same canonical ID, and deduplication correctly identifies and removes the duplicate.

ID reconciliation requires a cross-reference table: a mapping of every legacy ID to the target HRIS's canonical employee ID. This table is typically provided by the HRIS implementation team or constructed from a master employee list built during the system selection process.

Steps:

1. Export the master employee list from the target HRIS (or the canonical roster if the target HRIS is not yet live). This list should include the canonical employee ID and at least one additional unique identifier — name and hire date is the minimum; national ID is preferable.
2. For each unique SourceSystem in the combined file, match employee records to the master list using the available identifiers. This matching pass is where whitespace and case normalization matters: Johnson (trailing space) will not match Johnson on an exact-string comparison. Run Data Cleaner trimWhitespace and standardizeCase on the name columns of the combined file first.
3. For records that match, replace the source-system employee ID with the canonical target HRIS ID.
4. For records that do not match — employees in the export but not in the master list — flag them for manual review. These may be terminated employees, contractors not in the master list, or data entry errors. Do not leave unmatched records in the migration file without resolution.

After reconciliation, every employee record in the combined file should carry the same canonical ID regardless of which source system it came from.

---

Phase 4 — Remove Blank IDs and Deduplicate

With IDs reconciled:

1. Filter out rows with blank employee IDs. A blank ID either fails import or creates an unidentifiable employee record. If the blank-ID row contains the only record for that employee, complete the ID before this step — do not discard employee data. If the blank-ID row is a duplicate of an identified record, remove it.
2. Run deduplication against the canonical employee ID column using Data Cleaner dedupeByColumns. With IDs reconciled, duplicate detection now correctly identifies the same employee across source systems.

Why order matters: Running dedupeByColumns on the pre-reconciliation combined file keeps both EMP-10382 and CA-10382 because they have different ID values. Running it after reconciliation, when both carry the same canonical ID, correctly identifies and removes the duplicate.

---

Phase 5 — Date Normalization

Data Cleaner does not convert date formats. Data Cleaner's operations are: removeEmptyRows, dedupeByColumns, trimWhitespace, replaceEmpty, standardizeCase, and removeEmptyColumns. Date format conversion requires Find & Replace with a regex pattern.

For converting from MM/DD/YYYY to YYYY-MM-DD (required by most Workday EIB templates — see Workday EIB CSV Errors):

Use Find & Replace with regex enabled:

• Pattern: ^(\d{2})/(\d{2})/(\d{4})$
• Replacement: $3-$1-$2

This pattern captures month (group 1), day (group 2), and year (group 3) and reassembles in ISO order. Run this against every date column in the file. Check for placeholder dates (00/00/0000) and remove or correct those rows before re-importing.

For per-file cleaning of individual source exports before this migration workflow — including date preparation at the single-file level — see How to Prepare Employee CSV Files for HRIS Import. That guide covers one-file cleaning; this guide covers what comes after the files are combined.

---

Phase 6 — Structural Cleaning

With encoding normalized (Phase 1), IDs reconciled (Phase 3), duplicates removed (Phase 4), and dates normalized (Phase 5), run a final structural cleaning pass with Data Cleaner:

• removeEmptyRows — removes rows where all fields are blank
• trimWhitespace — removes leading/trailing spaces from all fields (catches export artifacts like Johnson )
• standardizeCase — normalizes department and pay grade values to consistent casing if needed (ENGINEERING vs Engineering vs engineering)
• removeEmptyColumns — removes columns that are entirely empty in the combined file (columns present in one source export but absent from another)

---

Phase 7 — Target Platform Column Mapping

Map the canonical combined file to the target platform's import template column schema. Platform-specific requirements:

• Workday EIB: Fixed column count per template. Every row must have exactly the same number of columns as the EIB template. Column names must match the EIB template exactly. For the full format requirements, see ADP vs. Gusto vs. Workday CSV Format Differences.
• ADP Workforce Now: Positional column mapping. Column order must match the ADP import layout. No header row in standard configuration. For ADP-specific requirements, see ADP vs. Gusto vs. Workday CSV Format Differences.
• Gusto: Header row required with Gusto template column names. UTF-8 required.
• QBO Advanced (AU/UK): Template-driven import. For QBO-specific import requirements, see QuickBooks Payroll CSV Import Errors.

Remove the SourceSystem column before import — it is a migration tracking field, not a target HRIS field. Its presence in the import file will cause column count or header validation errors on most platforms.

---

Phase 8 — Validation

Run SplitForge Data Validator against the platform-mapped file with a schema configured for your target platform's requirements:

• required rules on every field the target platform marks mandatory (employee ID, first name, last name, hire date at minimum)
• uniqueness rule on the employee ID column
• dataType: date on all date columns — catches any remaining placeholder dates (00/00/0000) or date values that cannot be parsed
• dataType: decimal and range: min 0 on salary and pay rate columns — catches formula residue and negative values
• regex on the SSN column if present (pattern: ^\d{3}-\d{2}-\d{4}$ for US format)
• enum on the earning code column if you have the target platform's approved earning code list

For the full validation checklist with automated vs. manual distinctions, see Payroll CSV Validation Checklist Before HRIS Import.

---

Phase 9 — Test Batch

Before submitting the full migration file, submit a 10–50 row test batch to the target HRIS. The test batch should include:

• At least one record for each source system in the migration
• At least one record with the edge cases you cleaned (special characters in names, non-standard pay grades, earning codes unique to one source system)
• No records for currently active employees whose live data could be overwritten by a test import — use terminated employees or synthetic records if your platform allows test imports in a non-production environment

If the test batch imports cleanly, submit the full file. If the test batch produces errors, fix the issue class in the full file before re-running the test batch.

---

Phase 10 — Archive

After a successful migration import:

1. Archive the migration working files — the raw source exports, the combined pre-reconciliation file, the ID reconciliation cross-reference table, and the final mapped file — with the import date and target platform noted in the filename.
2. Retain the ID reconciliation cross-reference table separately. It is the audit record for how source-system IDs map to the target HRIS canonical IDs, and will be needed if data correction or retrospective analysis is required.
3. Log the import run ID or confirmation number from the target HRIS. For Workday EIB, this is the integration run ID. For ADP, this is the import batch ID.

---

Migration Prep Sequence Table

---

Privacy Note: Migration Files Contain the Full Employee Record

HRIS migration files combine data from multiple source systems — employee IDs, SSNs, salary data, national identifiers, medical leave codes. The combined file is the highest-sensitivity payroll file in the migration workflow.

Running a combined migration file through an online tool transmits PII from every source system to a third-party server simultaneously, creating a GDPR Article 28 processor relationship for each source system's data.

Data Cleaner, Find & Replace, Data Validator, and Format Checker run entirely in your browser. No file data is transmitted. Verify with DevTools Network tab: zero POST requests appear during processing. For the full privacy analysis of online tool upload risk, see Never Upload a Payroll CSV to an Online Tool. For the broader privacy-first processing framework, see Privacy-First Data Processing Guide. For the full HR/payroll data handling framework, see the HR & Payroll CSV Data Prep Guide.

---

FAQ

Additional Resources

Tool operations verified against the SplitForge codebase; workflow reflects standard HRIS migration practice, May 2026.

• RFC 4180: Common Format and MIME Type for CSV Files — The CSV format standard; relevant for the column count consistency rules that underlie Workday EIB row-length validation
• Unicode FAQ — Background on character encoding; explains why concatenating Windows-1252 and UTF-8 files corrupts special characters at the byte level
• ADP vs. Gusto vs. Workday CSV Format Differences and Why Imports Fail — Format requirements for the three platforms most common in HRIS migrations
• Workday EIB CSV Errors: Row Length, Date and Validation Fixes — Detailed Workday EIB row-length, date error, and partial-commit behavior
• Payroll CSV Validation Checklist Before HRIS Import — Automated vs. manual validation checks; configure Data Validator rules against this checklist
• How to Prepare Employee CSV Files for HRIS Import — Per-file cleaning workflow; covers the Phase 5 single-file step in detail
• HR & Payroll CSV Data Prep Guide — Complete guide hub for HR data preparation

---