ERPNext BOM Import Template (Excel and CSV)
Entering bills of materials one by one in ERPNext is slow, so most teams prepare them in a spreadsheet and use Data Import. The common failures are missing items, inconsistent units and wrong layout. This worksheet gives you a clean flat structure and a cost check before you load anything.
| Finished item code | Component item code | Component description | Qty per unit | UoM | Rate | Amount | Source warehouse | |
|---|---|---|---|---|---|---|---|---|
$2 | ||||||||
$0 | ||||||||
$0 | ||||||||
$0 | ||||||||
$0 |
Create Item records first, then import BOMs. ERPNext's Data Import expects the BOM header row once and component rows beneath it, so rearrange this flat layout to match the template you download from the Data Import tool for your version. Treat this sheet as the clean source of truth before import.
BOM data cleanup help
Optional help cleaning and loading BOMs from spreadsheets or a legacy ERP into ERPNext.
Optional. The tool and every export work without this. No spam.
How to use the template
Enter one row per component and repeat the finished item code on each row. Quantities are per one unit of the finished item. Use units of measure that already exist in ERPNext, such as Nos or Kg, because unknown UoMs cause import errors. The Amount column multiplies quantity by rate, and the total shows material cost per finished unit. Export to Excel or CSV when satisfied, then reshape to the BOM import layout for your ERPNext version.
- Create every component and finished item in the Item master first.
- Keep item codes identical in spelling and case.
- Import a handful of BOMs as a test before the full file.
How ERPNext structures a BOM
A BOM in ERPNext links a finished item to a list of component items with quantities, UoM and rates, and may include operations and work stations if routing is used. Costs can be fetched from the price list, last purchase rate or valuation rate. Sub-assemblies are separate items with their own BOM, and nested BOMs roll up through the parent. The Data Import tool treats the header and the child table as one record, which is why the file needs the header fields only on the first row of each BOM.
Worked example
The sample rows describe a powder-coated bracket: 0.8 kg of steel sheet at 1.90, four screws, four nuts, a carton and a small amount of powder. Material cost comes to roughly 3.1 per unit. If the coating is outsourced you would model it as a service item or a subcontract operation rather than a stock component. Compare this total against your selling price to check material margin before labour and overhead.
Common import mistakes
Most failures are item codes that do not exist, UoM mismatches between the BOM line and the item's stock UoM without a conversion factor, and duplicate BOMs marked as default for the same item. Another trap is formatting: Excel can turn codes such as 00123 into numbers and drop leading zeros. Set code columns as text, save as CSV with UTF-8 encoding, and check the first rows in a text editor.
- Only one BOM per item should be default.
- Check decimal separators match your system settings.
- Do not import cancelled or draft engineering revisions.
Frequently Asked Questions
Can I import BOMs into ERPNext from Excel?
Yes. Use the Data Import tool on the BOM doctype, download the template for your version, fill it from your spreadsheet and upload. Items must already exist. Test with a few BOMs first, because the header-and-child row layout trips up many first imports.
How does ERPNext calculate BOM cost?
It can use the valuation rate, last purchase rate or a price list rate for each component, plus operation costs if routing is included. The rate fields on the BOM can be refreshed with the update cost option, so imported rates are only a starting point.
How do I handle multi-level BOMs?
Create each sub-assembly as an item with its own BOM, then reference the sub-assembly item as a component in the parent. Import lower levels first so they exist when the parent is loaded.
Do I need operations in the BOM?
Not to start. You can import materials only and add operations and workstations later when you want routing time and cost. Keep the material structure right first, then add complexity gradually.
If your BOMs live in several spreadsheets or a legacy ERP, Netray can help extract, clean and load them into ERPNext or another target.
Related Tools
ERPNext Stock Entry Types Reference
A lookup table of ERPNext Stock Entry purposes showing which warehouses are required, what the entry does and when to use it.
ERP Migration & SelectionSpreadsheet to MRP Migration Checklist
A phased checklist to move planning, BOMs and inventory out of spreadsheets and into a cloud MRP or ERP without losing control during cutover.
Other ERPsFishbowl Inventory Import Worksheet
An editable opening-inventory worksheet with part, location, quantity and cost columns and a valuation total to reconcile before importing into Fishbowl.
Other ERPsOdoo Manufacturing Setup Checklist
A practical checklist for configuring Odoo Manufacturing from inventory foundations through BOMs, work centers, planning and go-live.
More Other ERPs tools
See all- Infor VISUAL Work Order Status and Structure Reference
- Infor VISUAL Manufacturing Scheduling Checklist
- Infor XA (MAPICS) Period End Close Checklist
- Infor VISUAL Database Table Reference
- Infor XA (MAPICS) Application Module Reference
- IBM i Commands for Infor XA Administrators Reference
- Acumatica Generic Inquiry Design Template
- Acumatica Manufacturing Edition Setup Checklist
- Sage X3 Import Template Worksheet