Excel Import for Logistics and Supply Chain SaaS

Warehouses trade in workbooks. A supplier sends a price list, a 3PL sends a stock report, a customer sends a list of items to set up, and all three arrive as .xlsx with a sheet for each topic. The workbook is how these companies talk to each other.

A workbook meant for a person to open is laid out for reading. It carries a cover block above the table, a tab for items and another for stock, and a units column where each, case and pallet all appear.

Row one of the workbook a supplier sends carries the supplier's name. Row two names the export, row three carries an effective date, row four is empty, and the column headings start on row five.

A warehouse, inventory or 3PL platform keeps one item record. Its customers export items and stock out of a WMS, an ERP or a spreadsheet somebody keeps by hand. Each of those writes a different workbook. Moving them into your schema is customer data onboarding.

You write the schema. The person who uploads the workbook is the operations coordinator at the supplier, and the file came out of whatever they run.

The workbook and its four tabs

Supply chain systems hand their users workbooks. Extensiv tells you to open its import template and "refer to the second worksheet for additional guidance". Oracle puts the button that writes the CSV inside an "Instructions and CSV Generation" worksheet. So a workbook arrives carrying tabs nobody wants.

harbour-lane-items.xlsx
├── Instructions     4 lines of prose in one column
├── Item Master      4,200 items, headings on row 5
├── Stock by Site    11,600 stock lines, headings on row 1
└── Discontinued     empty

Each tab becomes its own card with its own checkbox, labelled with the sheet name and the file it came from. The empty tab arrives unchecked and reads Empty sheet. The prose tab holds four lines, so it counts as data and arrives checked. The person unchecks it before Next.

The two that matter go on as separate units. Column matching runs once per unit, so the screen reads Match columns (1 of 2) and each tab gets its own mapping.

Where the header row actually is

Updog looks for the table first and classifies its top row second. A leading row that fills less than half the sheet's column span counts as preamble. A name, a title and a date stacked above the headings cost nothing.

The first row that clears that bar is scored. The type flip below it is weighted heaviest. The body's consistency, the row's own text, its fullness and its repeats carry the rest. Score 0.55 and above is confident. Under that, the wizard adds a header-selection step and the person picks the row from a preview.

The preamble costs nothing. A missing header row does. A sheet that opens straight into values has no labels to promote. The detector says so and hands the choice to the person. Tick My data has no header row on that screen and every row stays as data under the names Column 1, Column 2 and so on. With two uncertain sheets there are two screens, in upload order, and the sheets that scored well are skipped.

The item tab above scores 0.73 and never reaches that screen. Its 4,200 rows land with the four rows above the headings dropped.

1Harbour Lane Provisions2Item master export3Effective 01 Sep 202645Item,Supplier Ref,Description,Unit,Pack,On Hand,Site,Lot,Origin,Gross Wt (kg)6HLP-1042,884120,Rye crispbread 200g,CS,12/CS,40,LDN1,B7741,SE,0.187HLP-1043,884121,Oat crackers 150g,EA,1/EA,77,MAN2,B7742,GB,0.288HLP-1045,884123,Seeded flatbread 220g,Each,12/CS,151,ldn1,B7744,PL,0.489HLP-1048,884126,Barley crackers 160g,Master Ctn,24/CS,262,GLA1,B7747,United Kingdom,0.7810HLP-1055,884133,Spelt breadsticks 125g,PLT,480/PLT,521,MAN2,B7754,Italy,1.48⋮4194 rows not shown4205HLP-6198,889276,Wholegrain rye loaf 400g,EA,1/EA,94,GLA1,B8397,GB,0.42

CS is a unit of measure. 12/CS is a pack configuration with the unit welded onto it. Master Ctn is a third way of writing the second one. Each value is correct inside the system that wrote it.

The item record on your side

Ten columns, and the option list comes from a vocabulary a platform in your position publishes. ShipHero names four packaging levels. An Each is the base product. An Inner Pack is a box of Eaches. A Master Case holds Inner Packs or Eaches, and a Pallet holds Master Cases or Eaches. ShipHero also publishes Country Of Manufacture as a two-digit country code, so the origin column carries a regex rule.

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

const UNITS = ["Each", "Inner Pack", "Master Case", "Pallet"];

export const columns: DataEditorColumn[] = [
  {
    id: "itemCode",
    title: "Item code",
    validators: [{ type: "required" }],
  },
  { id: "supplierItemCode", title: "Supplier item code" },
  {
    id: "description",
    title: "Description",
    validators: [{ type: "required" }],
  },
  {
    id: "uom",
    title: "Unit of measure",
    editor: { type: "select", options: UNITS },
    validators: [{ type: "required" }],
    dependentFields: ["unitsPerPack"],
  },
  {
    id: "unitsPerPack",
    title: "Units per pack",
    editor: { type: "number" },
    validators: [{ type: "number", min: 1, decimalPlaces: 0 }],
  },
  {
    id: "quantityOnHand",
    title: "Quantity on hand",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, decimalPlaces: 0 }],
  },
  {
    id: "siteCode",
    title: "Site code",
    validators: [{ type: "required" }],
  },
  { id: "lotCode", title: "Lot code" },
  {
    id: "countryOfOrigin",
    title: "Country of origin",
    validators: [
      { type: "regex", pattern: "^[A-Z]{2}$", message: "Two capital letters" },
    ],
  },
  {
    id: "grossWeightKg",
    title: "Gross weight (kg)",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, decimalPlaces: 3 }],
  },
];

