How to Migrate ATS Data from Spreadsheets to Automation: A Step-by-Step Guide

Spreadsheets kept your recruiting operation running. Now they’re holding it back. The move from fragmented Excel files to a fully automated Applicant Tracking System is the single most consequential infrastructure decision your HR team will make — and the migration itself is where most implementations are won or lost. This guide covers the complete, sequenced process: from pre-migration audit through post-launch decommissioning of your source files. It’s the operational complement to our broader ATS automation consulting strategy, focused on the one phase that determines whether your automation investment compounds or collapses.

The thesis is simple: data quality before migration is the only variable you fully control. Everything downstream — reporting accuracy, AI-assisted matching, candidate experience, compliance posture — depends on the foundation you build here. Get the sequence right and your ATS becomes a force multiplier from day one. Get it wrong and you automate your existing inefficiencies at scale.

Before You Start: Prerequisites, Tools, and Honest Risk Assessment

Do not begin migration activities until you have confirmed all four of the following prerequisites.

• ATS vendor import template and field specification in hand. Your target system dictates the required format. Download the official import template before touching a single source spreadsheet.
• A complete inventory of every spreadsheet containing candidate data. This includes shared drives, personal laptops, email attachments, and any shadow IT tools recruiters have built. Undiscovered spreadsheets surface after go-live and create data integrity nightmares.
• A designated data owner with decision-making authority. Field mapping and deduplication decisions require someone who understands the business — not just the data. HR leadership must be in the room.
• Compliance obligations documented. EEOC recordkeeping minimums, GDPR requirements for EU candidate data, and applicable state privacy laws all attach to this data the moment it moves. Understand your retention and deletion rules before migration begins. See our guide on automated ATS compliance obligations for a full breakdown.

Time estimate: 4–8 weeks for a mid-market operation with 2,000–10,000 records. Most of that time is the audit and cleansing phases. The actual import is often completed in hours.

Primary risks: data loss from unsaved source files modified during migration; post-import deduplication debt that makes recruiters distrust the system; compliance violations from retaining records past their deletion date.

Step 1 — Audit Every Source File and Map the Data Landscape

Before you can migrate data, you need a complete and honest picture of what you actually have. This step produces the single most important artifact in the entire migration: the Source Data Inventory.

For every spreadsheet in scope, document the following:

• File name, location, owner, and date last modified
• Total record count
• Column headers and a representative sample of values for each column
• Identified quality issues: blank fields, inconsistent formatting, apparent duplicates, freeform text mixed with structured data
• Estimated date range of records (oldest application date to most recent)

This is not a technical task — it requires a recruiter or HR manager who understands what the data means operationally. A column labeled “Status” might contain anything from “Phone screen done” to “Hired” to “Maybe good for Q3” depending on who entered it and when. That kind of entropy must be documented before it can be resolved.

Based on our experience, this audit phase routinely surfaces two to three additional spreadsheets that weren’t in the original inventory. Build in time for that discovery.

Step 2 — Define Migration Scope and Set Your Retention Cutoff

Not all historical candidate data is worth migrating — and migrating everything is almost always the wrong decision. The goal is a clean, queryable dataset of records that have present or near-future business value, not a complete archaeological archive.

Define your migration scope along three dimensions:

Recency Cutoff

Establish a date threshold. Active candidates and those who applied or were contacted within the past 12–24 months are strong migration candidates. Records older than your average time-to-fill cycle multiplied by two are rarely worth the import complexity they create.

Compliance Retention Rules

EEOC recordkeeping requires that application materials be retained for a minimum of one year from the date of the employment decision (two years for federal contractors). Records that have already satisfied their minimum retention window and have no active legal hold should be archived or permanently deleted — not migrated into a new system where they’ll accumulate indefinitely.

Activity Status

Segment records into three buckets: (1) Active — currently in a pipeline; (2) Silver medalist — strong past candidates worth re-engaging; (3) Inactive and expired — no business justification for migration. Only buckets one and two belong in your new ATS.

This scoping decision directly affects your migration timeline and data quality outcomes. A well-scoped migration of 3,000 clean records outperforms a bloated migration of 15,000 messy ones every time.

Step 3 — Cleanse and Standardize All Source Data

This is the most labor-intensive step and the one most frequently shortchanged. Every hour invested here prevents approximately ten hours of post-launch cleanup. The sequence within this step matters:

Deduplicate First

Use email address as the primary unique identifier. Cross-reference against phone number and full name for candidates who have changed email addresses. Merge duplicate records manually — automated deduplication tools create false negatives when names are slightly misspelled or when a candidate applied with two different email addresses in two different years. A human must make the final call on ambiguous cases.

