How to Import Product Catalogs from CSV and Excel

A catalog row carries a product, a variant of that product, and the SKU that names the variant.

Some cells hold the same value down a run of rows, the coffee name and the origin and the roast. The bag size, the grind and the price change on every line. Those repeating cells describe the product, the changing ones describe the variant, and the file draws no line between them.

The SKU is the only one of the three the file names outright. The product gets spelled out again on every row it owns, and it arrives with no id of its own. You decide first where that id comes from, and every product row you write follows from it.

The file that arrives

Marchmont Roastery sells wholesale in the United Kingdom, the euro area and Czechia, and prices each market in its own currency. Their catalog reaches your distributor app as one export.

1Product,Origin,Roast,SKU,Size,Grind,Unit price2Ndaro Peaberry,Kenya,Medium,MR-NDA-250-WB,250g,Whole bean,£16.003Ndaro Peaberry,Kenya,Medium,MR-NDA-1KG-WB,1kg,Whole bean,£52.004Ndaro Peaberry,Kenya,Med,MR-NDA-1KG-FL,1kg,Filter,£52.005Ndaro Peaberry,Kenya,Medium,MR-NDA-5KG-WB,5kg,,£240.006Marchmont Ndaro,Kenya,Medium,MR-NDA-250-ES,250g,Espresso,£16.007Sela Wash,Ethiopia,Light,MR-SEL-250-WB,250g,Whole bean,"18,50 EUR"8Sela Wash,Ethiopia,Light,MR-SEL-1KG-WB,1kg,Whole bean,"58,00 EUR"9Sela Wash,Ethiopia,Light,MR-SEL-1KG-FL,1kg,Filter,"58,00 EUR"10Cerro Alto,Colombia,Medium-Dark,MR-CER-250-WB,250g,Whole bean,"1 250,00 CZK"11Cerro Alto,Colombia,Medium-Dark,MR-CER-1KG-ES,1kg,Espresso,"4 100,00 CZK"12Tarn Hollow Blend,Blend,Dark,MR-TAR-1KG-ES,1kg,Espresso,£49.0013Tarn Hollow Blend,Blend,Dark,MR-TAR-1KG-ES,1kg,Espresso,£49.00

Four coffees fill these twelve rows. Ndaro Peaberry takes five of them, one per bag size and grind, and writes its name, origin and roast out again on every one. The SKU changes on each row, so it names a single bag. No column marks those five rows as one coffee.

Shopify tells merchants to fill every field on the first row, then repeat the URL handle below it and "skip the Title, Description, Vendor, and Tags columns", so the handle carries the grouping. WooCommerce writes a Type column holding variable on the parent and variation on the child, beside a Parent column holding the parent SKU. Google Merchant Center publishes item_group_id, an "ID for a group of products that come in different versions (variants)", and recommends "the parent SKU where possible". The file above carries none of those three columns. Excel import for ecommerce and PIM software measures what a 196,000-row export in the Shopify shape costs to read.

The two entities the file becomes

Your catalog splits into a product and its variants, and this one flat file carries both.

type Product = {
  productRef: string;
  name: string;
  origin: string;
  roast: string;
};

type Variant = {
  sku: string;
  productRef: string;
  bagSize: string;
  grind: string;
  price: number;
  currency: string;
};

No column stands behind productRef. Everything else on Product repeats down the rows of its group, and everything on Variant changes row by row.

What each column has to become

The last row decides the shape of everything else, so it goes first.

The product the file never names

The product name and the SKU prefix can each stand in for the missing id.

Row 6 decides it. MR-NDA-250-ES belongs to the same coffee as the four rows above it, and its Product cell carries a second wording of the name. Group on the name and that espresso grind becomes a fifth product with one variant under it. Group on MR-NDA and it lands where it belongs.

The SKU prefix wins here, because this roaster builds the SKU out of a product part and a variant part. A supplier who numbers variants independently gives you nothing to cut, and the grouping moves back to the name.

The price that carries its own currency

Google Merchant Center states the price format as "Number plus currency (use ISO 4217)" and publishes 15.00 USD as the example. A price typed by a person carries a symbol. Both forms reach one column.

A number column reduces a value on the way in. Currency symbols, percent signs, accounting parentheses and the file's own grouping all come off, and what stays is digits with a dot. It reads a symbol by the Unicode currency-symbol category, so £ and € come off and EUR stays as three ordinary letters.

cell            stored on a number column
£16.00          16.00
18,50 €         18.50
18,50 EUR       18,50 EUR, flagged
1 250,00 CZK    1 250,00 CZK, flagged

