CSV Import for CRM and Sales SaaS

A sales team's contact list outlives the software it lives in. It starts in one CRM, gets exported to a spreadsheet whenever somebody needs to work on it away from the app, picks up a column or two by hand, and comes back. Switching CRM means handing all of that to a new product in a single file.

Part of that file is already known to you. Some of those people sit in your database from a form fill or a trial signup, and the file brings a second version of the same person under a different email address.

Every contact list a customer uploads has been through another CRM first, and that system decided how a name is spelled, what a lifecycle stage is called and which day 03/07/2026 falls on. Your schema decided all three differently. Moving those lists into it is customer data onboarding.

The schema is yours. The file belongs to the sales operations administrator at the customer, who exported it out of an old CRM or a mail client.

The contact list, the account list, the lead list

A contact list is the whole address book, one row per person, exported the week a customer moves onto your product. An account list is the companies those people work at, and it arrives as a second file with a shared column. A lead list is neither, a purchased or scraped set of rows with no history behind it.

Everything below follows one contact list of 15,000 rows and the small account file beside it.

What the old CRM wrote

Six rows of that contact list, in the words the system that held it used. It files a person under the family name, so that is the order the name column carries.

1File as,Email 1 - Value,Business Phone,Job Title,Company,Company Domain,Lifecycle,Owner,Last Activity,Source2"Okafor, Chidera",c.okafor@brightwaterdental.com,+44 20 7946 0102,Practice Manager,Brightwater Dental Group,brightwaterdental.com,MQL,r.delacroix@northgate.example,03/07/2026,Web form3Vahid Sarrafzadeh,info@kestrelfreight.co.uk,020 7946 0111,Operations Lead,Kestrel Freight,kestrelfreight.co.uk,Closed Won,j.whitlock@northgate.example,2026-03-07,tradeshow4dr. amara ngozi,info@kestrelfreight.co.uk,+44 20 7946 0111,Director,Kestrel Freight,kestrelfreight.co.uk,Opp,j.whitlock@northgate.example,7.3.2026,Referral⋮83 rows not shown88Priya Raghunathan,info@sablecourt.co.uk,+44 20 7946 0148,Clinical Director,Sable Court Clinics,sablecourt.co.uk,SQL,r.delacroix@northgate.example,2026-02-19,Web form⋮1115 rows not shown1204"Boateng, Kwabena",k.boateng@fennimore.co.uk,020 7946 0163,Head of Facilities,Fennimore Estates,fennimore.co.uk,Churned,j.whitlock@northgate.example,46082,Newsletter⋮13796 rows not shown15001Marta Silveira,m.silveira@hallowfield.example,+44 20 7946 0175,Buyer,Hallowfield Interiors,hallowfield.example,Lead,r.delacroix@northgate.example,2026-03-02,tradeshow

MQL is a lifecycle stage. Opp is another one. info@ is a shared mailbox sitting on two rows that belong to two different people. The system that wrote each of those was right by its own rules. Your schema stores a stage from a fixed list and one row per person, so none of the three lands as written.

The eleven columns your API accepts

The option lists come from vocabularies the sending systems publish. HubSpot publishes eight default lifecycle stages, Subscriber, Lead, Marketing Qualified Lead, Sales Qualified Lead, Opportunity, Customer, Evangelist and Other, and it lets a customer add more. Zoho publishes Last Name as mandatory and caps it at 40 characters, and it caps an email address at 100.

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

const LIFECYCLE = [
  "Subscriber",
  "Lead",
  "Marketing Qualified Lead",
  "Sales Qualified Lead",
  "Opportunity",
  "Customer",
  "Evangelist",
  "Other",
];

export const columns: DataEditorColumn[] = [
  {
    id: "accountDomain",
    title: "Account domain",
    size: 200,
    validators: [{ type: "required" }],
  },
  { id: "accountName", title: "Account name", size: 200 },
  {
    id: "contactEmail",
    title: "Contact email",
    size: 240,
    validators: [{ type: "required" }, { type: "email" }],
  },
  { id: "firstName", title: "First name" },
  {
    id: "lastName",
    title: "Last name",
    validators: [
      { type: "required" },
      {
        type: "function",
        fn: (value) =>
          String(value ?? "").length > 40
            ? { level: "error", message: "Last name is over 40 characters" }
            : null,
      },
    ],
  },
  { id: "jobTitle", title: "Job title", size: 180 },
  { id: "phone", title: "Phone", size: 160 },
  {
    id: "lifecycleStage",
    title: "Lifecycle stage",
    size: 200,
    editor: { type: "select", options: LIFECYCLE, enableCustomValue: false },
    validators: [{ type: "oneOf", values: LIFECYCLE }],
  },
  {
    id: "ownerEmail",
    title: "Owner email",
    size: 240,
    validators: [{ type: "email" }],
  },
  {
    id: "lastActivityDate",
    title: "Last activity date",
    size: 170,
    editor: { type: "date" },
    validators: [{ type: "date" }],
  },
  {
    id: "sourceChannel",
    title: "Source channel",
    size: 170,
    editor: {
      type: "select",
      options: ["Web form", "Trade show", "Referral", "Cold outreach", "Other"],
    },
  },
];

The 40-character rule is a function validator, because that limit belongs to your database rather than to the file.

What your database refuses

Those failures in their generic form are collected in common CSV import errors. Two entries on that list behave in a way worth watching, and both are below.

The second customer's header row

The same list arrives from the next customer under its own words. A header is scored against both the column id and the column title, the better of the two counts, and under 60 nothing is claimed. Run that header row against the schema above and this is what lands.

