CSV Import for Payroll and HR SaaS

Every employer keeps a list of its people. It sits in an HRIS, it sits in the payroll system, and it sits in the spreadsheet HR builds each time somebody asks a question the HRIS cannot answer. Onboarding an employer onto a new platform starts with one of those files.

That file is a census. It carries names, start dates, pay, cost centres, managers and benefit elections, and every one of those fields is written the way the source system writes it. Pay arrives annual in one export and hourly in the next, and a manager is named in one file and referenced by employee ID in another.

An HR, payroll or benefits platform keeps one employee schema. Its customers export employees from Workday, BambooHR, Personio and from systems built in house, and each of those writes a different file. Moving those files into your schema is customer data onboarding.

You write the schema. The person who uploads the file is the HR administrator at the employer that sends it, and the file came out of their HRIS.

What your customers send

Four kinds of file arrive at an HR platform.

An employee roster is the whole population, one row per person, exported when a customer moves onto your product. A payroll register arrives every pay period and carries earnings against each person. A benefits census is the roster plus everyone the employee covers, and it is the widest of the four. A carrier eligibility export is the census after a broker has reshaped it.

The rest of this article follows two benefits census files that land in the same upload. One employer sends eighteen dependent columns, and the other sends twelve under headers of its own.

The file that arrives

Three rows out of the first census, 640 people at a food manufacturer. The system that exported it stores dependents as numbered columns.

1Emp #,Last,First,Birth Date,Hire Dt,Status,FLSA,Pay Basis,Annual Comp,Work Email,Mgr Email,Coverage,Dep 1 Name,Dep 1 Rel,Dep 1 DOB,Dep 2 Name,Dep 2 Rel,Dep 2 DOB,Dep 3 Name,Dep 3 Rel,Dep 3 DOB,Dep 4 Name,Dep 4 Rel,Dep 4 DOB,Dep 5 Name,Dep 5 Rel,Dep 5 DOB,Dep 6 Name,Dep 6 Rel,Dep 6 DOB2004182,Ferreira,Nuno,3/7/68,02/14/2019,FT,EX,Year,"$72,500.00",[email protected],[email protected],EE+SP,Ana Ferreira,SP,09/11/1971,,,,,,,,,,,,,,,3004183,Okonkwo,Grace,1984-05-30,08/02/2021,Part Time,NE,Hour,31.40,[email protected],[email protected],EE,,,,,,,,,,,,,,,,,,4004184,Salas,Miguel,24905,11/30/2015,FT,EX,Year,68 400,[email protected],[email protected],FAM,Rosa Salas,Spouse,04/02/1980,Leo Salas,Child,07/19/2012,,,,,,,,,,,,⋮636 rows not shown641004821,Whitcombe,Deborah,1990-02-11,05/06/2023,FT,EX,Year,"$58,900.00",[email protected],[email protected],EE,,,,,,,,,,,,,,,,,,

EX is an FLSA code. EE+SP is a coverage tier. 24905 is a date of birth that spent time in a spreadsheet cell. Each value is correct inside the system that wrote it. Your schema stores a status, a tier and an ISO date, so all three arrive in the wrong shape.

The fields the platform keeps

The schema is yours, and the option lists come from names those systems publish. BambooHR publishes employmentStatus as Contractor, Full-Time, Part-Time, Terminated, or a value the customer adds. It publishes exempt as the FLSA overtime status, and paidPer as Hour, Day, Week, Month, Quarter and Year. The coverage tier takes the federal enrollment types. 5 CFR 890.302 defines Self Plus One as the enrollee and one eligible family member, Self and Family as everyone eligible, and Self Only as the third.

import type { DataEditorColumn } from "@updog/data-editor";

const TIERS = ["Self Only", "Self Plus One", "Self and Family"];