Both halves of that table lose something. The two symbol forms parse, and the currency they carried is gone from the row. The two ISO forms keep the currency and reach validation as text a number rule cannot read.

Money belongs in a text column when the currency travels with it. A transformer puts every incoming form into one canonical shape, and a regex rule holds that shape.

const SYMBOLS: Record<string, string> = {
  "£": "GBP",
  "€": "EUR",
  "$": "USD",
};

const MONEY = /^([^\d\s]*)\s*([\d.,\s']+?)\s*([A-Z]{3})?$/;

// The decimal separator is whichever of . and , sits further right.
const canonicalAmount = (digits: string): string => {
  const clean = digits.replace(/[\s']/g, "");
  const decimal = clean.lastIndexOf(",") > clean.lastIndexOf(".") ? "," : ".";
  const group = decimal === "," ? "." : ",";
  return clean.split(group).join("").replace(decimal, ".");
};

export const toCanonicalPrice = (value: unknown): string => {
  const raw = String(value).trim();
  const match = MONEY.exec(raw);
  if (!match) return raw;

  const [, symbol, digits, code] = match;
  const currency = code ?? SYMBOLS[symbol];
  if (!currency) return raw;

  return canonicalAmount(digits) + " " + currency;
};

Each value names its own decimal separator. 1 250,00 groups with a space and splits with a comma, 1,250.00 does the reverse, and the two are the same amount. Whichever of the dot and the comma sits further right is the decimal one, and that settles both.

The transformer runs on every value arriving from outside the editor. A file import, loadData, a remote source and a paste from another app all call it, and a value the person types in the grid reaches the cell untouched. The transformer also runs before validation, so the regex judges the canonical form.

The roast written two ways

Med and Medium are the same roast level, and the file carries both. The roast column is a select with enableCustomValue: false, so the vocabulary is closed to the four levels your catalog uses.

"Medium"      → Medium
"Med"         → Medium
"Medium-Dark" → Medium-Dark
"Light"       → Light

Three of those four match on the name alone. Med reaches Medium through the built-in fuzzy matcher, and the person confirms it on the value screen before any row lands.

A closed select drops a value that reaches no option. The row still arrives with its roast cell empty, so required sits on that column. Without it, a coffee with an unrecognized roast reaches your API with the field blank.

What the schema should look like

Every decision so far lands in one array, and the mount below imports it from ./columns.

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

const ROASTS = ["Light", "Medium", "Medium-Dark", "Dark"];
const SIZES = ["250g", "1kg", "5kg"];
const GRINDS = ["Whole bean", "Filter", "Espresso"];

export const columns: DataEditorColumn[] = [
  {
    id: "name",
    title: "Product",
    validators: [{ type: "required" }],
  },
  { id: "origin", title: "Origin" },
  {
    id: "roast",
    title: "Roast",
    editor: { type: "select", options: ROASTS, enableCustomValue: false },
    validators: [{ type: "required" }],
  },
  {
    id: "sku",
    title: "SKU",
    validators: [{ type: "required" }, { type: "unique" }],
  },
  {
    id: "bagSize",
    title: "Size",
    editor: { type: "select", options: SIZES, enableCustomValue: false },
    validators: [{ type: "required" }],
  },
  {
    id: "grind",
    title: "Grind",
    editor: { type: "select", options: GRINDS, enableCustomValue: false },
    validators: [{ type: "required" }],
  },
  {
    id: "price",
    title: "Unit price",
    transformer: toCanonicalPrice,
    validators: [
      {
        type: "regex",
        pattern: "^\\d+(\\.\\d{1,2})? (GBP|EUR|CZK)$",
        message: "Write the price as 16.00 GBP",
      },
    ],
  },
];

Origin is the one field with no rule on it. A coffee whose origin cell arrives empty still reaches your API, and the roaster fixes it later.

The key that decides the second upload

primaryKey decides what a second upload does. Name the SKU and the second upload of this catalog updates the twelve variants it already wrote.

sku = MR-NDA-1KG-WB   already stored      → update
sku = MR-NDA-2KG-WB   new                 → create
sku = MR-TAR-1KG-ES   twice in one file   → both land, both flagged

Hold three of these variants already, then import the file over them. Two SKUs match and stay in the stored source, ten rows arrive as new, and one row counts as changed, because MR-NDA-250-WB moved from 15.00 GBP to 16.00 GBP. The other matched row rewrote its price with the value it already held, so nothing about it changed.

An import anchors against rows that came from somewhere else, and it skips every row belonging to the file being imported. So the two MR-TAR-1KG-ES rows never merge into each other. Both reach the grid, and { type: "unique" } flags them.

Uniqueness runs last on a column, once every other rule there has passed. A blank SKU reports that it is required and never reports that it is a duplicate.

A supplier file with no SKU column needs a key built from several columns.

primaryKey={["name", "bagSize", "grind"]}

Row 5 shows what that costs. Its Grind cell is empty, and a composite key needs every part filled. One empty part and the key is nothing, so the row matches no stored row and arrives as new. That happens on this upload and on every upload after it, which turns one 5kg bag into a fresh row each time the catalog lands. Prefer the SKU wherever the file carries one.

The rows the person fixes

The person who sent the catalog fixes it before anything reaches your API.

Row 7 stays out of that list. Its price cell arrived as 18,50 EUR, the transformer rewrote it to 18.50 EUR, and the regex passed. Rows 5, 12 and 13 stay, because a missing grind and a repeated SKU are decisions only the roaster can make.

What Updog Importer does not ship

There is no product and variant model in the grid. Updog Importer holds one flat table of rows, and your code builds the second level once the rows are validated. No catalog connector, no attribute library and no taxonomy comes with it.

Uniqueness is the only rule that reads more than one row. A function validator receives the cell value and the row around it, and nothing else, so two rows of one group disagreeing on a product-level value pass every rule you can write. Row 6 carries Marchmont Ndaro where the four rows above it carry Ndaro Peaberry, and the grid reports nothing. Your grouping code picks a winner, and toCatalog below picks the first row of each group.

Padded SKUs are their own problem, and preserving leading zeros covers what a spreadsheet does to 00422 before the file ever reaches you.

The handoff to your backend

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";
import type { Product, Variant } from "./catalog";

type CatalogRow = {
  name: string;
  origin: string;
  roast: string;
  sku: string;
  bagSize: string;
  grind: string;
  price: string;
};

type Catalog = { products: Product[]; variants: Variant[] };

const productRefOf = (sku: string) => sku.split("-").slice(0, 2).join("-");

const toCatalog = (result: DataEditorResult<CatalogRow>): Catalog => {
  const products = new Map<string, Product>();
  const variants: Variant[] = [];

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

      const productRef = productRefOf(row.sku);
      const [amount, currency] = row.price.split(" ");

      if (!products.has(productRef)) {
        products.set(productRef, {
          productRef,
          name: row.name,
          origin: row.origin,
          roast: row.roast,
        });
      }

      variants.push({
        sku: row.sku,
        productRef,
        bagSize: row.bagSize,
        grind: row.grind,
        price: Number(amount),
        currency,
      });
    }
  }

  return { products: [...products.values()], variants };
};

const saveCatalog = async (result: DataEditorResult<CatalogRow>) => {
  const response = await fetch("/api/catalog", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(toCatalog(result)),
  });
  if (!response.ok) throw new Error(await response.text());
};

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

export function CatalogImport({ open, onClose }: Props) {
  return (
    <DataEditor<CatalogRow>
      apiKey={import.meta.env.VITE_UPDOG_KEY}
      open={open}
      onClose={onClose}
      variant="uploader"
      columns={columns}
      primaryKey="sku"
      onComplete={saveCatalog}
    />
  );
}

toCatalog does the work the file could not. It cuts MR-NDA out of each SKU, writes one Product the first time a group appears, splits the canonical price back into an amount and a code, and pushes one Variant per row. It touches no network, so your tests hand it a result object and read the two arrays back.

isValid decides who reaches the arrays. Submit this catalog with the three flagged rows left as they are, and toCatalog returns three products and nine variants. Tarn Hollow Blend disappears, because both of its rows carry the duplicate SKU and neither one is valid. A product survives the handoff only while one of its variants does.

Throw when your backend fails. A handler that swallows its own error reads as success, and the SDK clears the grid with the rows unsaved. A throw keeps every row where it is, so the person can submit again.

You decide next where those two arrays land, and importing CSV into PostgreSQL covers the insert side of it.

What you built

Twelve flat rows become four products and twelve variants, once the person clears the three rows the grid flagged. You chose the SKU prefix as the grouping key, moved money into a text column so the currency survived the trip, closed the roast vocabulary to four values, and named the SKU as the key that makes the second upload an update.

CSV column mapping in React covers the header aliases that get a roaster's own wording onto those seven fields. No matcher can do the grouping for you, because the product the file never named has to be built out of the columns it did.