Quick Answer
Before importing a raw HR export into an HRIS, run three cleaning passes: remove rows with blank employee IDs, deduplicate by employee number to catch records from overlapping department exports, and trim whitespace from name and code fields. Mixed date format inconsistencies — mm/dd/yyyy values alongside yyyy-mm-dd in the same column — require a separate normalization step; the Data Cleaner identifies them through filtering but does not convert date formats. Use SplitForge Data Cleaner for all removal, deduplication, whitespace, and empty-value operations. No file upload — processing runs in your browser.
Not legal or compliance advice. HR data handling requirements vary by jurisdiction. Consult legal and compliance counsel before finalizing data workflows.
TL;DR: HR exports destined for HRIS import typically fail for the same three reasons: blank employee IDs from rows that did not make it through the source HRIS export filter, duplicate records from department-level extract files that were concatenated without a deduplication pass, and date format inconsistencies from combining exports from different regions or system versions. Each is fixable before import. Blank IDs are removed or flagged with the remove-empty-rows or replace-empty operation. Duplicates are resolved with a deduplicate-by-columns pass on the employee ID field. Mixed date formats are identified through date filtering and corrected with a Find & Replace normalization before re-loading. Whitespace in name and code fields is cleared in a single trim operation. The entire workflow runs locally in your browser — no file upload, no GDPR Article 28 processor relationship created for the cleaning step.
The Reality: What a Raw HR Export Looks Like
A payroll coordinator is preparing an HRIS migration for a 430-person company. The source is two ADP Workforce Now exports — one from the US payroll team and one from the Canadian payroll team — concatenated into a single file for the HRIS vendor's migration specialist.
Here is what the raw combined file looks like before any cleaning:
EmployeeID,FirstName,LastName,HireDate,Department,PayGrade,EarningCode
EMP-10382,Johnson,Michael,2021-03-15,Engineering,P3,REG
EMP-10383,Williams, Sarah ,04/22/2019,Marketing,P2,REG
,Torres,Elena,2023-08-01,Finance,P4,REG
EMP-10382,Johnson,Michael,2021-03-15,Engineering,P3,REG
EMP-10385,Chen,David,03/07/2020,Operations,P2, REG
EMP-10386,Patel,Anita,2022-11-30,Engineering,P3,REG
Three problems are visible in six rows:
Blank employee ID (row 4): Torres, Elena has no employee ID value. The HRIS import will either reject this row or, depending on platform configuration, create a new record with a generated ID that does not match the existing employee record.
Duplicate record (row 5): Johnson, Michael appears twice with identical data — once from the US extract and once from the Canadian extract file. Import platforms that update by employee ID will process the duplicate as an update to an already-created record. Import platforms that check for duplicates will reject the second row with an error.
Mixed date formats (rows 2 and 6 vs. rows 1, 3, 5): The US export uses mm/dd/yyyy (04/22/2019, 03/07/2020). The Canadian export uses yyyy-mm-dd (2021-03-15, 2023-08-01, 2022-11-30). Most HRIS platforms expect a single consistent date format. A file with both formats in the same column will fail date validation or silently parse some dates incorrectly.
Additionally: a leading space before "Sarah" in FirstName (row 2), a trailing space in " REG" in EarningCode (row 6). Minor, but name field spaces cause display issues and code spaces cause matching failures against earning code registries.
What Each Cleaning Operation Addresses
Before configuring the Data Cleaner, identify which operation targets which problem:
| Problem | Data Cleaner operation | How |
|---|---|---|
| Blank employee ID rows | Remove Empty Rows | Removes rows where all fields are blank — or filter by EmployeeID = blank, then remove filtered rows |
| Duplicate employee records | Deduplicate by Columns | Select EmployeeID as the deduplicate key; keeps first occurrence, removes subsequent duplicates |
| Leading/trailing whitespace in name and code fields | Trim Whitespace | Applied to all columns or selected columns |
Earning code with whitespace ( REG) | Trim Whitespace | Clears leading/trailing spaces from the code string |
| Empty department or pay grade fields | Replace Empty Values | Fill with a placeholder or default before import |
| Mixed date formats (mm/dd/yyyy vs. yyyy-mm-dd) | Filter + external step | Data Cleaner identifies affected rows; format normalization requires Find & Replace (see Step 4) |
Step-by-Step Cleaning Workflow
Step 1 — Load the file
Open SplitForge Data Cleaner. Drop your concatenated HR export. The file is read locally by the browser's File API — no upload, no transmission. For files combining multiple regional exports, load the already-concatenated file; the Data Cleaner processes the full combined dataset.
Step 2 — Identify and remove blank employee ID rows
Set up a filter: column = EmployeeID, filter type = "is empty" (or "equals" with a blank value). Review the filtered rows to confirm they are genuinely missing IDs — not rows where the employee ID was exported in a non-standard format. Once confirmed, use Remove Empty Rows to remove rows where the employee ID field is blank. If the platform requires every row to have an ID, do not use Replace Empty Values as a substitute — a filled placeholder ID will not match existing employee records during update imports.
Step 3 — Deduplicate by employee ID
Use Deduplicate by Columns. Select the EmployeeID column as the deduplication key. The operation keeps the first occurrence of each employee ID and removes all subsequent rows with the same value. Before running, verify that the first occurrence (from whichever regional export appears first in the file) has the correct and complete data — deduplicate-by-columns keeps the first match, not the most complete one.
If you want to keep the record with the most complete data rather than the first occurrence, do a manual review of the duplicated IDs before running the operation.
Step 4 — Normalize date formats (separate step, not Data Cleaner)
The Data Cleaner does not convert date formats. It can filter rows to identify which dates are in a non-standard format, but it does not transform yyyy-mm-dd to mm/dd/yyyy or vice versa.
To identify the scope: set a date filter on the HireDate column. Rows with yyyy-mm-dd dates will behave differently from rows with mm/dd/yyyy dates under the date filter. Use this to count how many rows in each format you have.
To normalize: use SplitForge Find & Replace on the date column with a regex pattern to convert the minority format to the standard. For example, to convert yyyy-mm-dd (e.g., 2021-03-15) to mm/dd/yyyy: use a regex pattern that captures the year, month, and day groups and reassembles them in the target order. After Find & Replace, reload the normalized file into the Data Cleaner for the remaining operations.
What date format your HRIS expects depends on the platform. Confirm the required format against the import template or documentation before normalizing.
Step 5 — Trim whitespace
Apply Trim Whitespace. This removes leading and trailing spaces from all cell values across every column. The operation addresses: spaces before or after names (" Sarah" → "Sarah"), spaces in earning code values (" REG" → "REG"), and incidental spaces introduced by copy-paste from spreadsheet editors.
If you want to restrict trimming to specific columns — for example, name and code fields only, leaving notes or description fields untouched — use the column selector in the Trim Whitespace panel.
Step 6 — Replace empty values in non-ID columns
For fields like Department or PayGrade where an empty value is ambiguous — could be a missing value or a legitimate blank — use Replace Empty Values to fill with a placeholder before import. The appropriate placeholder depends on the HRIS's handling of blank fields: some platforms treat a blank as "no change" in update imports; others treat it as an intentional null that overwrites the existing value.
Do not use Replace Empty Values on the employee ID or SSN columns. Filling a blank ID with a placeholder creates a false record linkage risk in update imports.
Step 7 — Standardize case (if needed)
If your HRIS enforces case formatting — for example, earning codes must be uppercase, or department names must be title case — apply Standardize Case. Select the target columns and choose the case type: upper, lower, title, or sentence. This prevents case mismatches when the import file is validated against a reference list in the HRIS.
Step 8 — Remove empty rows and columns
Run Remove Empty Rows to catch any rows that are entirely blank — these sometimes appear as trailing blank lines in concatenated files. Run Remove Empty Columns to drop any columns that are entirely unpopulated across all rows — these may appear in exports that include optional fields not configured in your company.
Step 9 — Preview and export
Use the before/after preview to verify: the EmployeeID column has no blank values remaining, the row count reflects the expected number after deduplication, the HireDate column shows a consistent single format (after the Find & Replace step), name fields have no leading or trailing spaces. Export via the browser's local download — no upload occurs.
Step 10 — Name the output file
Rename the cleaned file to distinguish it from the raw export:
- Raw:
hr-export-combined-20260523.csv - Cleaned:
hr-export-combined-20260523-CLEANED.csv
Do not overwrite the raw export. Keep the raw file as an audit reference — if a cleaning operation removes a record that should have been retained, you need the original to recover it.
Platform-Specific Notes
HRIS platforms differ in how they handle pre-import data structure. The cleaning workflow above is generic — applicable to any HRIS migration. Platform-specific import requirements — which columns are required, what the accepted date format is, whether header rows are expected — are documented in each platform's import template or data loader documentation.
For ADP Workforce Now–specific CSV import errors, see ADP CSV Import Errors: Fix Bad Employee Number & Codes.
For Workday EIB–specific validation errors including row-length failures, see Workday EIB CSV Errors: Row Length, Date and Validation Fixes.
For a pre-import structure validation pass (beyond cleaning), see Payroll CSV Validation Checklist Before HRIS Import.
Privacy Note: Cleaning HR Files Without Upload
HR export files contain employee PII — names, job titles, hire dates, pay grades, and in many exports, SSNs, bank details, and earning codes that may identify medical leave. Running this file through an online cleaning tool transmits that data to a third-party server, creating a GDPR Article 28 processor relationship.
SplitForge Data Cleaner processes entirely in your browser. No file data leaves your machine at any point during loading, cleaning, or export. For the full analysis of what online processing tools do with HR files, see Never Upload a Payroll CSV to an Online Tool. For the GDPR Article 28 framework for payroll data, see HR Payroll CSV GDPR Compliance. For the broader privacy-first processing framework, see Privacy-First Data Processing Guide.