How to Map CSV Values to Enums During Import

A museum writes the status of a loan in the words its registrar uses. The application behind the register holds four.

Requested   Approved   Declined   Returned

One line of this register says Not approved. It imports as Approved.

The characters of Approved sit inside Not approved, and a matcher built on characters cannot see the word in front of them. The loan was refused, the row says the loan was granted, and the file gave the importer everything it needed to get this wrong.

The register lists objects out on loan. One row is one object, and three of its columns carry a closed list.

1Object no.,Object title,Lender,Condition,Loan status,Handling notes⋮7 rows not shown9MLR-00008,View of a Weaver,Ivelet House,Fragile,Not approved,Crate only⋮4 rows not shown14MLR-00013,Interior of a Standing Figure,Rowan & Vale Estate,Stable,Rejected,No flash⋮8 rows not shown23MLR-00022,Study of Two Sisters,Ashgrove Trust,Stable,Denied,No flash; Gloves⋮7 rows not shown31MLR-00030,Interior of a Weaver,Pellworth Collection,Unstable,RET,Gloves⋮14 rows not shown46MLR-00045,View of Two Sisters,Pellworth Collection,Un-stable,Approved,Two-person lift⋮20 rows not shown67MLR-00066,Sketch of Two Sisters,Halstow Foundation,In treatment,Returned,No flash⋮27 rows not shown95MLR-00094,Sketch of the Old Mill,Pellworth Collection,Fragile,Returned,Gloves; flash⋮22 rows not shown118MLR-00117,View of a Harbour,Halstow Foundation,Fragile,Returned,Gloves; no photography⋮23 rows not shown142MLR-00141,View of a Winter Field,Marden Bequest,Fragile,Approved,Gloves; Keep upright⋮44 rows not shown187MLR-00186,Study of a Reading Room,Marden Bequest,Under treatment,Approved,Crate only

Line 9 writes its status as Not approved and line 14 writes Rejected. Line 23 writes Denied, line 31 writes RET. Line 46 hyphenates its condition as Un-stable and line 67 shortens it to In treatment. Three handling cells carry a second instruction the list has no option for, on lines 95, 118 and 142.

What the importer decides

The register holds 186 rows over six columns. Condition, Loan status and Handling notes map onto option lists, which puts 558 cells in front of the matcher.

Updog Importer collects the distinct values of each of those columns and decides each one once. Condition holds 6 distinct values, Loan status holds 8, and Handling notes splits into 7 tokens. The whole file comes down to 21 decisions, and one of them settles the 111 rows reading Stable.

Updog Importer merges the distinct values of every file in one upload, so a decision made here holds for every row carrying that value anywhere in the batch. Header matching works the other way. It solves a board of headers against fields all at once, and the column step is where that happens.

Values reach the screen as the file wrote them. Un-stable on line 46 and the Unstable on line 31 hold two of the six rows the condition column draws, and they land on the same option. The screen asks for the work twice.

Handling notes writes two instructions into one cell. Updog Importer tries a comma, a semicolon, a pipe, a newline and a tab against the column and keeps the one splitting the most cells into known options. This file settles on the semicolon, and its 19 distinct cells become 7 tokens. The delimiter prop on the column fixes the character when you already know it.

How a value finds an option

Updog Importer normalizes a value before it compares it. It lowercases the string and drops whitespace, underscores, hyphens and dots. Un-stable becomes unstable, which is the whole reason line 46 lands on the right option. Digits, accents and every other punctuation mark stay.

Then it scores the value against each option and keeps the highest. Five tiers can produce a winning score.

The word tier reads the original text, splitting it on those same characters and comparing whole words. The other four read the normalized form.

The edit budget grows with the longer string. One edit up to four characters, two up to eight, three up to fifteen, four beyond that. The winning option has to reach 60, so a value scoring under that reaches no option at all.

The synonym tier carries a vocabulary the SDK ships with, covering gender, seniority, employment type, status, marital status, priority and yes or no. Y and TRUE reach an option named Yes on that tier. A column coded 1 and 0 reaches nothing, because a bare digit shares no characters, no words and no synonym group with Yes or No.

Three of the five tiers can put a value on an option nobody wrote.

Where a tier lands on the wrong option

The register writes nine values outside its option lists. Each one is scored against its own column.

Two of the nine land where a registrar would put them. Three reach nothing. Four land on an option this file never meant.

Not approved and flash fail the same way. The option contains the value or the value contains the option, and containment holds no opinion about the not and the no in front. Rejected and Requested are two real words three edits apart, which is inside the budget for a string of that length. no photography shares the word no with No flash, and one word out of two is half.

Denied and Keep upright share no word with any option and sit outside the edit budget, and RET is three characters, one short of the floor the containment tier holds.

None of this is a parsing failure. Every one of those nine values is a suggestion the matcher can defend, and four of them are wrong about the world.

What lands in the row

Run those lines through the import and read what the row holds.