Research from Parseur’s Manual Data Entry Report indicates that manual data entry carries an error rate high enough that organizations processing significant candidate volumes should expect meaningful duplicate rates in legacy spreadsheet data. Deduplication is not optional.

Standardize Field Formats

Date fields must be in a consistent format (YYYY-MM-DD is safest for imports). Phone numbers need a single consistent format. State names should be abbreviated consistently. Job titles should follow a controlled vocabulary if your ATS uses them for search and matching. Every inconsistency that crosses the import boundary becomes a query failure or a reporting anomaly.

Resolve Freeform Text Fields

Identify every column that contains mixed, unstructured data. Decide — as a business decision, not a technical one — how to split or reclassify that data into the structured fields your ATS supports. This cannot be automated. It requires the same recruiter or HR manager who participated in the audit.

Fill Critical Blanks or Flag Records for Exclusion

Determine which fields are required for ATS import (the vendor template will specify these). Records missing required fields must either be completed or excluded from migration. Do not import records with blank required fields — they will surface as errors in every report the system generates.

Step 4 — Build Your Field-Mapping Document

Field mapping is the bridge between your source data and your destination system. It is also the step most commonly delegated to people who don’t understand its business consequences. Do not let that happen.

Create a field-mapping document with three columns: Source Spreadsheet Column | ATS Field Name | Transformation Required. Work through every column in your source inventory. For each one, answer three questions:

1. Which ATS field does this map to?
2. Does the data need to be transformed to fit the ATS field’s format or controlled vocabulary?
3. If there is no matching ATS field, where does this data go — a custom field, a notes section, or does it get excluded?

Pay particular attention to fields that your automation workflows will depend on. If you plan to trigger automated follow-up sequences based on candidate stage, the stage field must be mapped and populated with values that match your ATS workflow triggers exactly. A typo in a stage value breaks the automation.

This document also serves as your verification checklist in Step 6 and your audit trail for compliance purposes. Keep it version-controlled and date-stamped.

Proper field mapping is foundational to the broader ATS-HRIS integration and automated data flow you’ll build after go-live — every integration depends on fields being named and structured consistently.

Step 5 — Run a Pilot Migration on 5–10% of Records

Never run a full migration as your first import. Select a representative sample of 5–10% of your total record count. Make this sample deliberately difficult: include your oldest records, records with non-ASCII characters in candidate names, records with the longest notes fields, and a mix of all candidate stages. The goal is to find every class of import error before it affects your full dataset.

After importing the pilot sample, verify the following:

• Every source field landed in the correct ATS field — spot-check using your field-mapping document
• Date fields parsed correctly and are displaying in the expected format
• Resume attachments are linked to the correct candidate records and are accessible
• Special characters in names and addresses rendered correctly (encoding errors are common)
• Candidate stage values match your ATS workflow triggers
• No records that were excluded from scope made it into the import

Document every error you find, trace it to its root cause in the source data or the mapping document, and fix it before proceeding. If the pilot surfaces a class of error affecting more than 5% of pilot records, that class of error is present at scale in your full dataset and must be resolved in the source before the full import.

Step 6 — Execute the Full Migration

With a clean pilot behind you, you’re ready for the full import. Three non-negotiable rules for this step:

Freeze Source Data Before You Begin

Create a locked, timestamped backup of every source spreadsheet the moment you begin the full migration. Revoke edit access so no recruiter can update a spreadsheet while the import is in progress. If the migration fails mid-process, this backup is your rollback point. Without it, a failed import leaves you with a partially populated ATS and a source dataset that may have been modified, making reconciliation nearly impossible.

Schedule a Maintenance Window

Run the full import during off-hours when recruiter activity in both the old and new systems is minimal. This prevents candidate records from being updated in source spreadsheets simultaneously with the import.

Monitor the Import in Real Time

Most ATS platforms generate an import log with success and error counts. Watch it. Do not walk away and check back in the morning. If the error rate spikes above your pilot baseline in the first 10% of records, stop the import, diagnose, and restart rather than completing an import you’ll need to undo.

Review the ATS data security considerations for your talent pipeline before and during this step — data in transit and access controls during migration are a common compliance gap.

Step 7 — Verify and Validate Post-Import

The import completing without errors is not the same as the migration succeeding. Verification is a distinct, structured process.

Record Count Reconciliation

The number of records in your ATS after import should equal the number of records in your migration-scoped source data (minus any records the system rejected). Account for every record. Unexplained discrepancies must be investigated before go-live.

Random Sample Audit

Select 20–30 records at random and manually compare every field against the corresponding source spreadsheet row. This is the only way to catch systemic mapping errors that the import log won’t flag — for example, a date field that imported without errors but landed in the wrong ATS field.

