CSV Import for Manufacturing and ERP Software

A bill of materials is a tree. A finished product is built from assemblies, an assembly from sub-assemblies, and the bottom of the tree is a part somebody buys. Manufacturers keep that tree in an ERP or a PLM system, and they hand it out as a spreadsheet.

A spreadsheet has rows. Flattening a tree into rows means the shape has to be recorded some other way, and every system picks its own convention for that. Part numbers add their own trouble, because a spreadsheet reads one as a number and drops the leading zeros.

Your BOM line table holds a parent part number and a child part number. The file a customer sends holds eleven columns, and not one of them is a parent.

The parent is the row above. Which row above is settled by a Level integer and by the order the rows were printed in. Nothing else in the file says it.

A manufacturing platform stores one row per BOM line. Its customers export those lines out of the system they are leaving, and that export prints a tree as a list. Taking a customer's bill of materials into a schema of your own is customer data onboarding.

The file below holds 829 rows over eleven columns, 54 KB of CSV. Every number here came out of that file or out of running it through the importer.

The column that holds the tree

SAP Ariba publishes the template its BOM import reads, and the first mandatory field on it is Level. The page describes it as an integer value indicating "the hierarchical position of an entry in BOM". Beside it sit MaterialNumber, BOMNumber, a two-character BOMCategory, and a PlantID. Two more carry the effectivity, ValidFromDate and ValidToDate, both in YYYY-MM-DD. An ItemCategory closes the row over the values D, I, K, L, M, N, R and T.

One integer per row carries the whole shape of the assembly. That integer is written two ways, and First Resonance publishes both of them by name. Level notation numbers the tree, so 1 is the top assembly, 1.1 and 1.2 are its children, and 1.1.1 is a child of 1.1. Depth notation writes the indentation level instead, 1, then 2, then 3. OpenBOM builds the structure from either form on a spreadsheet import.

First Resonance also publishes the warning that goes with the second form. "The order matters", and the file must not have "been rearranged since exporting". A depth number says how far down a row sits. The row it sits under is whichever row above it holds the number one smaller, and that fact lives in the row order alone.

A BOM line on your side

Microsoft Learn describes the destination shape for Dynamics 365 Supply Chain Management. "A single BOM describes a single level that is identified by a unique ID. Components might have their own BOMs that are referenced by BOM versions." One assembly, its immediate children, and nothing deeper.

NetSuite publishes the same model as a file. Its BOM member replacement takes three columns in a fixed order, the internal ID of the assembly item, the internal ID of the BOM member, and a quantity. One assembly item gets as many rows as it has members. ERPNext stores it the same way, where "each item (sub-assembly) could have its own BOM hence forming a tree of Items with multiple levels". BuyPLM's own import file describes a "single-level BOM", the relationship "between an assembly and its immediate child parts".

Four published references, one storage model. A parent, a child, a quantity, and no level column anywhere. Your schema is a table of pairs, and the file is a picture of a tree.

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

const LINE_TYPES = ["Item", "Phantom", "Pegged supply", "Vendor"];

const UNITS = ["EA", "SET", "G", "KG", "L", "ML", "M", "MM", "SQM"];

export const columns: DataEditorColumn[] = [
  {
    id: "level",
    title: "Level",
    editor: { type: "number" },
    validators: [{ type: "required" }, { type: "number", min: 1 }],
  },
  {
    id: "findNumber",
    title: "Find number",
    editor: { type: "number" },
  },
  {
    id: "childPartNumber",
    title: "Part number",
    validators: [{ type: "required" }],
  },
  { id: "revision", title: "Revision" },
  {
    id: "description",
    title: "Description",
    validators: [{ type: "required" }],
  },
  {
    id: "quantityPer",
    title: "Quantity per",
    editor: { type: "number" },
    validators: [
      { type: "required" },
      { type: "number", min: 0, decimalPlaces: 3 },
    ],
  },
  {
    id: "unitOfMeasure",
    title: "Unit of measure",
    editor: { type: "select", options: UNITS, enableCustomValue: false },
    validators: [{ type: "oneOf", values: UNITS }],
  },
  {
    id: "lineType",
    title: "Line type",
    editor: { type: "select", options: LINE_TYPES, enableCustomValue: false },
    validators: [{ type: "oneOf", values: LINE_TYPES }],
  },
  { id: "referenceDesignators", title: "Reference designators" },
  { id: "effectiveFrom", title: "Effective from", editor: { type: "date" } },
  { id: "effectiveTo", title: "Effective to", editor: { type: "date" } },
];

