Payroll Data Migration Field Map: Translate Records Without Losing Their Meaning
Updated: 20 hours ago
An employee number can survive an import and still point to the wrong person.
A payroll migration field map explains the meaning, destination and acceptance test for each piece of data moving between providers. Build it before preparing the production import. The useful result is a set of traceable decisions: which record supplies a value, how it changes, where it lands and what proves that it works there.
Use this field map when moving an existing U.S. payroll into a new system and deciding how its records should be translated. It focuses on the data contract behind the transfer. The wider migration schedule and first-payroll release decision still need their own owners.
Start with the most consequential fields, then work through the families below. Use the reusable field card to resolve one ambiguous item at a time. A small employer can keep these cards in shared working notes; a larger migration can use the same headings in a controlled mapping file.
Confirm the migration service before choosing the provider. Talk through your transfer needs with a SelectSoftware Reviews advisor in a free consultation. Ask each finalist directly which imports, configuration tasks and validation work its proposed package includes.
Table of contents
Separate the four things people call payroll data
Treat the transfer as four related deliveries. Combining them into one employee spreadsheet obscures missing work.
Current operating settings tell the new system what to do next: employment status, compensation, pay schedule, approved withholding settings, recurring deductions and payment instructions. Their acceptance test concerns the next affected payroll.
Historical amounts describe payroll already processed. These can include wages, taxable-wage categories, taxes, deductions and employer contributions for an agreed year and quarter. Their acceptance test compares the same employee, employer, period and balance definition on both sides.
Configuration and crosswalks explain how records should be interpreted. Earning codes, deduction definitions, departments and employer-account associations may need setup before an import can use them. An imported code label does not establish that its target calculation is correct.
Retained evidence preserves what happened before the move: detailed registers, filed returns, payment confirmations, elections and correction records. Some evidence belongs in an accessible archive rather than the new payroll database. That is a deliberate storage choice with an owner and retrieval test.
Start the field inventory with eight families
Use these starting families to find required fields in your own reports and receiving specification. They are an inventory of questions, not a universal import schema.
Employer: Legal entity identifier, approved agency-account references, filing arrangement and the target employer record. Keep separate employers distinguishable throughout the transfer.
Employee: Source and target IDs, name, authorized tax identifiers, hire or separation dates and status. Identify who needs ongoing pay and who needs historical reporting only.
Compensation: Pay type, rate or salary basis, schedule, earning-code definitions and effective dates. Include special pay rules actually used by the employer.
Withholding: Applicable federal and state elections, form versions, approved settings and effective dates. Preserve exceptions requiring a specialist's review. Include IRS lock-in letter instructions and the calendar year covered by an exempt Form W-4.
Deductions and contributions: Code behavior, employee assignment, employer or employee share, amounts or percentages, remaining balances where relevant and start or stop dates. Carry garnishment instructions through the employer's authorized process.
Payment: Approved method, account references, allocation instructions and authorization evidence. Separate unchanged migration data from requests to change a destination.
Opening history and balances: Employee and employer totals by required period and category, plus PTO or other balances that the new system must administer. Document each balance's unit and as-of date.
Downstream and archive references: Department or costing codes, source-system relationships, retained report locations and the crosswalk needed to retrieve old records.
For every family, mark what will be imported, configured separately or retained elsewhere. Add a specific acceptance result for each required category rather than one blanket “employee loaded” status.
Gusto's current transfer documentation illustrates the boundary. Its onboarding import uses specified employee and payroll-history reports; it does not set up deductions, contributions or other pay items. Accepted report formats depend on the source provider. Obtain the current requirements for your actual transfer route before generating files.
Give each delivery a named output and recipient. For example, the employee configuration report goes to the payroll reviewer, the opening-history comparison goes to payroll and finance, and the archive index goes to the records owner. One successful upload should not close all four deliveries.
Before requesting data, decide whether a field is essential to the first payroll, needed for historical continuity or retained only for later reference. Defer a field only when its absence cannot compromise the required payroll or reporting behavior, and document how reviewers or payroll staff will retrieve it meanwhile.
Write the import contract before moving records
For each file, obtain a current target specification from the receiving provider. Record its version, accepted file type, field definitions, permitted values, required and conditional fields, date conventions, numeric precision and correction route. Resolve whether the importer adds records, updates them or replaces existing values.
Also ask what a blank cell means. Depending on the documented behavior, it might preserve the existing value, clear it or reject the row. Do not substitute zero for missing data to make a file upload. A zero deduction is a substantive value; an unresolved deduction is a question.
Keep source exports unchanged. Use a working copy for transformations and identify both the source extract and the mapping version on the resulting file. A later reviewer should be able to reproduce why a target value differs from the original.
Copy the Field Translation Card
Create one card for a field whose meaning, transformation or acceptance needs a decision. Repeat it for every required field family and for exceptions that use a different rule. Keep employee-level evidence in a restricted location; use masked references in shared project notes.
Field and purpose: Name the business value and the payroll or reporting behavior it supports.
Record key: Identify the employer or entity, source employee ID, target employee ID and any period or effective-date key required to distinguish this record.
Source evidence: Record the system, report, field name, extraction time, filter and file version.
Target requirement: Record the receiving field, specification version, type, allowed values and whether it is required or conditional.
Meaning check: State whether the source and target measure the same thing. Identify any distinction that needs a separate field or setup item.
Translation rule: Describe the explicit code crosswalk, format conversion, unit conversion or approved calculation. Write “no transformation” when the value should pass unchanged.
Blank and exception rule: State what missing, invalid, duplicate or conflicting values do. Name the person who resolves them.
Time coverage: Record the calendar year, quarter, pay dates, effective dates or balance-as-of date that apply.
Expected target result: Specify the exact stored value and any calculation, report or downstream output that must reflect it.
Actual result and evidence: Record the imported value, observed output, evidence location and test version.
Decision and owner: Mark Accepted, Correction required or Deferred with control. Name the reviewer and any remaining dependency. For a deferral, record the interim process, responsible owner and completion date.
Retest trigger: Specify which source, rule, configuration or date change would invalidate this acceptance.
Use “Accepted” only when the required expected results are observed for the tested version and no essential dependency remains unresolved. “Correction required” means the card cannot support the affected production use. “Deferred with control” identifies an item outside the immediate required scope, with a workable retrieval or interim process, owner and completion date. Do not use it to waive a missing tax setting or unexplained opening balance.
A completed card for an employee identifier
Consider a fictional employer, Cedar Workshop, whose source employee ID is 00123. The receiving system assigns internal ID CW-482. All names, field definitions, test results and evidence references below are fictional. Replace them with your provider's specification and your own observed results.
Field and purpose: Preserve the original employee identifier so old payroll registers connect to the intended new employee record.
Record key: Employer Cedar Workshop, source ID 00123, target ID CW-482. The employer and source-ID pair must identify one target record.
Source evidence: Employee export E3, field employee_number, extracted after the final completed old payroll. The raw value is the text 00123; the restricted source file is recorded as Cedar/E3/employee-export.
Target requirement: Specification T2 requires a text external-reference field and a separate internal employee ID. These are sample requirements, not a claim about a named product.
Meaning check: The source ID and target reference identify the same employer-scoped worker. The target internal ID is a different identifier and must remain a separate value.
Translation rule: Preserve 00123 as text, with no change to its leading zeros. Connect it to CW-482 through the crosswalk.
Blank and exception rule: Hold a missing source ID, duplicate employer/source-ID pair or conflicting target match for identity review by the payroll owner. If the target cannot preserve the reference, establish and test an external crosswalk before acceptance.
Time coverage: Retain the association for every historical period included in the agreed transfer and for the next payroll using the new record. This card does not approve the historical amounts themselves.
Expected target result: The exported target reference is 00123, and the employer/source-ID pair joins to CW-482 exactly once in both the crosswalk and the receiving report.
Actual result and evidence: In this fictional test, the target report preserves 00123 as text and the crosswalk returns one matching row. The exported value was checked, not just its screen formatting. Evidence: restricted folder Cedar/E3-R1/identity-proof, test version E3-R1 against T2.
Decision and owner: Accepted by fictional payroll reviewer Maya Chen for version E3-R1. No unresolved identity dependency remains. This acceptance covers the identifier relationship only.
Retest trigger: A new source extract, reference-field type change, crosswalk revision or rehire merge requires the affected identity checks to run again.
Preserve the original identifier and prove its relationship to the target record.
The card makes three checks explicit: the original identifier remains recoverable, its target relationship is unique, and downstream reports use that relationship. A technically valid file can pass the first check while failing the other two.
Resolve identities and code meanings first
Start with employer and employee identity because later balances depend on those keys. Decide how the target represents employing entities, rehires and historical-only employees. Preserve the source identifiers in a crosswalk even if the new provider uses different internal IDs.
Do not use a person's name as the only joining key. Names can change, repeat or be formatted differently. Reconcile the authorized population to employee records, identify duplicates and investigate unmatched records without merging them automatically. A rehire may require one continuing record or a documented relationship between records, depending on the receiving system.
For name and SSN questions, use the employer's authorized verification process. SSA limits its Social Security Number Verification Service to wage-reporting purposes for current or former employees. A mismatch alone does not establish immigration status or justify adverse action. It is not a general identity check for applicants or contractors.
Next, translate earning and deduction codes by their behavior. For each active code, document whether it is an amount or rate, who funds it, the relevant approved tax treatment, how it affects net pay and where it appears in reporting. A label such as “medical” does not establish whether the value is an employee deduction or employer contribution.
Separate code configuration from employee enrollment. A correctly configured deduction can still be missing from an employee, have the wrong start date or use a different amount. Compare the approved election and target assignment, then inspect an affected calculation. Obtain specialist review when the tax treatment or order terms are uncertain.
Keep home address, work location, employing entity and reporting department distinct. They may coexist on one employee record but answer different questions. A department code is not proof of a tax jurisdiction. Map the approved treatment and applicable employer-account setup, then check the new system's resulting configuration.
Handle banking and withholding data through restricted workflows. Preserve the approved payment destination without treating an import as permission to change it. Require an independent authorized process for new or changed banking instructions. Mask values in ordinary mapping evidence and limit access to the actual sensitive records.
Federal withholding also requires more than a filing-status label. Preserve the applicable Form W-4 version, settings and effective date. IRS Publication 15 explains that valid pre-2020 forms can continue and describes an optional computational bridge. Do not casually translate legacy allowances into the redesigned form's dollar fields or reset an employee merely because payroll is moving.
Keep pay history on one documented basis
An opening balance needs a definition. “YTD wages” is not enough: state the calendar year, employee, employer, included pay dates, earning or tax category and treatment of voids and corrections. Preserve the distinction between gross earnings, taxable-wage bases, tax withheld and employer tax amounts.
Inventory everyone paid in the relevant year, including people no longer receiving payroll. QuickBooks Online Payroll's current setup guidance includes former employees paid during the year, employee YTD information and company totals for paydays in the current quarter. Confirm the exact history granularity your receiving route requires.
Do not add separate detail and summary imports together without confirming how the target uses them. A closed-quarter total and current-quarter pay-date detail can serve different setup requirements. If both represent the same wages and the importer accumulates both, history can be duplicated despite each file being individually correct.
Work a correction through the opening balance
Suppose a fictional employee's approved history through September 30 shows gross wages of $42,600. That figure already includes a $600 bonus. An October preparation file adds a previously omitted September adjustment of $150. After payroll approves the correction, the revised historical gross figure is $42,750.
The initial import contains $42,600. Record that version and the $150 difference. If the receiving process replaces the balance, the replacement value is $42,750. If it adds a correction, the incremental value is $150. Sending $42,750 through an additive process would produce $85,350, not the approved balance.
Confirm whether the supported route replaces a balance or adds a correction.
These amounts illustrate gross-history arithmetic only. They do not determine taxable wages, withholding, tax deposits, deduction limits or accounting entries. Review every affected balance category and reporting output using the employer's approved correction treatment. A gross-wage match cannot approve the entire correction.
Reconcile history at employee and category level before accepting company totals. Two employees with opposite $100 errors can leave the total unchanged. Preserve the details that explain each difference, and distinguish missing records from legitimate changes in report definitions.
Record who handles prior-period corrections, deposits and filings. Importing balances into the new provider does not document whether the old provider filed a return or whether a payment was made. Keep the reporting handoff separate from the history-load decision.
Also decide which historical detail remains outside the new system. Intuit explains that its YTD entry does not recreate accurate historical detail for every pay date and does not put that history onto financial reports. Preserve the old reports and plan any necessary accounting work rather than expecting the new history screen to replace them.
Ask about the work behind the history import. Several entities, unusual pay items or corrections may require more conversion work than the standard import covers. SelectSoftware Reviews offers a free advisor consultation for comparing payroll options. Obtain each finalist's written scope and test its receiving route before relying on it.
Prove transformations with a small difficult file
Build a deliberately varied test file before preparing the full production population. Include a leading-zero identifier, a name with punctuation, a rehire or historical-only employee, a dated pay-rate change, an unusual deduction and a corrected history balance when these occur in your workforce. Use test data and a non-payment route agreed with the provider.
Inspect the file at three points: the original export, the transformed import and the target report. Count expected, accepted and rejected records at each stage. A rejection list needs a resolution route; deleting rejected rows to achieve a clean upload changes the population.
Text identifiers need text handling
Microsoft documents that Excel can remove leading zeros and change large numbers when it interprets them numerically. Import identifier columns as text before conversion occurs. A display format that adds zeros does not recover lost source characters or prove that an exported CSV retains them.
Use the actual target specification for SSNs, bank fields and external IDs. Do not apply one generic numeric conversion to every column that contains digits. Check the exported file itself and the receiving value, especially after someone opens and saves a working copy.
Dates need a declared interpretation
The string 03/04/2026 can be ambiguous without a convention. Specify the source meaning, target date format and any relevant time-zone handling. A date of birth is a calendar date; an integration event timestamp may carry time and zone information. Treating both identically can change a date unintentionally.
Test a value that exposes a swapped month and day, and verify the resulting target date. Where the provider accepts an unambiguous format such as 2026-04-03, use it according to the specification. Do not assume that every importer accepts the same format.
Amounts and units need an explicit conversion
Suppose the source holds an annual salary of $78,000 and the stated schedule has 26 equal regular salary installments. The illustrative installment is $3,000. A target field expecting the annual salary still needs $78,000; putting $3,000 there would change its meaning. Actual proration and irregular payments need the employer's approved rules.
For time, distinguish decimal hours from hours and minutes. A value of 7.30 interpreted as decimal hours represents seven hours and eighteen minutes, not seven hours and thirty minutes. Write the conversion rule and verify a payroll result, rather than accepting a plausible-looking screen.
Record amount precision, sign and rounding rules from the actual configuration. For exact opening values, expect the approved balance to match. For calculated outputs, explain any legitimate rounding difference and its impact instead of imposing an arbitrary tolerance that could conceal a systematic mapping error.
Carry late changes without overwriting good data
The initial export is a baseline with a timestamp. Keep a separate list of changes approved afterward: new hires, employment-status changes, compensation adjustments, elections, payment-instruction changes and corrected history. Each item should identify the affected key, old value, authorized new value, effective date and reviewer.
Keep the date a change was recorded separate from the date it takes effect. A rate approved October 5 but effective October 1 may affect a payroll already under review. A future-dated rate should not silently replace the current rate for an earlier calculation.
Where an import cannot store historical or future-dated rows, agree on a supported way to load the applicable current value and enter the later change. Preserve the relationship in the mapping evidence. Do not discard dates simply because the target has one visible compensation field.
Before accepting a refreshed file, compare it with the prior accepted version. Identify added, removed and changed records, then verify whether the importer preserves or clears fields omitted from the refresh. Recheck the source crosswalk and balance coverage whenever population changes.
Keep a compact change record
Copy these entries for each late change:
Source and target record keys: ______
Approved request or correction reference: ______
Previous value and authorized replacement: ______
Business effective date and recorded time: ______
Baseline and map versions affected: ______
Import or supported setup action used: ______
Payroll, report or downstream result requiring retest: ______
Actual result, evidence reference and reviewer: ______
Close the change only when the correct version is loaded and the affected result is rechecked. If a second extract could overwrite an employee's newer authorized bank details, remove that conflict through the approved mapping and change process before loading it. Do not rely on a generic “latest file wins” rule.
Approve the map through evidence, not an upload receipt
Give the preparer responsibility for documenting the rule and the reviewer responsibility for accepting its meaning and observed result. Where one person must perform both tasks, arrange an independent review of consequential settings and balance differences before production use.
Use these four acceptance questions for each required field family:
☐ Complete: Are all required records present, including exceptions and the relevant historical population?
☐ Faithful: Does the translation preserve the intended meaning, identifiers, amounts, units and dates?
☐ Usable: Does the target configuration, calculation or report produce the expected result?
☐ Traceable: Can another authorized reviewer find the source, rule, loaded version, actual result and approval?
An unchecked question keeps that family open. Assign the unresolved issue to the person who can change its source, transformation or target configuration. Correct the responsible record, repeat the affected test and update the card's result. Do not sign off a field by writing a general comment that the provider reviewed the file.
Before handing the accepted map to the release reviewer, attach its version, population scope, balance coverage and unresolved-item list. Identify the exact import and configuration versions it approves. A later change to an accepted rule should trigger the relevant retest, not reuse the old approval.
Keep access to retained evidence independent of the former provider's ongoing subscription where practical. IRS Publication 15 requires employment-tax records to be kept at least four years and identifies longer periods for some credit-related records. Determine the applicable retention schedule with the responsible adviser; the ordinary minimum is not permission to delete every older payroll document.
Retain a usable index rather than an unexplained folder of exports. For every deferred historical item, record what it contains, where it is stored, who can retrieve it and how it connects to the new employee or employer key. Try retrieving one terminated employee's prior-period detail using the crosswalk before retiring old access.
The accepted map should leave the implementation team with specific facts it can use: which values are loaded, which settings were configured separately, which history categories tie out and which records remain in the archive. Carry those facts into payroll rehearsal and release review.
Questions that surface during provider handoff
Can the provider's import template be our field map?
Use it as the receiving specification. Add the source evidence, meaning decisions, translation rules, date coverage and acceptance results. A list of target columns alone cannot explain how you obtained their values or whether they behave correctly after import.
Must every historical transaction move?
No universal import requirement applies to every provider and transfer route. Obtain the receiving requirements and preserve the detail needed for reporting, employee questions and corrections. Some history can remain in an accessible archive when the new setup has the necessary opening information.
Is a matched gross-pay total enough?
No. Check the underlying employee population and each required balance category. Gross wages can match while taxable wages, deductions, employer contributions or individual records are wrong. Use the card's expected-output field to specify what else needs inspection.
What if a required value has no target field?
Ask for the supported alternative and test its consequences. An external crosswalk can preserve a historical identifier, but it cannot replace a payroll setting that the calculation requires. If essential behavior remains unsupported, resolve the configuration or provider-fit question before accepting that field family.
How do we know the map is finished?
It is ready for its stated production use when every required family passes the four acceptance questions, consequential differences are resolved, deferred items have workable controls and the reviewer has accepted the exact loaded version. Keep that version with the migration evidence. The next approval concerns the payroll produced from it.
Browse the Payroll Operations & Controls collection for related operating decisions. You can also return to the full Decision Guides library.
Bring unresolved fields to the provider conversation. Use your map to explain which configuration, history or retained-record tasks still need support. A free SelectSoftware Reviews advisor consultation is one way to discuss provider options; confirm any finalist's conversion commitments directly before deciding.
About the author
Ben Scott writes and maintains payroll decision guides for founders and operators. His work focuses on execution realities and how decisions hold up under growth, complexity, and controls and documentation pressure. He works hands-on in HR and leave-management roles that intersect with payroll-adjacent workflows such as benefits coordination, cutovers, and compliance-driven process controls.
