CSV Import for Insurance Software

A policy register holds one row for every thing that happened to a policy. A policy was written, changed, cancelled, reinstated or audited. All of those rows carry the same thirteen columns.

The columns mean different things on different rows. An expiration date is mandatory on a renewal and correctly empty on a cancellation. A premium is positive on new business and negative on a cancellation. Which reading applies is decided by one cell, and that cell holds the transaction code.

An insurance platform stores one row per policy transaction. Its customers export those rows out of the agency management system they are leaving, as CSV. Taking a customer's book of business into a schema of your own is customer data onboarding.

The file below holds 2,392 rows over thirteen columns, 306 KB of CSV. A schema of required fields, option lists and number bounds imports it with 86 flagged cells. None of the 86 is what is wrong with the file. Every number here came out of that file or out of running it through the importer.

The column that decides what the rest of the row means

Vertafore publishes the policy transaction codes AMS360 writes, twenty-six of them, each three letters. NBS is New Business. RWL is Renew Policy. PCH is Policy Change. XLC is Cancellation Confirmation. REI is Reinstatement. PAB is Premium Audit. NRA is Non-Renewal Notified Agency, and REW is Rewrite.

Nothing inside those three letters says any of that. PCH shares no word with policy change and PAB shares none with premium audit. A matcher working on string similarity reaches them only through a table somebody wrote down.

Your platform stores six kinds of transaction. New business, renewal, endorsement, cancellation, reinstatement and audit. Every code in the file has to land on one of the six, and the rest of the row waits on where it lands.

An expiration date is required on the three kinds that open a policy term. A premium is positive on new business and on a renewal, and it is negative or zero on a cancellation. A commission rate has to agree with the commission amount beside it. Three rules, and all three read the same cell.

One transaction as your platform stores it

Salesforce publishes the InsurancePolicy object for Financial Services Cloud, and its field list is the working vocabulary for this row. Name is "The insurance policy number". NameInsuredId is "The name of the primary insured", required, and a reference to another record. WritingCarrierAccountId is a reference too. LineOfBusiness is a restricted picklist. GrossWrittenPremium is "The total expected premium amount for the insurance policy over the policy period". CommissionPercent is typed as a percent, BillingType publishes Agency Billing and Direct Billing, and EffectiveDate and ExpirationDate carry the term.

Two of those fields are references and the insured one is required. A policy row stays unwritten until the named insured and the writing carrier both resolve to a record you hold.

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

const TRANSACTION_TYPES = [
  "New Business",
  "Renewal",
  "Endorsement",
  "Cancellation",
  "Reinstatement",
  "Audit",
];

const LINES_OF_BUSINESS = [
  "Personal Auto",
  "Commercial Auto",
  "Homeowners",
  "Commercial Property",
  "General Liability",
  "Workers Compensation",
  "Umbrella",
];

const CARRIERS = [
  "Ardsley",
  "Kemwood",
  "Northgate",
  "Pelham",
  "Stanwick",
  "Valebrook",
];

const BILLING_TYPES = ["Agency Billing", "Direct Billing"];

export const columns: DataEditorColumn[] = [
  {
    id: "policyNumber",
    title: "Policy number",
    validators: [{ type: "required" }],
  },
  {
    id: "transactionType",
    title: "Transaction type",
    editor: {
      type: "select",
      options: TRANSACTION_TYPES,
      enableCustomValue: false,
    },
    validators: [
      { type: "required" },
      { type: "oneOf", values: TRANSACTION_TYPES },
    ],
  },
  {
    id: "transactionDate",
    title: "Transaction date",
    editor: { type: "date" },
  },
  {
    id: "effectiveDate",
    title: "Effective date",
    editor: { type: "date" },
    validators: [{ type: "required" }],
  },
  {
    id: "expirationDate",
    title: "Expiration date",
    editor: { type: "date" },
  },
  {
    id: "lineOfBusiness",
    title: "Line of business",
    editor: {
      type: "select",
      options: LINES_OF_BUSINESS,
      enableCustomValue: false,
    },
    validators: [{ type: "oneOf", values: LINES_OF_BUSINESS }],
  },
  {
    id: "insuredName",
    title: "Named insured",
    validators: [{ type: "required" }],
  },
  {
    id: "carrierCode",
    title: "Writing carrier",
    editor: { type: "select", options: CARRIERS, enableCustomValue: false },
    validators: [{ type: "oneOf", values: CARRIERS }],
  },
  {
    id: "billingType",
    title: "Billing type",
    editor: {
      type: "select",
      options: BILLING_TYPES,
      enableCustomValue: false,
    },
    validators: [{ type: "oneOf", values: BILLING_TYPES }],
  },
  {
    id: "writtenPremium",
    title: "Written premium",
    editor: { type: "number" },
    validators: [{ type: "required" }, { type: "number", decimalPlaces: 2 }],
  },
  {
    id: "commissionRate",
    title: "Commission rate",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, max: 100, decimalPlaces: 3 }],
  },
  {
    id: "commissionAmount",
    title: "Commission amount",
    editor: { type: "number" },
    validators: [{ type: "number", decimalPlaces: 2 }],
  },
  { id: "producerCode", title: "Producer code" },
];