Eleven fields, and level is one of them. It arrives, it is checked, and it is gone before the rows reach your database. There is no parentPartNumber column in this schema, because no header in any file can fill it.

The file a PLM system prints

Wrenfield Instruments makes flow instruments and is moving onto your platform. The file is the indented explosion of three products, printed by the system they are leaving.

1Level,Find,Part Number,Rev,Description,Qty,UOM,Type,Ref Des,Eff From,Eff To21,,WF-1000,B,Kestrel 400 Flow Meter,1,EA,Item,,07/17/2024,32,10,  2101-790,02,Bushing Bronze 10mm,2,ea,Item,,2023-11-15,42,20,  SA-1044,02,Rotor Assembly,1,,Item,,2026-02-06,53,10,    0712-330,B,O-Ring 24mm Viton,1,PC,,,,63,20,    SA-1046,C,Valve Block,1,ea,item,,2025-06-25,74,10,      2108-880,D,Foam Tape 12mm,4.296,MM,Pegged,,2026-02-20,84,20,      SA-1047,02,Filter Cartridge,4,PCS,Item,,08/06/2026,95,10,        2115-917,01,Grommet 8mm,12,EA,Item,,2023-06-13,105,20,        2117-335,-,Epoxy Adhesive 2-Part,0,,Pegged supply,,2026-02-25,115,30,        EC-51008,,Capacitor 100nF 50V,4,Ea,Item,"R1, R2, R3, R4",2024-07-21,2025-07-21⋮262 rows not shown2741,,WF-2200,A,Kestrel 250 Flow Meter,1,EA,Item,,03/04/2025,⋮286 rows not shown5611,,WF-3100,C,Kestrel 600 Flow Meter,1,EA,Item,,2025-09-08,⋮268 rows not shown8305,40,        2119-402,01,Washer M6 Nylon,8,EA,Purchased,,2024-10-02,

Depth notation, two spaces of indentation per level, carried inside the Part Number cell. That is what a printed indented BOM looks like on paper, and the export writes the paper.

The Level column runs 1 on 3 rows, 2 on 38, 3 on 150, 4 on 391 and 5 on 247. Part numbers come in four house styles, WF- for products, SA- for sub-assemblies, EC- for electronics, and four digits, a dash and three digits for purchased parts.

The indentation that is not data

Every parser in the SDK trims the cell before anything else sees it. cleanCell strips zero-width characters, turns a non-breaking space and a narrow non-breaking space into an ordinary space, normalises to NFC, and trims both ends. It runs on every cell of a CSV, an XLSX, a JSON file and an XML file, and on text pasted into the grid.

826 of the 829 Part Number cells in this file begin with a space. After the parse, none of them do.

That is the right answer for a part number. SA-1046 is the same part whether it is printed at the second level or the fourth, and a leading space is a property of the printout. The picture of the tree was never in that cell in a form your database could keep.

So Level is the only carrier left, and the row order underneath it.

The headers this export abbreviates

Seven of the eleven headers reach a field with nothing configured.

Rev, Qty, UOM and Ref Des reach nothing at all. Each of them is a shortening a manufacturing engineer reads without pausing, and a shortening shares no word with the field it stands for. Part Number also draws findNumber as a runner-up at 70, settled by the assignment that gives findNumber to Find.

export const synonyms = {
  columns: {
    childPartNumber: ["Part Number", "Part No.", "ChildNumber", "Component"],
    findNumber: ["Find", "Find No.", "Item No."],
    revision: ["Rev", "Revision"],
    quantityPer: ["Qty", "Qty Per", "Per", "Quantity Per Assembly"],
    unitOfMeasure: ["UOM", "Unit", "U/M"],
    referenceDesignators: ["Ref Des", "RefDes", "Reference Designator"],
  },
  values: {
    Item: ["Purchased", "Buy", "Standard", "Make", "Normal"],
    Phantom: ["Ph", "Phantom Item", "Phantom Assembly"],
    Vendor: ["Subcontract", "Subcontractor", "Outsourced"],
    "Pegged supply": ["Pegged", "Subproduction"],
    EA: ["Each", "PC", "PCS", "Piece", "Pieces"],
  },
};

