CSV Import for Healthcare and Patient Data SaaS

A clinic keeps its patient list in two places. One is the EHR the practice bought, and the other is the spreadsheet the front desk keeps beside it for everything the EHR makes hard. When the practice goes live on a new platform, both of them come along.

Nothing in those files is written for a machine. Sex is recorded as F in one export and Female in the next, a date of birth arrives in the American order in one file and the international order in another, and an allergy column holds three allergens in one cell.

A clinic's patient roster arrives with thirteen columns. Your schema has a field for eleven of them. The other two carry a social security number and a list of diagnosis codes, and your product stores neither.

A patient platform stores one record per person. Its customers export that population out of an EHR, or out of the spreadsheet the front desk keeps beside it. Taking those files into a schema of your own is customer data onboarding.

The practice manager at the clinic is the one who uploads the roster, in the week that clinic goes live on your product. The schema it lands in is yours.

The roster below holds 3,200 patients over thirteen columns, 378 KB of .csv. Every count below was measured by running that file through the importer once.

What a clinic hands over

Four kinds of file reach a health platform.

A patient roster is the whole panel, one row per person, sent when a customer moves onto your product. A member eligibility list is a payer's version of that same population, keyed on the member number the payer issued. An employer census carries the people a benefits plan covers, one row per employee and one more per dependent. A provider directory carries the clinicians, with a specialty and an NPI against each name.

Everything below follows one roster.

The row the front desk typed

Six rows out of it, in the spelling the spreadsheet holds.

1MRN,Last Name,First Name,DOB,Sex,Email,Insurance,Member #,Coverage Start,Coverage End,Allergies,SSN,Problem List2204010,Ionescu,Adriana,2011-11-18,F,,United Healthcare,97955138,2025-06-27,2026-09-29,"Latex, Penicillin",900-18-6880,I103204013,Halvorsen,Julien,1931-01-31,M,[email protected],BCBS TX,ZGP4648350,2026-03-06,,NKDA,900-54-1298,E11.9; I10; J45.9094204021,Novotny,Kwame,1944-08-17,M,[email protected],Self Pay,,,,NKDA,900-82-8964,I10; J45.9095204045,Vasquez,Luz,6/16/86,Female,,Medicare,1EG45433874,2023-08-13,,Shellfish,900-40-5476,I10⋮55 rows not shown61204068,Adeyemi,Folasade,1978-04-02,F,[email protected],Aetna,W273441905,2024-11-01,,NSAIDs; Shellfish,900-27-3319,E11.9⋮3139 rows not shown3201209884,Whitlock,Marguerite,1957-09-12,F,[email protected],Cigna,U884120773,2025-01-01,,NKDA,900-61-4470,I10

The allergy cell on row 2 separates two allergens with a comma, and row 3 writes NKDA, the clinic's shorthand for no known drug allergies. Row 61 puts a semicolon between its two allergens, which is how most of the file writes them. 6/16/86 is a birth date whose century nobody wrote down. The self-pay row carries no member number and no coverage dates, because there is no payer behind it.

The eleven fields the platform keeps

One of the three option lists is published and two are yours. HL7 defines administrative gender as male, female, other and unknown, and those four codes are what the sex column stores. The payer list is the set your platform holds contracts with. The allergen list is the vocabulary your clinical team maintains.

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

const SEX = ["male", "female", "other", "unknown"];

const PAYERS = [
  "Aetna",
  "Blue Cross Blue Shield of Texas",
  "Cigna",
  "Humana",
  "Medicare",
  "United Healthcare",
  "Self pay",
];

const ALLERGENS = [
  "Penicillins",
  "Sulfonamides",
  "NSAIDs",
  "Latex",
  "Peanut",
  "Shellfish",
  "Iodinated contrast",
];