The policy number identifies a policy and it identifies no single row. 543 policy numbers cover the 2,392 rows in this file, one policy carries 15 of them, and one number is written by two different carriers. { type: "unique" } on that column flags correct rows, and primaryKey on it merges transactions that have nothing to do with each other. The key that holds is the policy number with the transaction date and the transaction type, and it is distinct on all 2,392 rows here.

The register an agency exports

Wexbourne Insurance Group is an agency moving its book onto your platform. The file is the transaction register printed by the management system they are leaving, sorted by transaction date. A policy's rows are scattered down the file.

EZLynx publishes the CSV template its commission statement import reads. Policy Number, Transaction Type Code, Premium, Comm Amount, LOB Code, Policy Holder Name and Policy Eff Date are the required headers. Agency ID, Producer Subcode, Transaction Date, Comm Percent, LOB Subcode and Policy Exp Date are the recommended ones. The same page tells the person to enter a chargeback or a carrier fee as a negative amount. The headers below are that template, shortened the way an export shortens.

1Policy Number,Trans Code,Trans Date,Policy Eff Date,Policy Exp Date,LOB Code,Policy Holder Name,Carrier,Bill Type,Premium,Comm Percent,Comm Amount,Producer Code2VN551554629,NBS,2022-12-20,2023-01-07,2024-01-07,Commercial Property,"Marchetti Foundry, Co",Valebrook National,A,57639.75,0.15,8645.96,3NS-742994,NBS,2022-12-24,01/05/2023,2024-01-05,Commercial Property,"Ostrander Bakery, LLC",Northgate Specialty Lines,Agency,17455.5,15,"2,618.32",GBY-064SFM54918094,New Business,2022-12-30,2023-01-14,01/14/2024,General Liability,"Nyquist Contracting, Inc",Stanwick Fire & Marine,AB,23714.1,15,3557.12,HFN-045NS-795288,N/B,2023-01-04,2023-01-15,2024-01-15,WORK,Dupree Excavating,NORTHGATE SPEC,DB,"60,177.58",12,"$7,221.31",HFN-046KC9522547,N/B,2023-01-04,01/16/2023,01/16/2024,Personal Auto,Malachy Aldridge,Kemwood Casualty,D,$873.21,0.175,152.81,HFN-047SFM21287702,NBS,2023-01-08,2023-01-23,2024-01-23,WORKERS COMPENSATION,"Kessler Millwork, Inc",Stanwick F & M,Agency Bill,36501.35,17.5,6387.74,TRW-02⋮2385 rows not shown2393KC9522547,RWL,2026-08-03,2026-01-16,2027-01-16,Personal Auto,Malachy Aldridge,Kemwood Casualty,D,901.55,17.5,157.77,HFN-04

Seven rows, and most of the file's trouble is already on the screen. Three spellings of new business. Effective dates written both ways round. A premium carrying a dollar sign and a premium carrying a thousands comma. A commission rate written 0.15 on one row and 15 on the next. WORK and WORKERS COMPENSATION in one column, Stanwick Fire & Marine and Stanwick F & M in another.

Counted over all 2,392 rows, the transaction code column holds 30 distinct strings, the line of business column 29, the carrier column 24, the billing type column 10 and the commission rate column 25. The expiration date is blank on 677 rows. The premium is negative on 561.

Header detection lands on row 1 at a confidence of 0.76 and asks the person to confirm nothing. The parse reports no errors. 403 effective dates are written MM/DD/YYYY against 1,989 written ISO, and the importer settles the ordering once for the file, so all 2,392 reach the store as ISO.

The headers that end in Code and Date

Seven of the thirteen headers reach a field with nothing configured.

Two of this schema's columns end in Code and three end in Date. A header built from those words spreads its score over several columns and commits to none of them.

Trans Code and LOB Code reach nothing, and both of them score 70 against carrierCode and 70 against producerCode. Those two columns go to the headers that match them outright, Carrier and Producer Code, and the two Code headers are left holding a tie they lost. Policy Eff Date, Policy Exp Date and Policy Holder Name each share one word of three with four different columns, which scores 20 and sits under the threshold. Comm Percent reaches nothing at all.