Every string on the left of that list is published by somebody. BuyPLM prints Find and RefDes as header names in its own BOM import file, MRPeasy prints Part No., Quantity and Revision, and BuyPLM prints Per for a quantity per assembly. The alias table is the industry's vocabulary written down once, so the next customer's export lands on the same table.

With those six rows in place all eleven headers map.

Item, Phantom, Pegged supply, Vendor

Microsoft publishes four line types for a BOM line, and each one changes what the manufacturing run does. Item is an ordinary component. Phantom explodes "any lower-level BOM items that are contained on the BOM line". Pegged supply raises a subproduction, an event kanban or a direct purchase order for that line. Vendor is the subcontracted case.

Those four are the option list your schema declares. The Type column in this file holds 17 different strings and a blank.

Item on 261 rows, Purchased on 74, Buy on 69, item on 65 and blank on 58. Then Subcontract on 51, Pegged supply on 44, Vendor on 41, Standard on 38, Make on 36, vendor on 29 and Pegged on 22. The tail is ITEM on 14, Phantom on 12, phantom on 7, Ph on 4, Phantom Item on 2 and PHANTOM on 2.

Ten of the seventeen reach the right option unaided, because they are an option written in another case or with a word dropped. Purchased, Buy, Standard, Make, Subcontract and Ph reach nothing, and they cover 272 rows. Somebody has to say that a subcontracted line and a Vendor line are one thing, and no matcher can say it for them.

A value that reaches no option arrives empty, and the oneOf rule passes it. An empty cell is not a wrong value, so the rule has nothing to report. Left alone, 330 rows carry no line type at all, which is the 58 blanks in the file plus the 272 that matched nothing. { type: "required" } on the column is what turns that silence into a flag.

The seventeenth lands on the wrong option. Phantom Item matches Item before it matches Phantom, and it is written on 2 rows. The values half of the alias list settles all seventeen and puts those two where they belong. After it, 58 line-type cells stay empty, and 58 is exactly the number of blanks the file was written with.

The unit column behaves the same way. Each, PC, PCS, pcs and Ea are five spellings of one unit, and one alias row folds them onto EA.

The quantity that is not a number

The Qty column holds 650 plain integers and 49 zeros. 109 decimals are written with a dot and 10 with a comma. Three cells are blank, and 8 read AR.

AR means as required. First Resonance publishes a quantity "including zero for consumables/as-required materials", so both the word and the zero are legal in this trade. A number column cannot hold the word, and the 8 cells come through flagged Invalid number. The 49 zeros come through clean, which is why the rule on this column reads min: 0. A min: 1 would flag 49 correct lines.

Ten cells carry a comma. Updog settles a number format per file, and this file's quantity column votes United States on the strength of its 109 dot decimals. Each comma cell then landed somewhere.

A comma followed by three digits is legal grouping in a United States file, so six quantities became whole numbers a thousand times larger and nothing was flagged. A group of one or two digits cannot be grouping, and neither can a group that starts from a zero. The other four stayed exactly as they were written, and the person sees them.

That is the honest ceiling on a per-file decision. Four cells announce themselves and six do not. Common CSV import errors walks the failures any file can produce. A quantity that reads as a number and holds the wrong number is the one nobody goes looking for.

The row that lost its parent

One row in 829 claims a level with no row above it to hang on. Row 251, SA-1117, is written as level 5, and the row before it is written as level 3. A hand edit somewhere between the export and the email took the level 4 line out.

No rule you can put on a cell will notice it. A function validator is called with the value and the row it sits in, and nothing else. dependentFields widens that to another column of the same row. One line here needs it, row 125, EC-51164, valid from 2026-05-14 until 2024-03-01. The schema above declares no rule that reads two columns, so that row comes through clean. A rule reaches across a row. It never reaches up a row.

Position in the file is not a property of a cell. The check for it belongs to the code that receives the finished import.

A part number written thirty-five times

702 distinct part numbers cover 829 rows, so 127 rows carry a part number that already appeared. Four fasteners account for all 127. 0461-018 is written 36 times, 0453-204 35 times, 0712-330 34 times, and 0453-119 26 times. Every other part in this file is written once.

A { type: "unique" } rule on the part number would flag all 127, and every one of those rows is correct. The same bronze bushing goes into thirty-five different assemblies, and saying so is what a bill of materials is for. primaryKey on that column is wrong here for the same reason.

The pair is closer. Rebuilt from the file, 825 child rows produce 803 distinct parent-child pairs, so 22 rows repeat a pair that is already there. Two lines on one assembly for the same part is ordinary, and the find number is what tells them apart. Parent, child and find number together are distinct on all 825 rows in this file.