line 9     file  Not approved             row  loanStatus "Approved"
line 14    file  Rejected                 row  loanStatus "Requested"
line 23    file  Denied                   row  loanStatus ""
line 31    file  RET                      row  loanStatus ""
line 95    file  Gloves; flash            row  handling ["Gloves", "No flash"]
line 118   file  Gloves; no photography   row  handling ["Gloves", "No flash"]
line 142   file  Gloves; Keep upright     row  handling ["Gloves"]

Updog Importer writes a select value to the row only when it maps. Lines 23 and 31 leave the cell empty, and every built-in rule except required passes an empty value, so oneOf on its own says yes to a loan with no status. The employee import carries the same gap, and required turns it into a row somebody has to fix.

A multiselect cell splits into tokens and each token maps on its own. Line 142 holds two instructions and arrives with one, and the array around it stays non-empty, so a rule that reads the cell finds one good option and reports nothing. Line 118 arrives with an instruction about flash photography that the file never carried.

What you declare

Three columns declare their lists, and the two select columns close them.

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

const CONDITION = ["Stable", "Unstable", "Fragile", "Under treatment"];
const STATUS = ["Requested", "Approved", "Declined", "Returned"];
const HANDLING = ["Gloves", "Two-person lift", "No flash", "Crate only"];

export const columns: DataEditorColumn[] = [
  { id: "objectRef", title: "Object reference", validators: [{ type: "required" }] },
  { id: "title", title: "Title" },
  { id: "lender", title: "Lender" },
  {
    id: "condition",
    title: "Condition",
    editor: { type: "select", options: CONDITION, enableCustomValue: false },
    validators: [{ type: "required" }, { type: "oneOf", values: CONDITION }],
  },
  {
    id: "loanStatus",
    title: "Loan status",
    editor: { type: "select", options: STATUS, enableCustomValue: false },
    validators: [{ type: "required" }, { type: "oneOf", values: STATUS }],
  },
  {
    id: "handling",
    title: "Handling",
    editor: { type: "multiselect", options: HANDLING, delimiter: ";" },
  },
];

enableCustomValue: false makes the column a strict list, so the person maps every off-list value to an existing option and creates none. Leaving it at its default lets them create an option for a value the list is missing, which is the right setting for a vocabulary you expect to grow. oneOf checks membership, required catches the empty cell, and delimiter: ";" fixes the splitting character on the handling column.

None of that repairs Not approved. The synonyms prop does, by putting the register's vocabulary on the synonym tier at 90, above the containment tier that was getting it wrong.

<DataEditor
  columns={columns}
  primaryKey="objectRef"
  synonyms={{
    values: {
      Declined: ["Not approved", "Denied", "Rejected"],
      Returned: ["RET"],
    },
  }}
  onComplete={handleComplete}
/>

              before          after
Not approved  Approved        Declined
Rejected      Requested       Declined
Denied        nothing         Declined
RET           nothing         Returned

The values table feeds option matching and the columns table feeds header matching, and the two stay apart, so an alias learned inside a dropdown never competes for a column. Every entry you add merges with the built-in vocabulary.

Once the person has settled the values, the row is ready for a database holding the same list. A PostgreSQL enum type rejects what the browser let through, and settling those four values here keeps that rejection from arriving after the person has closed the tab.

What the person sees

The match step draws one row per distinct value. The imported text sits on the left, and a dropdown of the column's options sits on the right holding the proposal. This register builds 21 of those rows.

A row reaching no option is marked, and its icon carries a warning. A row holding a proposal is drawn the same way whatever tier produced it, so the Not approved row and the Stable row look alike. The score stays inside the matcher.

A column opens closed once every one of its values has found an option. Condition does that here, because all six of its values landed somewhere. Loan status and Handling open on the strength of the three values that reached nothing, which puts 15 of the 21 rows on screen. A column whose wrong proposals all scored above 60 hides itself the same way, and Not approved is on screen only because two of the values beside it failed.

Rows with a proposal are visible inside an open column, and the person can hide them and work the unmatched list alone. Above 500 distinct values in one column, the step draws three rows, says how many the column holds, and puts the rest behind a drawer. That many distinct values in a dropdown column points at a column of free text that reached the wrong field.

Every correction that changes the machine's answer comes back through onComplete under learnedSynonyms, as a source and a target. Group them by target, feed them in through synonyms on the next mount, and the register maps itself the second time.

Where matching stops

No tier reads meaning. Not approved scores 80 against Approved because those characters are there, and a matcher built on characters will keep saying so. The same holds for every option list carrying a word and its negation, and for two options sitting inside each other's edit budget.

An importer can propose, show the proposal, and make it one click to change. Everything past that needs the vocabulary in the synonyms table, or a person on the screen, or your own onValueMatch deciding the column with knowledge the file does not carry.

Line 9 of this register sits one dropdown away from the truth. It reaches the person as a finished proposal, and they are the only reader who knows the loan was refused.