Oracle publishes the model under that enum. A unit belongs to a class with a base unit, and a standard conversion says how many base units it holds. So 1 Case (CS) = 24 Each (EA) is a fact about the customer's catalogue, and the string in the cell is a label.

What your warehouse cannot receive

Those failures in their generic form are collected in common CSV import errors. Two entries on that list behave in a way worth watching, and both are below.

When the schema's own names collide

The stock tab writes the same data in the WMS's words. Four of its seven headings end in Code. Four columns of the schema above end in Code too, so the word carries no information.

Matching solves the whole board at once and keeps the highest total. Every arrangement of those four scores the same total. So the answer moves with the order the headings sit in.

A header that reaches nothing leaves an empty column. A header that ties with three others fills one and looks finished. Qty on the same tab reaches nothing, and so does Supplier Ref on the item tab. The person sees both.

The alias table settles all of it. An alias under a column id scores 90, which outranks every tie at 70. Check the built-in table against your own vocabulary first. It carries dob, salary and hiredate, and it carries nothing for SKU, Qty, Warehouse, Batch or COO.

export const synonyms = {
  columns: {
    itemCode: ["item", "product code", "sku"],
    supplierItemCode: ["supplier ref", "vendor part"],
    uom: ["unit", "uom code"],
    unitsPerPack: ["pack", "pack size", "units/case"],
    quantityOnHand: ["on hand", "qty"],
    siteCode: ["site", "whse code", "warehouse"],
    lotCode: ["lot", "batch code"],
    countryOfOrigin: ["origin", "coo"],
    grossWeightKg: ["gross wt (kg)", "net wt"],
  },
  values: {
    Each: ["ea", "ea.", "pc", "piece"],
    "Inner Pack": ["ip", "inner ctn", "inner"],
    "Master Case": ["cs", "ctn", "carton", "mc", "master ctn"],
    Pallet: ["plt", "pal", "plt."],
  },
};

Each, EA and Dozen

Value matching runs once for the whole upload and collects the unit column across both tabs. Nineteen distinct strings arrive and sixteen of them land.

Each, Pallet, Case and Inner land on their own. An exact string is taken at 100, and a value sitting inside an option at 80. Inner Pk and Master Ctn land on a shared word at 70. The values table above carries the rest, so EA, CS, CTN, IP, MC, PLT and PAL reach an option at 90.

Three are left, and they fail for a different reason. BX, DZ and Dozen name packaging your schema does not carry. No alias helps. Either the schema grows a fifth option, or the person picks the nearest one and owns that choice.

A unit nobody mapped arrives as an empty cell. The string the supplier wrote stays behind. In the sample above that leaves 840 rows with no unit, and the required rule on that column puts every one of them in front of the person.

One landing is worth a second look. Case reaches Master Case because one string sits inside the other. Whether the supplier meant a master case or an inner pack is nowhere in the file.

The pack size welded to the unit

The pack column carries 12/CS where your schema wants 12. A number column is reduced to its canonical form first. 12/CS is a shape the importer cannot reduce, so it reaches the transformer whole.

{
  id: "unitsPerPack",
  title: "Units per pack",
  editor: { type: "number" },
  transformer: (value) => {
    const digits = String(value ?? "").match(/^\s*(\d+)/);
    return digits ? digits[1] : value;
  },
  validators: [
    { type: "number", min: 1, decimalPlaces: 0 },
    {
      type: "function",
      fn: (value, row) =>
        row.uom === "Each" && String(value) !== "1"
          ? { level: "error", message: "An Each holds one unit" }
          : null,
    },
  ],
}

12/CS is stored as 12, 480/PLT as 480 and 1/EA as 1. The unit half is thrown away on purpose. That unit already has a column of its own, two fields to the left.

A quantity with no unit behind it

Row 8 of that file says the unit is Each and the pack is twelve. One of those two is wrong, and a count of 151 means either 151 pieces or 1,812 of them.

The function rule above reads the whole row, so it weighs the pack against the unit beside it. dependentFields sits on the column the person edits and names the columns to check again. Changing the unit rechecks the pack. In the sample that rule flags 210 rows, and gb, Italy and United Kingdom fail the two-letter rule on top of it.

<DataEditor
  columns={columns}
  primaryKey={["itemCode", "siteCode", "lotCode"]}
  synonyms={synonyms}
  onComplete={async (result) => {
    for (const source of result.sources) {
      const clean = source.rows.filter((r) => r.isValid);
      await postItems(source.sourceName, clean);
    }
  }}
/>

The key names three columns, because one item sits in several sites under several lots. Each tab lands as its own data source, named harbour-lane-items.xlsx - Item Master. So onComplete hands you the rows tab by tab.

What Updog knows about warehouses

Nothing. There is no item template, no unit list, no WMS connector and no per-industry schema to start from. The ten columns, the four options, the alias table, the transformer and the two rules live in your repository. Updog Importer reads CSV, TSV, JSON, XML, XLSX, XLS, XLSB and ODS. A price list that arrives as a PDF or a scanned image goes to a parser you supply, and the rows it returns walk the same path, which is written up in importing PDFs with your own parser.

The unit enum is the honest cost. Four options cover the packaging your platform stores. Every supplier who ships by the dozen lands in front of a person. That is the trade a fixed list makes, and widening the list moves the same decision into your database.

What the schema leaves out

There is no ordered quantity beside the received one, no rate, no delivery window and no partner id. Every one of those belongs to a file that arrives on a schedule. This import is one person opening one workbook and pressing a button.

What the ten columns hold is the state of a catalogue on the day somebody sent it. If that is the file your customers attach to an email, then the header row, the unit column and the pack size are the whole job. All three are settled before a single row reaches your API.