Excel Import for Ecommerce and PIM Software

A catalog is never finished. A merchant adds products through the season, edits prices in bulk in a spreadsheet, and exports the whole thing whenever it has to move somewhere else. What comes out is the catalog plus every decision the old platform made about how to write it down.

The largest of those decisions is what counts as a product. One item in seven sizes is one product to the merchandiser, and the file has to hold it as several rows that belong together.

Six rows in every seven of a merchant catalog arrive with no title. The vendor cell beside them is empty, and so is the category. Those rows are variants, and the file is correct.

A commerce or PIM platform keeps one product record. Its customers export catalogs out of Shopify, WooCommerce, a PIM or a spreadsheet somebody maintains by hand. Moving those into your schema is customer data onboarding.

You write the schema. The person who uploads the catalog is the merchandiser at the merchant, and the file came out of whatever they sell on.

The catalog below is an apparel export. 28,000 products in seven sizes each, so 196,000 rows over twelve columns, 8.6 MB of .xlsx. Every number in this post comes from running that file through the importer once.

Six rows in seven arrive with no title

Shopify tells merchants to write variants that way. Its own CSV documentation says to fill every field on the first row, then "enter the URL handle" on the rows below it and "skip the Title, Description, Vendor, and Tags columns".

1URL handle,Title,Vendor,Product category,SKU,Barcode,Option1 name,Option1 value,Price2cirque-hooded-fleece-slate-00001,"Cirque Hooded Fleece, Slate",Ridgeline Outfitters,Apparel & Accessories > Clothing > Outerwear,RO-00001-XS,5.065E+12,Size,XS,$18.003cirque-hooded-fleece-slate-00001,,,,RO-00001-S,5.065E+12,,S,$19.994cirque-hooded-fleece-slate-00001,,,,RO-00001-M,5.065E+12,,M,$20.995cirque-hooded-fleece-slate-00001,,,,RO-00001-L,5.065E+12,,L,$21.006cirque-hooded-fleece-slate-00001,,,,RO-00001-XL,5.065E+12,,XL,$22.007cirque-hooded-fleece-slate-00001,,,,RO-00001-2XL,5.065E+12,,2XL,$23.998cirque-hooded-fleece-slate-00001,,,,RO-00001-3XL,5.065E+12,,3XL,$24.00⋮195992 rows not shown196001linnet-quilted-vest-ember-28000,,,,RO-28000-3XL,5.065E+12,,3XL,$74.00

Nine of the twelve columns, with the barcode as the spreadsheet displays it. One product, seven sizes, seven rows. The handle repeats down all seven and carries the grouping. The row at the bottom is where the file ends, 27,999 products later, and it carries no title either.

WooCommerce writes the same idea with two columns instead. Its Type column holds variable on the parent and variation on each child, and its Parent column holds the parent SKU. Akeneo publishes parent on the product and reads an entity_type of product or product_model per row.

A flat grid holds rows, and a parent is a row like any other. So the schema below leaves title without a required rule. Marking it required would flag 168,000 rows in this file and help nobody. The product-level check belongs on your side, against the handle.

The barcode that reads 5.065E+12

Excel's General format switches to scientific notation at twelve digits. Below that the digits stay on screen.

A GTIN-8 stays legible. A GTIN-12, a GTIN-13 and a GTIN-14 all land above that line. The .xlsx file still holds the number underneath, and the scientific text is only a display format, so Updog reads the stored value instead. 5.065E+12 reaches the grid as 5065000000011.

In this catalog that recovers 185,671 barcodes. It does not recover 6,631 of them.

A leading zero is gone before the file is written. A UPC-12 printed 076543000290 is stored as the number 76543000290, eleven digits, under the scientific-notation line and therefore untouched by the fallback. Nothing in the file records the zero.

Google's product data specification publishes what a GTIN has to be. Eight, twelve, thirteen or fourteen digits, with a correct check digit. Both halves become rules.

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

