How to Import Inventory from CSV

Stock sits in more than one place at once. A retailer holds the same jacket in its shops and in its warehouse, a fulfilment company holds one client's goods across two buildings, and a food wholesaler holds the same flour at every site it delivers from. Every one of those pairings carries its own count.

The counting happens away from your app. Someone walks the aisles with a scanner or a clipboard, and the numbers reach you as a file, because the tool that counts and the tool that sells are separate systems. That file is a CSV or a workbook, exported after the count and handed to whoever keeps the system of record.

It writes one line per item per place, so an item code takes as many lines as the places that hold it.

Wistow Provisions supplies bakeries across the north of England and counts stock at three sites. In its file WP-FLR-T55 holds 480 at Wistow Mill, 96 at Ryburn Depot and zero at Fenton Cold Store, all at the same moment. An item code does not name a stock level, and an import that keys on the item alone writes a new row where a new count belongs.

Two fields name the record

type StockLevel = {
  sku: string;
  warehouse: string;
  onHand: number;
  allocated: number;
  reorderLevel: number;
  unit: string;
};

sku and warehouse carry the identity. onHand is what the counter found on the shelf, allocated is what open orders have already claimed, and reorderLevel is where buying starts again. unit says whether the count runs in pieces or in kilograms, and that field decides which counts are valid.

Your stock table holds one unique index over the two columns together. The import has to arrive with the same idea of identity, so primaryKey takes both.

primaryKey={["sku", "warehouse"]}

The file carries both

The counter at Wistow Provisions exports one file after every count, one line per shelf.

1Item code,Site,On hand,Allocated,Reorder at,Unit2WP-FLR-T55,Wistow Mill,480,120,200,each3WP-FLR-T55,Ryburn Depot,96,24,60,each4WP-FLR-T55,Fenton Cold Store,0,0,40,each5WP-FLR-WHM,Wistow Mill,212.5,45,100,each6WP-BTR-25,Fenton Cold Store,12.5,4,10,kg7WP-YST-FRS,Fenton Cold Store,(4),0,20,kg8WP-YST-FRS,Ryburn Depot,30,6,20,kg9WP-SGR-CST,Wistow Mill,640,0,250,each10WP-SGR-CST,,75,0,250,each11WP-CHC-70,Ryburn Depot,144,36,60,each12WP-CHC-70,Ryburn Depot,150,36,60,each13WP-CHC-70,Wistow Mill,60,12,60,each14WP-CHC-70,Fenton,26,0,60,each

Six items fill these thirteen rows, and twelve of the lines write both halves of the pair. An item code comes back further down under a different site, a site comes back under a different item, and neither column names a row on its own.

Row 5 counts a half of something sold in pieces. Row 6 counts a half of something weighed. Row 7 writes its count in brackets. Row 10 leaves the site blank, and row 14 writes a site name short.

Header wording carries the smaller half of the work here. CSV column mapping in React covers the matcher that gets Item code onto sku and Reorder at onto reorderLevel.

Normalize the site before building the key

Row 14 says Fenton. The three sites your app knows are Wistow Mill, Ryburn Depot and Fenton Cold Store. A key built from the file's own spelling would open a fourth site that no warehouse matches.

The site column is a select with enableCustomValue: false, so the list stays closed to those three names.

"Wistow Mill"        → Wistow Mill
"Ryburn Depot"       → Ryburn Depot
"Fenton Cold Store"  → Fenton Cold Store
"Fenton"             → Fenton Cold Store

Three of those match on the name alone. Fenton reaches Fenton Cold Store through the built-in matcher, and the person confirms it on the value screen before any row lands. The importer builds the key from the confirmed option, so row 14 joins the Fenton records and the short spelling never reaches the key.

A closed list drops a value that reaches no option, and the cell arrives empty. That is why required sits on the site column.

A composite key needs every part filled. One empty part and the key is nothing at all, so the row matches no stored record. Row 10 carries a count of 75 for WP-SGR-CST and no site. It stays an unmatched new row in the editor until the person picks a site, and until then it reaches no request. The count is real and the record it belongs to is unknowable, which is work only a person can finish.

The unit decides what number is valid

Row 5 counts 212.5 of a flour sold in pieces. Row 6 counts 12.5 kilograms of butter. Both write a half, and only one of them is wrong.

A fixed decimalPlaces: 0 on the count would refuse them both and tell a warehouse that its butter is a bad number. The rule the domain holds reads two fields at once, so it goes in a function validator, which receives the cell value and the row around it.

if (row.unit === "kg") return null;

dependentFields: COUNTS sits on the unit column and names the three count columns. Change a line from each to kg and the counts beside it are judged again, so a verdict never goes stale behind an edit.

