How to Import Employees from CSV and Excel

Updog Importer matches a file header to one of your fields, and a cell value to one of your options. Both work on the strings in front of them. A staff export carries one column those two tools cannot reach. The manager is written as a person's name, your schema wants a staff number, and the person holding that number is a row further down the same file.

That column is the whole job.

The file that arrives

Three properties export staff, three payroll systems produce the file, and the group sends all of it as one roster.

1Staff No,Given Name,Family Name,Email,Team,Reports To,First Day,Hrs/Week2A-0001,Marta,Okafor,[email protected],Front of House,Ines Duarte,2019-07-03,23.83A-0002,Priya,Raman,[email protected],Housekeeping,Marta Okafor,2022-04-22,34.24A-0003,Tomasz,Wieczorek,[email protected],Maintenance,Marta Okafor,2026-02-10,23.75A-0004,Ines,Duarte,[email protected],Reception,Marta Okafor,2023-09-27,28.86A-0005,Fiona,Eriksen,[email protected],Housekeeping,Ines Duarte,2019-11-23,27.77A-0006,Grigor,Fahey,[email protected],House Keeping,Priya Raman,2023-03-11,188A-0007,Hana,Gallardo,[email protected],Front of House,M. Okafor,03/04/2026,28.1⋮4 rows not shown13A-0012,Mira,Lindqvist,[email protected],Reception,"Okafor, Marta",2025-03-31,16⋮5 rows not shown19A-0018,Tevita,Rasmussen,[email protected],Front of House,Dana Whitfield,2021-05-30,27.3⋮5 rows not shown25A-0024,Emeka,Eriksen,[email protected],FOH,Priya Raman,2025-08-14,19.726A-0025,Fiona,Fahey,[email protected],Front Desk,Priya Raman,2023-01-05,26.4⋮4 rows not shown31A-0030,Kwame,Kovac,[email protected],Housekeping,Ines Duarte,2019-12-24,36.232A-0031,Liesel,Lindqvist,[email protected],Maint.,Ines Duarte,2024-09-19,30.3⋮4 rows not shown37A-0036,Rafael,Quinlan,[email protected],House Keeping,Ines Duarte,03/04/26,28.4⋮5 rows not shown43A-0042,Callum,Delaney,[email protected],Housekeeping,Tomasz Wieczorek,46085,32.6⋮4 rows not shown48A-0047,Hana,Ivanova,[email protected],Housekeeping,Ines Duarte,2023-03-13,34.6⋮5 rows not shown54A-0053,Marta,Okafor,[email protected],Front of House,Tomasz Wieczorek,2026-01-06,24.9⋮5 rows not shown60A-0059,Ulla,Abara,[email protected],House Keeping,Marta Okafor,4 March 2026,18.1⋮1 rows not shown62A-47,Hana,Ivanova,[email protected],Front of House,Priya Raman,2020-09-05,35⋮10 rows not shown73A-0072,Mira,Ostrowski,[email protected],Front of House,Marta Okafor,17/03/2026,26.9⋮23 rows not shown97A-0096,Rafael,Tanaka,[email protected],Reception,Marta Okafor,2019-05-11,24.9

Line 7 writes the housekeeping team as House Keeping. Line 8 writes the manager as M. Okafor and the start date as 03/04/2026. Neither line is broken. Both disagree with the line above them.

The schema it becomes

Eight fields, and every one of them holds a rule the file can break.

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

const TEAMS = [
  "Front of House",
  "Back of House",
  "Housekeeping",
  "Maintenance",
  "Reception",
];

export const columns: DataEditorColumn[] = [
  {
    id: "staffNumber",
    title: "Staff number",
    validators: [{ type: "required" }, { type: "unique" }],
    transformer: (v) => String(v ?? "").trim().toUpperCase(),
  },
  {
    id: "legalFirstName",
    title: "First name",
    validators: [{ type: "required" }],
  },
  {
    id: "legalLastName",
    title: "Last name",
    validators: [{ type: "required" }],
  },
  {
    id: "contactEmail",
    title: "Email",
    size: 240,
    validators: [{ type: "email" }, { type: "unique" }],
  },
  {
    id: "team",
    title: "Team",
    editor: { type: "select", options: TEAMS, enableCustomValue: false },
    validators: [{ type: "required" }, { type: "oneOf", values: TEAMS }],
  },
  { id: "reportsTo", title: "Reports to", size: 200 },
  {
    id: "firstDay",
    title: "First day",
    editor: { type: "date" },
    validators: [{ type: "date", max: "2027-12-31" }],
  },
  {
    id: "weeklyHours",
    title: "Hours a week",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, max: 48, decimalPlaces: 1 }],
  },
];