Six headers stay unmapped, and five of them carry the columns every rule in this article reads.

export const synonyms = {
  columns: {
    transactionType: ["Trans Code", "Transaction Type Code", "Tran Type"],
    transactionDate: ["Trans Date"],
    effectiveDate: ["Policy Eff Date", "Eff Date"],
    expirationDate: ["Policy Exp Date", "Exp Date"],
    lineOfBusiness: ["LOB Code", "LOB", "Coverage Code"],
    insuredName: ["Policy Holder Name", "Insured", "Named Insured"],
    writtenPremium: ["Premium"],
    commissionRate: ["Comm Percent", "Comm Pct", "Commission Percent"],
    commissionAmount: ["Comm Amount"],
  },
  values: {
    "New Business": ["NBS", "NB", "New", "BNB"],
    Renewal: ["RWL", "REN", "RWR", "BRW"],
    Endorsement: ["PCH", "END", "Policy Change", "BPC"],
    Cancellation: ["XLC", "XLN", "CAN", "Cancel"],
    Reinstatement: ["REI", "Reinstate"],
    Audit: ["PAB", "AUD", "Premium Audit"],
    "Personal Auto": ["PAUTO", "PPA"],
    "Commercial Auto": ["CAUTO", "BAUTO", "Comm Auto"],
    Homeowners: ["HOME", "HO", "HO3"],
    "Commercial Property": ["PROP", "CPROP", "Comm Prop"],
    "General Liability": ["CGL", "GL", "Gen Liab"],
    "Workers Compensation": ["WORK", "WC", "Work Comp", "Workers Comp"],
    Umbrella: ["UMBR", "UMB", "Comm Umbrella"],
    "Agency Billing": ["A", "AB", "Agency", "Agency Bill"],
    "Direct Billing": ["D", "DB", "Direct", "Direct Bill"],
  },
};

Every string on the left of the values list is published by somebody. The three-letter codes are Vertafore's AMS360 list, WORK and CGL are the ACORD line of business codes Vertafore names for Sagitta, and Agency Bill and Direct Bill are the billing types EZLynx prints. The alias table is the trade's own vocabulary written down once, so the next agency's export lands on the same table.

With those rows in place all thirteen headers map.

The non-renewal that reads as a renewal

Of the 30 distinct transaction codes, 25 reach one of the six options. Five reach nothing and they cover 81 rows. N/B on 50, NRA on 13, REW on 8, Rewrite on 5 and NON RENEW on 5.

N/B is the one worth walking through. Value matching normalizes a value before it compares, and normalizing strips whitespace, underscores, hyphens and full stops. A slash stays where it is. N/B becomes n/b, which is three characters, and the tier that matches on one string containing another refuses anything shorter than four. The alias "NB" never sees it. An alias written "n/b" settles all 50 rows.

NRA and REW are published AMS360 codes and both are three characters, so they fail the same way. Rewrite and NON RENEW name kinds of transaction this schema does not store, and a person has to decide where they go.

Then there is the one that lands. Non-Renewal normalizes to nonrenewal, which contains renewal, and that is a score of 80 without any alias behind it. Seven rows enter the store as renewals. A non-renewal ends a policy and a renewal opens a term, so the value landed on its own opposite. Common CSV import errors walks the failures any file can produce, and this is the shape that leaves no trace.

A value that reaches no option arrives empty, and oneOf passes an empty cell. With oneOf on four columns it flagged nothing at all across the whole file. { type: "required" } on the transaction type is what turns those 81 rows into 81 flagged cells, and it is the only reason the person sees them.

One word, two lines of business

The line of business column holds 29 distinct strings over seven options, and with the alias table all 29 reach one. CGL, GL and Gen Liab land on General Liability. WORK, WC, Work Comp and WORKERS COMPENSATION land on Workers Compensation. Eight strings cover the two auto lines.

Auto is written on 93 rows and it reaches Personal Auto. 25 of those 93 sit on policies whose other rows write BAUTO, CAUTO or Comm Auto, and whose named insured is a business.

Value matching settles one landing per column for the whole upload. The person sees Auto once, picks one option, and the same choice reaches all 93 rows. A string that means two things depending on the row cannot be settled on that screen.

No rule recovers it either. oneOf passes Personal Auto because Personal Auto is an option. The evidence sits in the policy's other rows, and a rule on a cell never reaches them. The code that receives the finished import does reach them, and the last section of this article is that code.

A required rule that is wrong 670 times