The second rule stays flat, because no unit makes a negative count meaningful. A number column reduces a value on the way in. Spaces, currency symbols and percent signs come off, the file's own grouping character comes off, and accounting brackets around a value make it negative.

cell     stored     verdict
480      480        whole pieces, valid
12.5     12.5       weighed in kilograms, valid
212.5    212.5      a half of something counted in pieces
(4)      -4         out of range

Row 7 writes a loss of four kilograms of fresh yeast as (4). The brackets are read before the digits, so the cell holds -4, and min: 0 rejects it. The two rules carry their own messages, so the person reads a different sentence on row 5 and on row 7.

Every decision lands in one array.

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

const SITES = ["Wistow Mill", "Ryburn Depot", "Fenton Cold Store"];
const UNITS = ["each", "kg"];
const COUNTS = ["onHand", "allocated", "reorderLevel"];

const wholeUnlessWeighed = (
  value: unknown,
  row: DataEditorRow,
): ValidationError | null => {
  if (row.unit === "kg") return null;

  const count = Number(value);
  if (!Number.isFinite(count) || Number.isInteger(count)) return null;

  return {
    level: "error",
    message: "Count whole pieces. A half belongs on a line weighed in kilograms.",
  };
};

const quantity: DataEditorColumn["validators"] = [
  { type: "function", fn: wholeUnlessWeighed },
  {
    type: "number",
    min: 0,
    message: "A count starts at zero. Send a loss as an adjustment.",
  },
];

export const columns: DataEditorColumn[] = [
  { id: "sku", title: "Item code", validators: [{ type: "required" }] },
  {
    id: "warehouse",
    title: "Site",
    editor: { type: "select", options: SITES, enableCustomValue: false },
    validators: [{ type: "required" }],
  },
  {
    id: "onHand",
    title: "On hand",
    editor: { type: "number" },
    validators: [{ type: "required" }, ...quantity],
  },
  {
    id: "allocated",
    title: "Allocated",
    editor: { type: "number" },
    validators: quantity,
  },
  {
    id: "reorderLevel",
    title: "Reorder at",
    editor: { type: "number" },
    validators: quantity,
  },
  {
    id: "unit",
    title: "Unit",
    editor: { type: "select", options: UNITS, enableCustomValue: false },
    dependentFields: COUNTS,
  },
];

editor: { type: "number" } makes those three columns numeric. A column with a number validator and no editor stays text, and a value in brackets never becomes a negative number there. (4) reaches the number rule as text, fails to parse, and carries that rule's own message, so the person reads a sentence about a count starting at zero over a cell that never went negative.

The same key updates the stored row

Stock counts arrive again and again, and the key decides what each arrival does. The person meets that choice once the import has something to merge into, which is either rows already in the grid or a second file in the same upload. The step is called Add or update, and it offers two cards.

Add every row
Every row becomes a new entry, even if it repeats one you already have.

Update by Item code + Site
WP-FLR-T55 + Wistow Mill
WP-FLR-T55 + Ryburn Depot
...
WP-CHC-70 + Fenton

The second card carries the key you declared, titled with the column headings joined by a plus sign. Its examples are the file's own values, twelve pairs for thirteen rows, and the last one still reads Fenton because the site names settle in the step before it. Row 10 is missing from that list, because a row with no site has no key to show.

Hold three records, one of them a count of 18 for WP-CHC-70 at Fenton Cold Store, and import this file over them. The grid lands on fourteen rows, and one of the three moves.

WP-CHC-70 + Fenton Cold Store   18 → 26    isChanged
WP-FLR-T55 + Ryburn Depot       96 → 96    matched, nothing moved
WP-OAT-50 + Wistow Mill         untouched by the file

A matched row keeps the record it matched. The stored row updates in place and holds its id, so nothing downstream sees a delete and an insert. A row counts as changed when a value moves, and the two rows that stood still stay out of the result altogether. isNew and isChanged are what your handler reads to tell the three apart.

The card needs every one of its parts mapped. Drop the site column and the card goes with it. A file with no unique column left loses the step, and an item code repeats by design here, so that rule never sits on it. One file landing in an empty grid skips the step too, and the key you declared still anchors the merge.

Duplicate keys inside one file need another check

Rows 11 and 12 both read WP-CHC-70 at Ryburn Depot, with counts of 144 and 150. An import anchors against rows that came from somewhere else, and skips every row belonging to the file being imported, so those two never merge into each other. Both reach the grid and neither is flagged.

{ type: "unique" } reads one column, and an item code is meant to repeat here, so putting the rule on sku would flag every item held at more than one site. The pair drives the merge and paints no error cell, which leaves rows 11 and 12 valid and sharing one key.