primaryKey is staffNumber, so a second upload of the same roster lands on the rows these people already hold. TEAMS closes the team vocabulary, and enableCustomValue: false stops the person inventing a sixth team while they fix their rows.

Where the file and the schema disagree

Seven kinds of disagreement run through the 96 rows, and each one lands in a different part of the importer.

The team column

Value matching scores every imported spelling against the five options and keeps the best one at 60 or above. The score comes off a fixed ladder. Equal after normalizing is 100, a synonym 90, one string held inside the other 80, half the words shared 70, a spelling close enough to correct 65. A spelling that reaches none of those rungs earns a fraction of the words it shares, which lands it far under the threshold. This roster spells the five teams eleven ways, and six of those miss the schema spelling.

That budget comes off the longer of the two strings. Four characters allow one edit, eight allow two, fifteen allow three, and anything longer allows four, so Housekeeping at twelve letters carries three to spend.

Front Desk and FOH fall under the threshold, so nothing is mapped, and the cell reaches the grid empty. Every built-in rule except required passes an empty value, which means { type: "oneOf" } on its own says yes to a person with no team. { type: "required" } turns that silent gap into a row the person has to fix. { type: "oneOf" } earns its place on the rows you load from your own backend, where a value can arrive off the list.

Three-letter abbreviations are the case you close by hand, because containment refuses to match a string shorter than four characters. The synonyms prop takes both halves of the vocabulary.

<DataEditor
  columns={columns}
  primaryKey="staffNumber"
  synonyms={{
    columns: {
      staffNumber: ["staff no", "staff #", "payroll ref"],
      reportsTo: ["reports to", "line manager", "supervisor"],
    },
    values: {
      "Front of House": ["foh", "front"],
      "Back of House": ["boh", "back"],
      Housekeeping: ["hk", "hskp"],
    },
  }}
/>

columns feeds header matching and values feeds option matching, and the two tables never cross. After that table lands, FOH reaches Front of House on the synonym tier at 90. Front Desk shares one word out of three with Front of House and nothing with the other four, so it stays unmatched and its cell stays empty.

The first day column

The importer samples the first thousand rows of the column and offers them to every date shape it knows. Each shape reads what it can, and the one that read the most values takes the column. This roster writes 91 of its 96 dates as 2019-07-03, so YYYY-MM-DD wins ninety-one to two.

YYYY-MM-DD    91   ← reads the column
DD/MM/YYYY     2
MM/DD/YYYY     1
DD/MM/YY       1
MM/DD/YY       1
D MMM YYYY     1
serial         1

The winner reads alone. A value it cannot parse keeps the text it arrived as, and the date rule flags it.

2026-03-04    →  2026-03-04
03/04/2026    →  03/04/2026, flagged
17/03/2026    →  17/03/2026, flagged
4 March 2026  →  4 March 2026, flagged
03/04/26      →  03/04/26, flagged
46085         →  46085, flagged

Line 8 holds 03/04/2026, which is 3 April in one office and 4 March in another. The column already reads YYYY-MM-DD, so that cell keeps its text and reaches the person who knows which day it means. Ninety-one rows written the same way carry the column, and five land in the grid to be read by hand.

A locale settles the column only when two shapes read the same number of values. The browser's own order goes first, and the column reports the tie through onError. An Excel serial takes a column only when every value in it is a serial, so 46085 among 95 written dates stays a number.

The same roster saved as a workbook reads back as 91 cells typed as dates, four typed as text, and one typed as a number.

Excel cell        stored   reaches the grid as
typed as a date   43649    2019-07-03
General format    46085    46085

A cell Excel knows is a date arrives as ISO text before any importer logic runs, whatever format the sheet shows it in, so the serial never appears. A cell somebody left on General format is a plain number, the importer keeps the digits it displayed, and the date rule flags them. Line 43 of this roster is that cell.

The manager written as a name

Reports To holds Marta Okafor, Okafor, Marta, and M. Okafor, and the schema wants A-0001. Nothing in column mapping or value matching answers that, because the answer lives in the other rows.

{ type: "asyncFunction" } is the one rule handed whole rows. When a file opens, the importer sweeps the column and calls your function once. The sweep carries one entry per cell that holds a value and picked up no other error, and each entry hands you the cell value and the full row it sits in. That is enough to build the index out of the file itself, with no request to your backend.

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

const normalizeName = (value: string): string => {
  const trimmed = value.trim();
  const inverted = trimmed.match(/^([^,]+),\s*(.+)$/);
  const straight = inverted ? inverted[2] + " " + inverted[1] : trimmed;
  return straight.toLowerCase().replace(/\s+/g, " ");
};