export const columns: DataEditorColumn[] = [
  { id: "mrn", title: "MRN", validators: [{ type: "required" }] },
  { 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: "required" }, { type: "date", max: "2026-07-26" }],
  },
  {
    id: "sex",
    title: "Sex",
    editor: { type: "select", options: SEX, enableCustomValue: false },
  },
  { id: "email", title: "Email", validators: [{ type: "email" }] },
  {
    id: "payer",
    title: "Payer",
    editor: { type: "select", options: PAYERS, enableCustomValue: false },
  },
  { id: "memberId", title: "Member ID" },
  { id: "coverageStart", title: "Coverage start", editor: { type: "date" } },
  { id: "coverageEnd", title: "Coverage end", editor: { type: "date" } },
  {
    id: "allergies",
    title: "Allergies",
    editor: { type: "multiselect", options: ALLERGENS },
  },
];

Eleven of the thirteen incoming headers reach one of those fields. Eight of them land on the column name exactly, DOB reaches dateOfBirth on a built-in synonym, and Member # reaches memberId on the one word the two strings share. Insurance reaches nothing on its own and takes an alias.

export const synonyms = {
  columns: {
    payer: ["insurance", "carrier"],
  },
  values: {
    "Blue Cross Blue Shield of Texas": ["bcbs tx", "bcbstx"],
    "United Healthcare": ["uhc"],
    Penicillins: ["pcn"],
    Sulfonamides: ["sulfa drugs", "sulfa"],
  },
};

The value half of the same table carries BCBS TX, UHC, PCN and Sulfa drugs to the options your schema spells out in full. U and X in the sex column reach no code. Those 77 rows arrive with an empty sex until the person maps the two spellings, alongside 48 cells the file left blank.

The two columns you do not map

SSN and Problem List reach nothing, because the schema declares no field for either. HL7's US Core profile says a patient's social security number should not be used as the patient identifier. It points at the medical record number, which is the column your key already reads.

An unmapped header contributes no field to the row. The import plan is built from the headers that carry a column id, so the values under SSN never enter the store, never reach the grid, and never appear in onComplete. The row your API receives holds the eleven fields above and nothing else.

enableCreateColumn={false} is what keeps it that way. Left at its default, the matching step offers the person a way to keep an unmatched header by creating a column for it. A practice manager tidying up a roster can take that offer, and the diagnosis codes travel with it.

The file itself stays where it was dropped. Updog parses it, matches it and validates it inside the tab the person already has open, so a roster full of names and birth dates reaches your endpoint and no other server. The two execution models are compared in client-side versus server-side import.

Where a clinic roster and a patient record disagree

Every failure in that table has a generic twin in common CSV import errors. Four of them behave in a way worth watching here, and each one takes a section below.

The allergy cell holding three values

A multiselect column stores a list, so every cell in this column is split into tokens on the way in. The separator is detected per column. Five candidates are tested against your option list, ,, ;, |, a newline and a tab, and the one that splits the most cells into recognised tokens wins. In this roster 928 cells separate their allergens with a semicolon and 111 use a comma, so ; takes the column. The value-matching step prints it in a field the person can overwrite.

Ten distinct tokens come out of that split, and nine of them find an option. Latex, NSAIDs and Shellfish are exact. Penicillin and Peanuts differ from their options by one letter, and one string containing the other is enough. Contrast dye shares a word with Iodinated contrast. PCN and Sulfa drugs arrive through the alias table.

NKDA reaches nothing, and that is the correct outcome, since no known drug allergies is the absence of an allergen. A multiselect value is imported only when it is mapped, so those 441 cells land as an empty list, alongside the 987 cells that were empty in the file.

The comma cells lose an allergen. Latex, Penicillin is one token once the column splits on ;, and it matches Latex because that option sits inside it. The row reaches your API with one allergen where the clinic wrote two, and 111 rows in this roster do that.

A token the option list does not recognise is split again on a second separator. That second split holds only where every piece it produces is an option, character for character. Latex is one. Penicillin misses, because the schema spells the option Penicillins. Spell the option the way the file writes it and the same cell arrives as two values.

The year written with two digits

384 birth dates in this roster are written 6/16/86. Every numeric date pattern the importer reads wants a four-digit year, so those cells reach the grid as the text the file carried, and the date validator flags all 384.