The expiration date is blank on 677 of the 2,392 rows. It is blank on all 163 cancellations, on all 102 audits, on 387 of the 970 endorsements, and on 18 of the 81 rows whose code reached no option. It is blank on none of the 490 new business rows, none of the 53 reinstatements, and on 7 of the 533 renewals.

A term has an end date. A cancellation, a premium audit and a mid-term endorsement have none, and the blank cell is the correct value.

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

const TERM_TYPES = ["New Business", "Renewal", "Reinstatement"];

const termNeedsAnEnd: CellValidator = (value, row) =>
  TERM_TYPES.includes(String(row.transactionType)) && !value
    ? { level: "error", message: "A term needs an expiration date" }
    : null;

// on the transaction type column
dependentFields: ["expirationDate", "writtenPremium", "commissionRate"],

// on the expiration date column
validators: [{ type: "function", fn: termNeedsAnEnd }],

The fn receives the cell value and the row it sits in, and it returns a verdict or null. Reading row.transactionType is what makes the rule right on all six kinds of row at once. dependentFields sits on the transaction type and names the three columns to recheck, so a person correcting a code refreshes the verdicts that read it.

The seven rows the conditional rule finds are the seven Non-Renewal rows. Value matching sent them to Renewal, a renewal opens a term, and a term needs an end date. A rule about one column caught a wrong value in another.

When a negative premium is the correct number

561 of the 2,392 premiums are negative, and the sign follows the kind of row.

An endorsement adds coverage on 605 rows and takes it away on 365, and a premium audit corrects a payroll estimate in either direction. Both signs are legal on both kinds of row. A cancellation returns unearned premium, so its number is negative, and that is the chargeback rule EZLynx publishes.

{ type: "number", min: 0 } on this column flags all 561 negative premiums and 560 of them are correct. No pair of bounds is right on six kinds of row at once, so the rule reads the type.

const premiumSignFollowsTheType: CellValidator = (value, row) => {
  const premium = Number(value);
  const type = String(row.transactionType);

  if (type === "Cancellation" && premium > 0) {
    return { level: "error", message: "A cancellation returns premium" };
  }
  if (TERM_TYPES.includes(type) && premium <= 0) {
    return {
      level: "error",
      message: "This transaction cannot reduce premium",
    };
  }
  return null;
};

// on the written premium column
validators: [{ type: "function", fn: premiumSignFollowsTheType }],

Nine cells come back. One is a new business row written -4180.50, one is a cancellation written 2260.00, and the other seven are the non-renewals that arrived as renewals carrying a premium of zero. 531 premiums carry a dollar sign and 720 carry a thousands comma, and every one of them reaches the store as a number.

The rate and the amount disagree

The commission rate column holds 25 distinct strings. 1,700 rows write a whole percent, 15 or 17.5. 463 rows write the same rate as a fraction, 0.15 or .15. 127 rows write it with a percent sign and 102 leave it blank.

The percent sign looks like the problem and is not. 15% parses to 15 and joins the whole percents, so those 127 rows are already correct. The fraction is the problem, and 0.15 is a legal number between 0 and 100. { type: "number", min: 0, max: 100 } passes it and passes 15 beside it.

The commission amount is the witness. Salesforce types CommissionPercent as a percent, so the unit your schema stores is settled, and the amount divided by the premium says which unit the file wrote.

const rateAgreesWithAmount: CellValidator = (value, row) => {
  if (value === null || value === undefined || value === "") return null;

  const premium = Number(row.writtenPremium);
  const amount = Number(row.commissionAmount);
  if (!premium || !amount) return null;

  const implied = (amount / premium) * 100;

  return Math.abs(Number(value) - implied) > 0.5
    ? { level: "error", message: "Rate disagrees with the amount" }
    : null;
};

// on the commission rate column
validators: [{ type: "function", fn: rateAgreesWithAmount }],

461 cells come back, which is the 463 fraction-written rates less the two whose premium is zero.

The guard on the first line is load-bearing. Number("") is 0, so a blank rate implies a rate of zero and disagrees with every amount beside it. Without the empty check the count came back 563, and 102 of those were blank cells doing nothing wrong.

Nothing here knows what an endorsement is

Updog ships no insurance template, no transaction code table, no line of business list and no carrier file. The thirteen columns, the six options, the alias tables, the three conditional rules and the handler are all code you write.

If the register is yours and the destination is yours, this is the wrong tool. The agency management system you already run has an import screen built for its own tables, and it knows what a reinstatement means. Updog Importer is for the file that arrives from somebody else, in their vocabulary, on a day you did not pick.

Updog Importer reads CSV, TSV, XLSX, XLS, XLSB, ODS, JSON and XML. A policy schedule that survives only as a PDF goes through a parser you supply and arrives as ordinary rows. One million rows is a practical ceiling rather than an unlimited claim, and this register is 2,392.