export const columns: DataEditorColumn[] = [
  {
    id: "memberId",
    title: "Member ID",
    size: 120,
    validators: [{ type: "required" }, { type: "unique" }],
  },
  { id: "lastName", title: "Last name", validators: [{ type: "required" }] },
  { id: "firstName", title: "First name", validators: [{ type: "required" }] },
  {
    id: "dateOfBirth",
    title: "Date of birth",
    editor: { type: "date" },
    validators: [{ type: "date", max: "2026-07-19" }],
  },
  { id: "hireDate", title: "Hire date", editor: { type: "date" } },
  {
    id: "employmentStatus",
    title: "Employment status",
    editor: {
      type: "select",
      options: ["Full-Time", "Part-Time", "Contractor", "Terminated"],
    },
    validators: [
      { type: "oneOf", values: ["Full-Time", "Part-Time", "Contractor", "Terminated"] },
    ],
  },
  {
    id: "flsaStatus",
    title: "FLSA status",
    editor: { type: "select", options: ["Exempt", "Non-exempt"] },
  },
  {
    id: "payBasis",
    title: "Pay basis",
    editor: {
      type: "select",
      options: ["Hour", "Day", "Week", "Month", "Quarter", "Year"],
    },
  },
  {
    id: "annualComp",
    title: "Annual comp",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, decimalPlaces: 2 }],
  },
  {
    id: "workEmail",
    title: "Work email",
    size: 240,
    validators: [{ type: "email" }, { type: "unique" }],
  },
  {
    id: "coverageTier",
    title: "Coverage tier",
    editor: { type: "select", options: TIERS, enableCustomValue: false },
    validators: [{ type: "oneOf", values: TIERS }],
  },
];

memberId declares no editor, so the value lands as the file wrote it and 004182 holds its zeros. The date and number editors carry a check of their own kind, so a value the importer could not convert is flagged before you declare any validator.

What breaks, field by field

02/14/2019 puts fourteen second, so this census reads month first and every date column in it follows that one order. The next employer's 14.02.2019 sends its file the other way. Every numeric pattern wants a four-digit year, so 3/7/68 reaches the grid as text, and the Excel serial 24905 lands as text too. The date validator flags both cells for the person to fix. The general version of this list is in common CSV import errors.

The headers this industry sends

Updog scores each header against the column id and the column title, and the higher score wins. Exact is 100. A synonym group is 90. One string containing the other is 80. Half the words shared is 70. A typo inside the edit distance is 65. Anything under 60 reaches nothing.

The built-in table already carries this vocabulary. dob, birthdate, hiredate, empstatus, workerstatus, supervisor and mgr are aliases the SDK ships, so most of the file lands with no configuration. Status scores against two of your columns, and the assignment gives it to employmentStatus, because taking the higher total leaves FLSA its own column.

The second employer sends the same census in other words. Employee ID reaches memberId on one shared word. Surname and Given Name land on synonyms. Overtime Status reaches flsaStatus. Email (Work) reaches workEmail. Tier sits inside coverageTier. Paid Per and Base Salary reach nothing.

The wide dependent block

A benefits census stores dependents across the row, one group of columns per dependent. Six groups of three columns is eighteen headers. The next employer sends four groups in different words.

One column of the three matches on its own. Dep 1 Name shares two words of three with your Dependent 1 name title and lands at 70, and every block after it behaves the same way. Dep 1 Rel shares one word of three and reaches nothing. Dep 1 DOB reaches nothing either, because the synonym group behind dob fires only when both strings normalize into it, and dep1dob does not.

const blocks = [1, 2, 3, 4, 5, 6];

const dependentAliases = Object.fromEntries(
  blocks.flatMap((n) => [
    ["dependent" + n + "Relationship", ["dep " + n + " rel", "dep " + n + " relation"]],
    ["dependent" + n + "DateOfBirth", ["dep " + n + " dob", "dependant " + n + " dob"]],
  ]),
);

export const synonyms = {
  columns: {
    memberId: ["emp #", "employee no", "payroll id"],
    payBasis: ["paid per", "pay frequency basis"],
    annualComp: ["base salary", "annual rate"],
    ...dependentAliases,
  },
  values: {
    "Self Only": ["ee", "e", "emp only", "single"],
    "Self Plus One": ["ee+sp", "ee+1", "employee + spouse", "employee + child"],
    "Self and Family": ["fam", "e+f", "ee+fam", "employee + family"],
    Exempt: ["ex"],
    "Non-exempt": ["ne", "nonex"],
    Year: ["annual", "annually", "salaried"],
  },
};