Nothing here knows what a sub-assembly is

Updog ships no BOM template, no part number format, no line type list, and no connector to any manufacturing system. The eleven columns, the four options, the alias tables, the rules and the walk below are all code you write.

If the parts list is yours and the destination is yours, this is the wrong tool. The system you already run has an import screen built for its own tables, and it knows what a revision means. Updog Importer is for the file that arrives from somebody else, in their vocabulary, on a day you did not pick.

Updog Importer reads CSV, TSV, XLSX, XLS, XLSB, ODS, JSON and XML. A BOM that survives only as a PDF goes through a parser you supply and arrives as ordinary rows. One million rows is a practical ceiling rather than an unlimited claim, and this file is 829.

From levels to parents

The walk is one pass over the rows in the order they arrived, keeping the last part number seen at each level.

type BomLine = Record<string, unknown>;

export const withParents = (rows: BomLine[]) => {
  const partAtLevel: string[] = [];

  return rows.map((row) => {
    const level = Number(row.level);
    partAtLevel[level] = String(row.childPartNumber);
    partAtLevel.length = level + 1;

    return {
      parentPartNumber: level === 1 ? null : (partAtLevel[level - 1] ?? null),
      childPartNumber: String(row.childPartNumber),
      findNumber: row.findNumber,
      revision: row.revision,
      quantityPer: Number(row.quantityPer),
      unitOfMeasure: row.unitOfMeasure,
      lineType: row.lineType,
    };
  });
};

Setting partAtLevel.length after the assignment is the whole trick. A row at level 3 drops everything the array held at level 4 and below, so a part number from a branch that closed cannot become a parent in the next branch. A row whose level jumps by more than one finds nothing at the level above it and reports null. Row 251 reaches your handler with an empty parent, which is the one answer a person can act on.

# the eight rows as the file writes them
Level,Find,Part Number,Rev,Qty,UOM,Type
1,,WF-1000,B,1,EA,Item
2,10,  2101-790,02,2,ea,Item
2,20,  SA-1044,02,1,,Item
3,10,    0712-330,B,1,PC,
3,20,    SA-1046,C,1,ea,item
4,10,      2108-880,D,4.296,MM,Pegged
4,20,      SA-1047,02,4,PCS,Item
5,10,        2115-917,01,12,EA,Item

# the same eight rows as the handler posts them
parentPartNumber,childPartNumber,findNumber,revision,quantityPer,unitOfMeasure,lineType
,WF-1000,,B,1,EA,Item
WF-1000,2101-790,10,02,2,EA,Item
WF-1000,SA-1044,20,02,1,,Item
SA-1044,0712-330,10,B,1,EA,
SA-1044,SA-1046,20,C,1,EA,Item
SA-1046,2108-880,10,D,4.296,MM,Pegged supply
SA-1046,SA-1047,20,02,4,EA,Item
SA-1047,2115-917,10,01,12,EA,Item

Level is gone, parentPartNumber is filled, the indentation never made it past the parser, and the spellings settled on the value step. Eight rows of a printed tree became eight rows of a table.

<DataEditor
  columns={columns}
  synonyms={synonyms}
  onComplete={async (result) => {
    const rows = result.sources
      .flatMap((source) => source.rows)
      .filter((r) => r.isValid && !r.isDeleted)
      .map((r) => r.row);

    const lines = withParents(rows);
    const orphans = lines.filter(
      (line, n) => !line.parentPartNumber && Number(rows[n].level) > 1,
    );

    await postBomLines({ product: "WF-1000", lines, orphans });
  }}
/>

All of that runs where the person dropped the file, and the rows travel from their browser to your endpoint under your own agreement. Client-side and server-side import follows a file down each of the two routes.

The list the engineer works down

829 rows and eleven columns make 9,119 cells, and 15 of them come through flagged. Twelve are quantities the importer could not reduce to a number, 8 of them written AR and 4 written with a decimal comma. Three are quantities the file left blank. One row has no parent, and it reaches your handler in the orphans list rather than in the table.

Sixteen items go to the person who knows the product, and every one of them is legible. The description sits beside every flagged quantity. The level 3 line above SA-1117 names the assembly the missing line belonged to.

Then 803 parent-child pairs land in a table your platform can explode, and a tree printed for a person to read becomes rows.