Nothing in the grid knows what a stock level means either. On hand against allocated is a rule your domain owns, and a row claiming 36 allocated against 0 on hand passes every validator on the page. Checking an item code against your catalogue is also yours, and it belongs in an asyncFunction check, batched once per column.

So the duplicate pair reaches your database silently unless you look for it. The handler builds a key over the same two columns the merge uses, walks the valid rows once, and returns every pair that arrived more than once. Joining the two into one string needs a separator no value can carry, so the key here is the two values as an array.

const pairOf = (level: StockLevel) => {
  return JSON.stringify([level.sku, level.warehouse]);
};

Missing is different from zero

A stock file is a statement about the rows it carries. Row 4 sets WP-FLR-T55 at Fenton Cold Store to zero. That is a count, and the record exists with nothing on the shelf behind it.

The pairs the file never mentions carry no count at all. A zero is data, and an omitted row is silence, so the safe reading leaves the stored record alone. State that rule in your API and the counter can send a short file after counting one aisle.

The decision lands in your handler, because the editor hands you the rows it holds. A row loaded from your backend that nobody touched stays out of the result, so silence in the file arrives as silence in the payload.

Submit sends the validated changes

Three rows carry an error, and the grid names each one.

onComplete fires once, with the rows grouped by source. Each row carries isNew, isChanged, isDeleted and isValid, and the flags are independent.

import { DataEditor } from "@updog/data-editor";
import type { DataEditorResult } from "@updog/data-editor";
import { columns } from "./columns";

type StockRow = {
  sku: string;
  warehouse: string;
  onHand: string;
  allocated: string;
  reorderLevel: string;
  unit: string;
};

type StockLevel = {
  sku: string;
  warehouse: string;
  onHand: number;
  allocated: number;
  reorderLevel: number;
  unit: string;
};

const pairOf = (level: StockLevel) => {
  return JSON.stringify([level.sku, level.warehouse]);
};

const toLevels = (result: DataEditorResult<StockRow>): StockLevel[] => {
  const levels: StockLevel[] = [];

  for (const source of result.sources) {
    for (const { row, isValid, isDeleted } of source.rows) {
      if (!isValid || isDeleted) continue;

      levels.push({
        sku: row.sku,
        warehouse: row.warehouse,
        onHand: Number(row.onHand),
        allocated: Number(row.allocated),
        reorderLevel: Number(row.reorderLevel),
        unit: row.unit,
      });
    }
  }

  return levels;
};

const repeatedPairs = (levels: StockLevel[]): string[] => {
  const seen = new Set<string>();
  const repeated = new Set<string>();

  for (const level of levels) {
    const pair = pairOf(level);
    if (seen.has(pair)) repeated.add(pair);
    seen.add(pair);
  }

  return [...repeated];
};

const saveStock = async (result: DataEditorResult<StockRow>) => {
  const levels = toLevels(result);
  const repeated = repeatedPairs(levels);

  if (repeated.length > 0) {
    throw new Error(repeated.length + " item and site pairs arrive twice");
  }

  const response = await fetch("/api/stock-levels", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(levels),
  });
  if (!response.ok) throw new Error(await response.text());
};

type Props = { open: boolean; onClose: () => void };

export function StockImport({ open, onClose }: Props) {
  return (
    <DataEditor<StockRow>
      apiKey={import.meta.env.VITE_UPDOG_KEY}
      open={open}
      onClose={onClose}
      variant="uploader"
      columns={columns}
      primaryKey={["sku", "warehouse"]}
      onComplete={saveStock}
    />
  );
}

Submit this count with the three flagged rows left as they are. Thirteen rows reach the handler, toLevels returns the ten that are valid, and repeatedPairs answers with WP-CHC-70 and Ryburn Depot. The throw that follows keeps the grid open with all thirteen rows and all three errors where they were.

Throw when your backend fails, for the same reason. A handler that swallows its own error reads as success. The SDK clears the grid with the counts unsaved.

The next stock count follows the same rules

Thirteen rows of a stock count become one record per item and site, once the counter clears the three rows the grid flagged and settles which of the two counts for WP-CHC-70 at Ryburn Depot is the real one. You named two columns as the key, closed the site list to three names so the key parts stay comparable, let the unit decide which halves are real, and counted the repeated pairs yourself before the request went out.

Send this count twice in one upload, as the CSV and as the workbook it came from, and the grid lands on fourteen rows. Twelve rows of the second file reach the records the first one made. The thirteenth carries no site, so it holds no key, and it arrives again.

Excel import for logistics and supply chain software covers the files that arrive from the other side of the warehouse, and importing product catalogs covers the item codes themselves. Those levels land in a table with a unique index over the item and the site, and importing CSV into PostgreSQL covers the insert side of it.