const GTIN = /^(\d{8}|\d{12,14})$/;

const gtinCheckDigit = (digits: string): number => {
  const body = digits.slice(0, -1);
  let sum = 0;
  for (let i = body.length - 1, weight = 3; i >= 0; i--, weight = 4 - weight) {
    sum += Number(body[i]) * weight;
  }
  return (10 - (sum % 10)) % 10;
};

export const gtin: DataEditorColumn = {
  id: "gtin",
  title: "GTIN",
  validators: [
    { type: "regex", pattern: GTIN.source, message: "8, 12, 13 or 14 digits" },
    {
      type: "function",
      fn: (value) => {
        const digits = String(value ?? "");
        if (!GTIN.test(digits)) return null;
        return Number(digits.at(-1)) === gtinCheckDigit(digits)
          ? null
          : { level: "error", message: "Check digit does not match" };
      },
    },
  ],
};

The regex catches the 6,631 shortened UPCs. The check digit catches one more row, thirteen digits long and therefore past the regex, which a length rule never sees.

required is the only built-in rule that reacts to an empty cell. So the 3,698 products with no barcode at all pass both rules above and reach your API without one.

What the platform stores per variant

Twelve fields. The category arrives as one string and lands in three of them, and the variant axis and its value take two more.

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

export const columns: DataEditorColumn[] = [
  {
    id: "productCode",
    title: "Product code",
    validators: [{ type: "required" }],
  },
  {
    id: "sku",
    title: "SKU",
    validators: [{ type: "required" }, { type: "unique" }],
  },
  { id: "title", title: "Title" },
  gtin,
  { id: "department", title: "Department" },
  { id: "category", title: "Category" },
  { id: "subCategory", title: "Sub category" },
  { id: "variantAxis", title: "Variant axis" },
  {
    id: "variantValue",
    title: "Variant value",
    validators: [{ type: "required" }],
  },
  {
    id: "price",
    title: "Price",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, decimalPlaces: 2 }],
  },
  {
    id: "quantity",
    title: "Quantity",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, decimalPlaces: 0 }],
  },
  {
    id: "weightGrams",
    title: "Weight (g)",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, decimalPlaces: 0 }],
  },
];

The price column carries $18.00 and $1,299.00 in the file and needs no rule of its own. A number column is reduced to its canonical form on the way in, and currency symbols and the file's own grouping come off there.

What the headers reach

Ten of the twelve incoming headers reach a field. Title, SKU and Price land on the name alone, and so does Product category, because Category sits inside it. The other six need the alias table.

export const synonyms = {
  columns: {
    productCode: ["url handle", "handle"],
    gtin: ["barcode", "upc", "ean"],
    variantAxis: ["option1 name"],
    variantValue: ["option1 value"],
    quantity: ["inventory quantity", "stock"],
    weightGrams: ["weight value (grams)", "grams"],
  },
};

Vendor and Status reach nothing, since the schema carries no field for either, and their values stay out of the import. Shopify also renamed its export headers and kept the old ones working, so Handle and Variant SKU arrive from one merchant and URL handle and SKU from the next. Aliases are what make one schema accept both.

Where a catalog export and a product schema disagree

Those failures in their generic form are collected in common CSV import errors. The barcode row is the one settled above. Four more behave in a way worth watching, and each of them has a section below.

Two rows with the same SKU

Uniqueness is relational, so every cell is checked against every other row in the column. Four rows in this catalog carry RO-00001-XS and two carry RO-12858-XS, and all six are flagged, the first occurrence included.

The unique check always runs last. It waits until every other rule on the column passes, wherever { type: "unique" } sits in the array. So a blank SKU reports that it is required, and it never reports that it is a duplicate.

primaryKey="sku" names the same column as the key the import merges on, which is what makes a second upload of this catalog update rows.

No product template ships with this