Recruiter Acceptance Testing

Before declaring go-live, have two or three recruiters run their most common search queries in the new system and compare results against what they would expect based on their knowledge of the candidate pool. If a search for “Java developer, 5+ years experience, interviewed in 2024” returns zero results when it should return twelve, something is wrong with field mapping or data standardization — and a recruiter will catch it faster than any technical audit.

Tracking the right indicators after this step goes live is critical. Our guide on post-go-live ATS metrics to track covers what to measure in the first 90 days.

Step 8 — Decommission Source Spreadsheets and Enforce the Single System of Record

This step is the one most organizations skip or delay — and it’s the reason ATS investments underperform. As long as recruiters have write access to source spreadsheets, they will use them. The moment they use them, the ATS is no longer the system of record, your data bifurcates, and every report and automation built on ATS data becomes unreliable.

Decommissioning is a policy decision, not just a technical one. It requires leadership to explicitly communicate that source spreadsheets are now read-only archives, that all new candidate data flows exclusively into the ATS, and that updating a spreadsheet instead of the ATS is a process violation.

Practical decommissioning steps:

• Archive locked copies of all source spreadsheets in a read-only folder with a clear naming convention (“ARCHIVED — DO NOT EDIT — [Date]”)
• Revoke edit permissions for all users
• Update any automation workflows, job board integrations, or application forms that were previously routing data to spreadsheets — redirect them to the ATS
• Communicate the change to all recruiting staff with a clear cutover date and a single point of contact for questions

The Asana Anatomy of Work research consistently finds that workers spend a disproportionate share of their time duplicating work that already exists in another system. Parallel spreadsheet maintenance is a textbook example of that waste. Eliminating it is not just good data hygiene — it’s an operational efficiency gain that compounds every week.

How to Know It Worked

A successful ATS data migration produces four measurable outcomes within 30 days of go-live:

1. Record count matches scope. The number of active candidates visible in the ATS matches your pre-migration scope document, with all exclusions accounted for.
2. Search results are trusted. Recruiters run queries without defaulting to spreadsheets to “double-check.” Trust is behavioral — if anyone is still opening old files to verify ATS results, the migration has a problem.
3. Automated workflows fire correctly. Any stage-based triggers, follow-up sequences, or integration pushes to your HRIS are executing without manual intervention. See our coverage of building a future-proof automated talent pipeline for what those workflows should look like.
4. Source spreadsheets have zero new edits. Check file modification timestamps 30 days post-go-live. Any spreadsheet with a modification date after cutover reveals a process compliance issue that needs immediate correction.

Common Mistakes and How to Avoid Them

Mistake: Migrating everything without a retention cutoff

Fix: Define your cutoff date in Step 2 and enforce it. A bloated ATS with ten years of dead records is harder to search, harder to maintain, and harder to audit than a clean, scoped dataset.

Mistake: Treating field mapping as a technical task

Fix: HR leadership owns the field-mapping document. IT and the ATS vendor execute it. The business decisions embedded in mapping — which fields matter, how to split freeform data, what controlled vocabulary to use — cannot be delegated to people who don’t run recruitments.

Mistake: Skipping the pilot migration

Fix: The pilot exists to surface error classes before they affect 100% of records. Teams that skip it because they’re confident in their cleansing process routinely discover in full-import review that they missed an entire category of problem. The pilot is cheap insurance.

Mistake: Leaving source spreadsheets editable after go-live

Fix: Decommissioning is a leadership decision communicated as policy, not a technical setting. Both are required. Revoke edit access on cutover day and communicate why.

Mistake: Confusing import completion with migration success

Fix: Verification (Step 7) is a separate, structured process. Import logs confirm records were processed — they do not confirm fields landed correctly. Random sample audits and recruiter acceptance testing are the only reliable verification methods.

What Comes After: Building Automation on a Clean Foundation

A successful migration is the foundation, not the finish line. With clean, structured candidate data in your ATS, you can build the automation layer that justifies the entire investment: stage-based communication sequences, automated interview scheduling, resume parsing pipelines, and HRIS sync workflows. Gartner research consistently highlights that talent acquisition functions that invest in foundational data infrastructure before deploying automation see faster time-to-value from those downstream tools.

The compounding effect is real. Clean data makes AI-assisted candidate matching accurate. Accurate matching reduces time-to-hire. Reduced time-to-hire lowers cost-per-hire. Every automation you add to a clean ATS builds on a foundation that makes the next one more effective. Every automation you add to a dirty ATS adds complexity to a problem that only grows.

Review the ATS automation ROI metrics that should improve in the 90 days after a successful migration — and use them to build the business case for the next phase of your automation program.