Nothing in the file records the century. A patient born in 1986 and a patient born in 2086 are one keystroke apart, and the person who sent the roster is the one who can settle which. The other 2,816 rows carry an ISO date and pass without a decision.

The end date that is empty on purpose

2,758 rows carry no coverage end date. 2,514 of them are patients whose coverage is current, and 244 are the self-pay rows, which carry no coverage at all. An empty cell answers to required alone, and this column declares none, so both kinds of row travel.

The 25 rows that do carry a bad end date carry it in relation to the cell beside them.

{
  id: "coverageStart",
  title: "Coverage start",
  editor: { type: "date" },
  dependentFields: ["coverageEnd"],
},
{
  id: "coverageEnd",
  title: "Coverage end",
  editor: { type: "date" },
  validators: [
    {
      type: "function",
      fn: (value, row) => {
        const end = String(value ?? "");
        const start = String(row.coverageStart ?? "");
        if (!end || !start) return null;
        return end < start
          ? { level: "error", message: "Coverage ends before it starts" }
          : null;
      },
    },
  ],
},

dependentFields on the coverage start names the column to check again, so editing a start date clears the verdict left on the end date. An open enrollment is the normal case in this schema, and the rule above says so by leaving every empty cell alone.

An MRN your database already holds

{ type: "unique" } on the MRN column checks every value against every other row in the file. Six rows in this roster share a chart number with a second row, and all six carry the flag, the earliest of each pair included.

The duplicate that matters more is the one the file cannot see.

{
  id: "mrn",
  title: "MRN",
  validators: [
    { type: "required" },
    {
      type: "unique",
      fn: async (values) => {
        const res = await fetch("/api/patients/known-mrns", {
          method: "POST",
          body: JSON.stringify(values),
        });
        return res.json();
      },
    },
  ],
}

The optional fn hands your endpoint the distinct MRNs from the column and takes back the ones you already hold. Seven come back here, and they carry their own message, Already exists in your database, so the person can tell a typo inside the file from a patient your system registered last month.

Your endpoint is asked last. A cell that fails a rule of its own, or repeats another cell inside the file, is held back and never reaches fn, and neither does an empty one. A blank chart number reports that it is required, and your service never sees it.

primaryKey="mrn" names the same column as the key a later upload merges on. That later upload is where the remote check stops being right, since a roster sent again to update patients carries MRNs your database is supposed to hold. Take fn off the column for that flow and let the key do the work.

Everything clinical here is yours to write

Updog ships no healthcare template, no EHR connector and no code lists. The allergen vocabulary, the payer list, the coverage rule and the MRN endpoint are code in your repository, and the four FHIR gender codes are one line of your own. Updog Importer reads CSV, TSV, JSON, XML, XLSX, XLS, XLSB and ODS. A scanned intake form or a faxed referral reaches a parser you supply, and whatever rows it returns enter the same matching step this roster entered.

The 468 rows that need a person

Out of 3,200 rows, 468 carry something a rule caught, and sixteen of those carry two. 384 birth dates with no century, 60 addresses that stop at the @, 25 coverage periods that end before they start, 7 chart numbers your database already holds, 6 that repeat inside the file, and 2 rows missing a name. Each of those messages is its own line in the rows panel, and checking one leaves the rows behind it on screen and hides the rest.

<DataEditor
  columns={columns}
  synonyms={synonyms}
  primaryKey="mrn"
  enableCreateColumn={false}
  onComplete={async (result) => {
    for (const source of result.sources) {
      const ready = source.rows.filter((r) => r.isValid && !r.isDeleted);
      await postPatients(source.sourceName, ready);
    }
  }}
/>

Every row reaches onComplete, and each one carries isValid, so the filter above decides what a row still holding an error becomes. Send those to a review queue, or keep them out of the write and let the clinic upload again.

The practice manager works down the list of flagged rows against the paperwork on their own desk, which is the only place the answers live. Then onComplete hands you 3,200 patient records carrying eleven fields each, and the two columns you never mapped stayed in the file they arrived in.