Updog ships no Shopify or PIM connector, no attribute library and no category taxonomy to start from either. The twelve columns, the GTIN rules and the alias table are code in your repository. Updog Importer reads CSV, TSV, JSON, XML, XLSX, XLS, XLSB and ODS. A supplier price list that arrives as a PDF or a photograph goes to a parser you supply, and the rows it hands back walk the same path.

The parent and child link is the other honest cost. onComplete hands you flat rows, so grouping seven variants back under one product is a loop you write. The handle is the key it groups on.

The category path in one cell

The category arrives as free text with its levels welded together. Your schema wants three fields.

Right-click the column heading, then Transform, then Split. Split by >, into Department, Category and Sub category. A source column can be one of its own targets, so the middle level writes back where it came from. The preview fills before anything is applied.

Apparel & Accessories > Clothing > Outerwear becomes three cells. The 168,000 variant rows carry no category and stay empty, which is what the grouping loop expects.

A column operation covers the column, so all 196,000 rows are walked to fill 28,000 of them. Afterwards the count of rows holding an empty cell drops from 196,000 to about 169,000, and the difference is the product rows that now have every field your schema asked for.

The size your platform spells differently

The file writes 2XL where your catalog spells it XXL. Open find and replace in the filters panel, set Search in columns to Variant value, and the counter reads 28,000, one row per product.

Restricting the search to one column is what keeps 2XL out of the SKU beside it, where RO-00001-2XL carries the same three characters. Replace all rewrites the 28,000 cells as a single operation.

Those column choices move the counts too. With Variant value selected, the rows panel counts errors, changed rows and empty cells inside that column alone. Clear the selection and the counts describe the file again.

A blank quantity and a zero quantity

1,218 rows in this catalog carry no inventory quantity. The number rule on that column leaves every one of them alone, because a number rule reports the values it could not reduce and an empty cell holds no value to reduce.

So the blanks travel all the way to onComplete as empty, and what they mean is your decision. Shopify treats a blank as zero on the way in, and its own documentation says the column applies only to stores with a single location. A merchant selling from two of them has a stock level that column cannot carry.

Add { type: "required" } when the platform needs the person to say which. Leave it off when your API already reads an absent quantity as unknown. The rule is one line either way, and the row it puts in front of the merchandiser is where the question gets asked.

What 196,000 rows cost

The catalog is 8.6 MB and holds 2.35 million cells. Reading it takes 4,424 ms in a worker, so the tab stays live while it happens. Splitting the category column writes three fields across 196,000 rows, 588,000 cells, in 916 ms.

An operation touching 50,000 cells or more is treated as heavy. It shows a progress overlay and walks the rows in chunks of 25,000, so the browser paints between them. The 28,000-cell replace above sits under that line and finishes with no overlay at all.

One million rows of around twenty columns is the practical ceiling the grid is built around, and 196,000 is a fifth of it. The rendering side of that number is written up in a canvas grid that renders a million rows.

What the person sees before submit

Three counts, out of 196,000 rows. 6,631 barcodes that lost a leading zero, one whose check digit does not match, and six rows sharing a SKU with another row. The rows panel lists them under the message each rule carries, so each count is one click away from being the only thing on screen.

Checking the first of them leaves 6,631 rows on screen and hides the other 189,369. The merchandiser types the missing zero back in against the printed carton, or clears the cell so the row travels with no barcode at all, which is what the 3,698 blanks already do.

<DataEditor
  columns={columns}
  synonyms={synonyms}
  primaryKey="sku"
  onComplete={async (result) => {
    const byProduct = new Map();
    for (const source of result.sources) {
      for (const { row } of source.rows) {
        const group = byProduct.get(row.productCode) ?? [];
        group.push(row);
        byProduct.set(row.productCode, group);
      }
    }
    for (const [productCode, variants] of byProduct) {
      await postProduct(productCode, variants);
    }
  }}
/>

Then onComplete hands over 196,000 flat rows, and the loop above turns them back into 28,000 products. Everything the importer could not settle was settled by the person who sent the file, before a single row reached your API.