# ERPNext BOM Import Template (Excel and CSV)

> Source: https://netray.co/tools/erpnext-bom-import-template
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

An editable BOM worksheet with finished item, component, quantity, UoM and rate columns, calculating extended and rolled up cost before you import to ERPNext.

## Category

other-erp

## Erp

ERPNext

## Industry

Small Manufacturing

## Audience

- ERP admin
- Implementation consultant
- Engineer

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Finished item code

**Key:** fg_item

**Type:** text

#### Component item code

**Key:** comp_item

**Type:** text

#### Component description

**Key:** comp_name

**Type:** text

#### Qty per unit

**Key:** qty

**Type:** number

#### UoM

**Key:** uom

**Type:** select

**Options:**

- Nos
- Kg
- Meter
- Litre
- Box
- Set

#### Rate

**Key:** rate

**Type:** number

**Format:** currency

#### Amount

**Key:** amount

**Type:** number

**Expr:** qty * rate

**Format:** currency

#### Source warehouse

**Key:** warehouse

**Type:** text

**Rows:**

**Fg item:** FG-BRKT-100

**Comp item:** RM-STEEL-2MM

**Comp name:** Steel sheet 2 mm blank

**Qty:** 0.8

**Uom:** Kg

**Rate:** 1.9

**Warehouse:** Stores

**Fg item:** FG-BRKT-100

**Comp item:** HW-SCREW-M6

**Comp name:** Screw M6 x 16

**Qty:** 4

**Uom:** Nos

**Rate:** 0.06

**Warehouse:** Stores

**Fg item:** FG-BRKT-100

**Comp item:** HW-NUT-M6

**Comp name:** Nut M6

**Qty:** 4

**Uom:** Nos

**Rate:** 0.03

**Warehouse:** Stores

**Fg item:** FG-BRKT-100

**Comp item:** PK-BOX-S

**Comp name:** Carton small

**Qty:** 1

**Uom:** Nos

**Rate:** 0.35

**Warehouse:** Packing

**Fg item:** FG-BRKT-100

**Comp item:** CT-POWDER-BLK

**Comp name:** Powder coat black

**Qty:** 0.05

**Uom:** Kg

**Rate:** 6.5

**Warehouse:** Stores

**Totals:**

#### Material cost per finished unit

**Key:** amount

**Agg:** sum

**Format:** currency

**Summary:**

#### Average component cost

**Key:** avg_line

**Expr:** sum_amount / count

**Format:** currency

#### Most expensive component line

**Key:** max_line

**Expr:** max_amount

**Format:** currency

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the template

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Bullets:**

- Only one BOM per item should be default.
- Check decimal separators match your system settings.
- Do not import cancelled or draft engineering revisions.

**Cta:** If your BOMs live in several spreadsheets or a legacy ERP, Netray can help extract, clean and load them into ERPNext or another target.

**Faq:**

#### Can I import BOMs into ERPNext from Excel?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

erpnext bom import, erpnext bom template excel, erpnext data import bom, erpnext bill of materials csv, erpnext bom upload, erpnext item and bom bulk import, frappe manufacturing bom template, erpnext bom cost

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json