Census Files Without the Grind: Automating Payroll Imports for 401(k) TPAs

The census file problem every TPA knows by heart

Ask any 401(k) TPA or recordkeeper where their operations time goes and the answer is remarkably consistent: census intake. Every plan sponsor client runs payroll somewhere different (ADP, Paychex, Gusto, Paylocity, QuickBooks, or a homegrown export from an ERP), and every one of those systems produces a census file with its own columns, its own date formats, and its own ideas about what an employee ID looks like. Before eligibility can be determined, before contributions post, before year end testing can run, someone has to turn that file into your format.

For most TPAs that someone is a plan administrator or an operations analyst working in Excel: renaming columns, fixing dates, chasing missing hire dates, and emailing the sponsor about the three participants whose Social Security numbers do not parse. Multiply by every client, every payroll cycle, and every year end, and census processing quietly becomes one of the largest cost centers in the business. This is the same pattern we described in data onboarding for HR tech, with one difference: for a TPA, the file IS the product input, not a side channel.

Why census files resist standardization

• The sender is not technical: The file comes from a payroll admin or an HR generalist at the sponsor. Sending them a 14-page census template does not change what their payroll system exports. They will send what the system gives them, every time.
• Formats multiply by provider and by version: An ADP Workforce Now export differs from ADP Run, which differs from what the same client sent last year before they switched providers mid-plan-year.
• The data has legal weight: Eligibility, vesting, HCE determination, ADP/ACP testing, and Form 5500 filings all sit downstream of census accuracy. A silently wrong date of hire is not a typo, it is a compliance exposure.
• Recurring, not one-time: Beyond the annual census, contribution files arrive every payroll cycle, often by SFTP or email, and each needs the same mapping treatment. Our SFTP automation guide covers why that recurring channel deserves its own pipeline.

What automated census intake looks like

1. The sponsor sends whatever their payroll system exports, through whatever channel they already use: email attachment, SFTP drop, or portal upload. No new template, no training the client.
2. The pipeline recognizes the sender and applies their learned mapping: column names, date formats, ID conventions, compensation definitions. The first file trains it; every later file flows through automatically.
3. Validation runs at intake, not at testing time: missing hire dates, negative compensation, duplicate SSNs, and eligibility-impacting gaps are flagged the moment the file arrives, following data validation best practices.
4. Errors go back to the sponsor immediately with row-level detail they can actually fix, instead of a vague email three weeks later.
5. Clean census data lands in your recordkeeping or administration system through your existing API or import format.

The compounding effect is what matters: each client's format is learned once. The second census from the same sponsor, and every per-payroll contribution file in between, processes without anyone touching it. Year end stops being a fire drill because the intake work happened continuously all year. We walked through the same transformation for a client onboarding workflow in our EQORefer case study.

How FileFeed fits

FileFeed is the intake pipeline described above: it watches SFTP and email, maps each sponsor's census and contribution files to your schema (learning the format from the first file), validates against your rules, and delivers clean data to your system. Your administrators review exceptions instead of retyping spreadsheets, and new clients onboard in days instead of weeks.