The policies with no beginning

The register is sorted by transaction date, so one policy is six rows in six places. Gathered by policy number, NS-742994 reads as a story.

1Policy Number,Trans Code,Trans Date,Policy Eff Date,Policy Exp Date,LOB Code,Policy Holder Name,Carrier,Bill Type,Premium,Comm Percent,Comm Amount,Producer Code⋮1 rows not shown3NS-742994,NBS,2022-12-24,01/05/2023,2024-01-05,Commercial Property,"Ostrander Bakery, LLC",Northgate Specialty Lines,Agency,17455.5,15,"2,618.32",GBY-06⋮161 rows not shown165NS-742994,PCH,2023-03-28,2023-03-25,,CPROP,"Ostrander Bakery, LLC",Northgate Specialty,AB,-840.81,15,-126.12,GBY-06⋮494 rows not shown660NS-742994,RWL,2023-12-31,2024-01-05,2025-01-04,Comm Prop,"Ostrander Bakery, LLC",Northgate Specialty,Agency,"$18,865.94",15,2829.89,GBY-06⋮51 rows not shown712NS-742994,endorsement,2024-01-31,2024-01-24,2025-01-04,Commercial Property,"Ostrander Bakery, LLC",Northgate Specialty,Agency Bill,"1,234.38",15,185.16,GBY-06⋮221 rows not shown934NS-742994,END,2024-05-28,2024-05-23,2025-01-04,Commercial Property,"Ostrander Bakery, LLC",NORTHGATE SPEC,A,"$1,648.81",0.15,247.32,GBY-06⋮111 rows not shown1046NS-742994,Cancellation,2024-07-29,2024-07-24,,Comm Prop,"Ostrander Bakery, LLC",Northgate Specialty Lines,AGENCY BILL,-8476.75,.15,-1271.51,GBY-06

New business, a policy change that gives premium back, a renewal, two more endorsements and a cancellation. The two rows with no expiration date are the mid-term change and the cancellation, and neither one opens a term. Both negative premiums sit on rows that take coverage away.

Your handler groups the rows this way because a policy is what your platform stores. Once they are grouped, the handler can look for the row that opened the policy. No rule on a cell reaches that far.

type Transaction = Record<string, unknown>;

const CREATING = ["New Business", "Renewal"];

export const policiesWithNoBeginning = (rows: Transaction[]) => {
  const byPolicy = new Map<string, Transaction[]>();

  for (const row of rows) {
    const key = String(row.policyNumber);
    const held = byPolicy.get(key) ?? [];
    held.push(row);
    byPolicy.set(key, held);
  }

  return [...byPolicy.entries()]
    .filter(([, held]) =>
      held.every((row) => !CREATING.includes(String(row.transactionType))),
    )
    .map(([policyNumber, held]) => ({ policyNumber, transactions: held }));
};

543 policies cover the 2,392 transactions, and 28 of them carry no new business row and no renewal row. That is 60 transactions describing policies that never began.

Three of the 28 have no creating row anywhere in the file, so an endorsement arrived for a policy the export left behind. The other 25 have one, and it wrote N/B. One alias missing from the table makes 25 policies look as though they appeared out of nothing, and no cell in the grid says so.

<DataEditor
  columns={columns}
  synonyms={synonyms}
  primaryKey={["policyNumber", "transactionDate", "transactionType"]}
  onComplete={async (result) => {
    const rows = result.sources
      .flatMap((source) => source.rows)
      .filter((r) => r.isValid && !r.isDeleted)
      .map((r) => r.row);

    const orphans = policiesWithNoBeginning(rows);

    await postRegister({ transactions: rows, orphans });
  }}
/>

All of that runs where the person dropped the file, and the rows travel from their browser to your endpoint under your own agreement. Client-side and server-side import follows a file down each of the two routes.

What the handler refuses to write

2,392 rows and thirteen columns make 31,096 cells. A schema of required fields, option lists and number bounds flags 86 of them, and the register still lands wrong. The schema whose rules read the row flags 563.

461 are commission rates written in the wrong unit. 81 are transaction codes that reached no option. 8 are premiums a term row cannot carry, 7 are terms with no end date, 5 are rows with no named insured, and 1 is a cancellation that adds premium.

Every one of them is legible to the person who knows the book. The policy number, the insured name and the transaction code sit in the same row as the flagged cell, and the code says what the row was meant to be.

Then 28 policies reach your handler in a list of their own rather than in the table, because an endorsement needs a policy underneath it. A register printed for a person to read becomes rows your platform can bill.