export const reportsToColumn: DataEditorColumn = {
  id: "reportsTo",
  title: "Reports to",
  size: 200,
  validators: [
    {
      type: "asyncFunction",
      fn: async (cells) => {
        const byName = new Map<string, string[]>();
        for (const { row } of cells) {
          const name = normalizeName(
            row.legalFirstName + " " + row.legalLastName,
          );
          const holders = byName.get(name) ?? [];
          holders.push(String(row.staffNumber));
          byName.set(name, holders);
        }

        return cells.map(({ value }) => {
          const holders = byName.get(normalizeName(String(value)));
          if (!holders) {
            return { level: "error", message: "No row carries that name" };
          }
          if (holders.length > 1) {
            return {
              level: "error",
              message: "Two rows carry that name, " + holders.join(" and "),
            };
          }
          return null;
        });
      },
    },
  ],
};

Every Reports To in this roster holds a name, so the sweep carries all 96 cells and comes back with these verdicts.

Normalizing turns Okafor, Marta into marta okafor on line 13, the lookup succeeds, and the answer is still an error, because two people in this file carry that name. A staff number is the thing that separates them, and the file never wrote one.

Twenty-nine of the 96 rows carry an error on this column. One duplicated person broke every plain reference to her. M. Okafor is the other shape, and it stays unresolved on purpose. Matching works on strings, and deciding that an initial means Marta Okafor guesses at a person.

A reference that points at a row pointing back is a loop, and a row naming itself is the same bug written shorter. Walk the resolved pairs once the index exists and both fall out.

Create, update, or reject

The primary key decides what happens to each row.

staff number already in your data   →  update
staff number new                    →  create
staff number twice in one file      →  both rows land, unique flags them

An import anchors against rows that came from somewhere else, so two rows inside the same file never merge into each other. unique is the rule that catches an in-file duplicate.

The importer compares keys as trimmed strings and nothing more, which is why this roster carries A-0047 on line 48 and A-47 on line 62. Same person, two spellings, two keys, two rows. The transformer on staffNumber is where you normalize the padding before it becomes a key.

Email catches what the staff number missed. This file holds exactly two duplicate addresses, and they mean opposite things.

The importer reports both as duplicates and stops there. Which one is a merge and which one is a real collision is a question about your business, and the person holding the file can answer it.

The rows the person fixes

The empty team cell, the unreadable dates, and the unresolved managers all land in the grid as cell errors before anything reaches your API. The person sees their file with the problem cells marked, and works through a list that reads in their own vocabulary.

Fixing line 26 means picking a team from the five. Fixing line 13 means writing a staff number the file already holds. Both are edits the person can make and you cannot.

What Updog Importer does not ship

The index your function builds covers the cells the sweep handed it. A person who left their own Reports To empty stays out of that sweep, so nobody in the file can resolve to them. Every row in this roster names a manager, so the index stays whole.

One sweep covers every file up to 5,000 rows. Past that the importer sweeps once per chunk, and a cell already carrying an error stays out of the next sweep. The index shrinks with every pass, so a row that resolved against the first chunk can come back an error once the second one lands. For a roster of a few thousand people the mechanism is exact, and for a file larger than that the resolution belongs after submit.

A value shorter than four characters cannot match by containment, so FOH needs the synonyms prop. Normalizing keeps a #, which costs a header the exact and containment tiers. Emp # shares no word with Staff number and reaches nothing, while Staff # still lands on the column at 70, on the one word it shares. One file column cannot fan out into two fields, so a Name (Last, First) header scores 20 against both name columns and reaches neither.

Updog Importer has no view of your database, it runs no lookups against it, and it never learns whether A-0047 already exists in your system. Everything here happens in the browser against the file, and reaching your data is your code.

Reaching your backend

onComplete fires once with every source the person imported, and every row carries isNew, isChanged, isDeleted, and isValid.

<DataEditor
  columns={columns}
  primaryKey="staffNumber"
  onComplete={async (result) => {
    const rows = result.sources
      .flatMap((source) => source.rows)
      .filter((entry) => entry.isValid);

    const response = await fetch("/api/staff", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify(rows.map((entry) => entry.row)),
    });

    if (!response.ok) {
      throw new Error("Staff import failed with " + response.status);
    }
  }}
/>

Throw when your request fails. The editor clears itself when the promise resolves, so a handler that swallows its own error reads as success and takes the person's work with it. Throwing keeps every row on screen and lets them submit again.

What you built

A schema of eight fields, a closed team vocabulary with a synonym table behind it, a date column that takes its order from the file, a duplicate rule that tells a merge apart from a collision, and one column resolved against the other rows of the same file.

The last one is the part the person with the file cannot fix by reading it. A name is not an identity until something says which row it points at.