The table is generated because the headers follow a pattern. Each block gets the two entries the matcher misses. A file carrying four blocks maps four and leaves the other two blocks empty. Column matching runs once per file, so the second employer's Dependant 1 First Name reaches the field the first employer's Dep 1 Name reached.

The coverage tier column

Three enrollment types arrive as eleven distinct strings.

Four of the eleven resolve on their own and seven need the table. Value matching runs once for the whole upload, so two employers uploaded together produce one merged list and one decision per string.

The other three enum columns cost less. FT, Part Time and Term all reach Full-Time, Part-Time and Terminated on the built-in table, so employment status needs nothing. Exempt and Non Exempt land on their own, and the payroll shorthand EX and NE takes one entry each. Year and Hour land, and Annual takes one.

Waived belongs to no enrollment type, and a select value is imported only when it is mapped. Leave enableCustomValue at its default and the person can create an option for it. Set it to false, as the schema above does, and the column stays a closed enum where an unmapped value is dropped before it reaches your API.

The manager email column

A manager address has to be answered against the other rows in the file, and a function validator sees one value and one row. That answer lives in the async rule, which receives every cell in the column together with its row.

import type { DataEditorColumn } from "@updog/data-editor";

export const supervisorColumn: DataEditorColumn = {
  id: "supervisorEmail",
  title: "Manager email",
  size: 240,
  validators: [
    { type: "email" },
    {
      type: "asyncFunction",
      fn: async (cells) => {
        const inFile = new Set(cells.map((cell) => cell.row.workEmail));
        const unresolved = cells
          .map((cell) => String(cell.value ?? ""))
          .filter((email) => email && !inFile.has(email));
        const onRecord = new Set(await api.managersOnRecord(unresolved));

        return cells.map((cell) => {
          const email = String(cell.value ?? "");
          if (!email || inFile.has(email) || onRecord.has(email)) return null;
          return { level: "error", message: "No employee holds that address" };
        });
      },
    },
  ],
};

Opening a file sweeps the column at once, so cells carries the manager addresses with the row each one sits in. Empty cells stay out of the sweep, so an employee who reports to nobody contributes no address to the set. The census answers most of the question by itself, and whatever it cannot place goes to your endpoint. The unique rule on workEmail runs last, once every other validator on that column passes, so a malformed address reports the format problem it has.

The next import

Open enrollment brings the census back with last year's rows inside it. primaryKey="memberId" merges an imported row into the row it matches and adds the rest as new. A row whose key is empty merges with nothing.

<DataEditor
  columns={columns}
  primaryKey="memberId"
  synonyms={synonyms}
  blockSubmitOnError
  onComplete={async (result) => {
    for (const source of result.sources) {
      const created = source.rows.filter((r) => r.isNew && r.isValid);
      const updated = source.rows.filter((r) => !r.isNew && r.isChanged && r.isValid);
      await postCensus(source.sourceName, { created, updated });
    }
    await saveSynonyms(result.learnedSynonyms);
  }}
/>

onComplete returns one entry per file, each row carrying isNew, isChanged, isDeleted and isValid, and learnedSynonyms carrying the pairs the person fixed by hand. Turn those pairs into aliases under their targets and the next census matches itself, a loop written up in how to remember CSV import mappings.

What Updog does not ship

Updog has no HR template, no HRIS connector and no per-industry schema library. The columns above, the validators, the aliases and the enum are code you write. Updog Importer reads CSV, TSV, JSON, XML, XLSX, XLS, XLSB and ODS. A census that arrives as a PDF or a scan goes to a parser you supply, and the rows it hands back walk the same path. Everything HR-shaped above sits on top of that as configuration.

What you built

You wrote twelve columns, one aliases table for the headers the matcher misses, one value table for the coverage enum, and one async rule for the manager column. A census that carried three date shapes and eleven ways of writing three coverage tiers reaches your API as rows your database accepts, with everything the importer could not settle flagged for the person who sent the file.