Read the last four rows together. Organization reaches nothing and leaves an empty column the person can see. Full Name reaches accountName, because both strings carry the word name, and it fills a column that then looks finished.

The name column that lands in the wrong field

A header that reaches nothing announces itself. A header that reaches the wrong field does not. The account name column of that second file holds a person, and every row looks complete on the mapping screen.

The first customer's file has the opposite problem. File as shares no word with any column and contains none of them, so it scores zero and lands nowhere. One export loses the name, the next one hides it.

Both are settled in the same table. Aliases under a column id win at 90, which outranks the 70 that carried Full Name to the wrong place.

export const synonyms = {
  columns: {
    accountName: ["company", "organization"],
    contactEmail: ["email 1 - value", "work email"],
    ownerEmail: ["owner", "assigned to"],
    lastActivityDate: ["last activity", "last touch"],
    lastName: ["file as", "full name", "contact"],
  },
  values: {
    "Marketing Qualified Lead": ["mql", "mkt qualified"],
    "Sales Qualified Lead": ["sql"],
    Opportunity: ["opp"],
    Customer: ["closed won"],
    Subscriber: ["newsletter"],
    Other: ["churned"],
  },
};

file as, full name and contact now reach lastName, and organization and company reach accountName. The value table under it takes the seven lifecycle strings that reach nothing on their own, MQL, mkt qualified, SQL, Closed Won, Newsletter, Opp and Churned. Eight of the fifteen strings in that column land without help, and New Lead reaches Lead because one contains the other.

That leaves the name itself. One column reaches one field, so a combined name lands whole in lastName, and a transformer takes the half that belongs there.

{
  id: "lastName",
  title: "Last name",
  transformer: (value) => {
    const name = String(value ?? "").trim();
    const comma = name.indexOf(",");
    return comma === -1 ? name : name.slice(0, comma).trim();
  },
}

The transformer runs as rows enter the store, so Okafor, Chidera is stored as Okafor and Vahid Sarrafzadeh is stored unchanged. The given name is gone. Recovering it is guesswork the importer refuses to do, so either your schema keeps one name field, or the person fills the second one in the grid.

The date that changes meaning

Updog scans the date columns across the first thousand rows before it decides an order, and the first value with a part above twelve settles it for the whole file.

That makes 03/07/2026 a value with no meaning of its own. Put 02/14/2026 anywhere in those rows and fourteen lands second, so the file reads month first and 03/07/2026 becomes 7 March. Put 14/02/2026 there instead and the file reads day first, so the same cell becomes 3 July. The value 7.3.2026 swaps the other way in each case.

When nothing in those rows settles the order, the fallback is the locale of the machine that opened it. One file can then land on two different days in two different offices, and a 15,000-row export that first disagrees with the locale on row 4,000 lands on the locale's reading.

2026-03-07 carries its order inside it and reaches 7 March every time. An Excel serial carries no order to read, so it lands as text, and the date validator flags it for the person to fix.

Deciding what a row is

Now the identity question. Two rows in that file carry info@kestrelfreight.co.uk, because a freight company put a shared mailbox on both of its people. A third account also publishes an info@ address.

Rows inside one file stay apart whatever the key says. Merging runs against what the editor already holds, so the freight company's two people land as two rows, and the key decides what happens on the upload after this one.

That upload is where the address alone breaks. Next quarter's file carries info@kestrelfreight.co.uk again, this time on a contact filed under another account. Key on the email column alone and the row merges into one of the freight rows, taking that contact out of the import with nothing on the screen to show for it.

primaryKey takes a list, and a row merges only when every listed column matches. Key on the email and the account domain together and that contact arrives as its own row, because the domains differ. Values are compared after trimming, so a padded export still merges with what you hold.

The second upload

Open the same file again a quarter later and most of it repeats. loadData hands the editor the contacts you already store, and the imported rows merge into them by that composite key.

<DataEditor
  columns={columns}
  primaryKey={["contactEmail", "accountDomain"]}
  synonyms={synonyms}
  blockSubmitOnError
  loadData={async (onChunk) => {
    onChunk(await fetchContacts(), {
      id: "crm",
      source: "Already in your CRM",
    });
  }}
  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 postContacts(source.sourceName, { created, updated });
    }
    await saveSynonyms(result.learnedSynonyms);
  }}
/>

onComplete hands back one entry per file with isNew, isChanged, isDeleted and isValid on every row, and a row that arrived from your own data untouched is left out. So the second upload of 15,000 rows reaches your API as the handful that changed. learnedSynonyms carries the pairs the person fixed by hand, as { source, target } entries under columns and values, where the target is the column title for a header and the option value for a value. Store them, add each source as an alias under its target, and the next list matches itself. That loop is written up in how to remember CSV import mappings.

What you write yourself

Every CRM-shaped line above is yours. The columns, the validators, the alias table, the enum and the key live in your repository, because Updog ships no CRM template, no connector to any CRM and no schema library to start from. Updog Importer reads CSV, TSV, JSON, XML, XLSX, XLS, XLSB and ODS. A contact sheet that arrives as a PDF or an image goes to a parser you supply, and the rows it returns walk the same path. The rest is configuration.

The composite key has a price of its own. A row missing either the email or the domain builds no key, so it merges with nothing and arrives as new. That trades a silent wrong merge for a visible duplicate, which is the better failure and still a failure.

What reaches your API

The first upload posts 15,000 contacts. The second posts the rows whose key is new and the rows whose values moved, and leaves the rest of the file out. Behind that sit eleven columns, an alias table, seven lifecycle strings, a transformer on the name column and a key made of two of those columns, all of it code you keep. The two dates the importer refused and the given name it dropped wait in the grid for the person who sent the file.