# BOM Scrub Python Script Generator for CSV Files

> Source: https://netray.co/tools/bom-scrub-duplicate-mpn-python-script-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate a Python script that reads a BOM CSV and reports duplicate MPNs, blank part numbers, missing reference designators and quantity versus refdes count mismatches.

## Category

developer

## Industry

Electronics Manufacturing

## Audience

- Electronics buyer
- Component engineer
- NPI engineer

## Tool

**Kind:** template

**Output:** code

**Language:** python

**Fields:**

#### BOM CSV file path

**Key:** bom_file

**Type:** text

**Default:** bom_rev_b.csv

#### MPN column name

**Key:** mpn_col

**Type:** text

**Default:** MPN

#### Reference designator column

**Key:** refdes_col

**Type:** text

**Default:** RefDes

#### Quantity column

**Key:** qty_col

**Type:** text

**Default:** Qty

#### Manufacturer column

**Key:** mfr_col

**Type:** text

**Default:** Manufacturer

#### Findings output CSV

**Key:** out_file

**Type:** text

**Default:** bom_findings.csv

**Blocks:**

#### bom_scrub.py

**Body:** import csv
import re
from collections import defaultdict

BOM_FILE = '{{bom_file}}'
OUT_FILE = '{{out_file}}'
MPN_COL = '{{mpn_col}}'
MFR_COL = '{{mfr_col}}'
REFDES_COL = '{{refdes_col}}'
QTY_COL = '{{qty_col}}'

findings = []
by_mpn = defaultdict(list)
all_refs = defaultdict(list)

with open(BOM_FILE, newline='', encoding='utf-8-sig') as f:
    for line_no, row in enumerate(csv.DictReader(f), start=2):
        mpn = (row.get(MPN_COL) or '').strip().upper()
        mfr = (row.get(MFR_COL) or '').strip()
        refs = [r for r in re.split(r'[,\s]+', (row.get(REFDES_COL) or '').strip()) if r]
        try:
            qty = int(float(row.get(QTY_COL) or 0))
        except ValueError:
            qty = -1

        if not mpn:
            findings.append((line_no, 'MISSING_MPN', 'No manufacturer part number'))
        else:
            by_mpn[mpn].append(line_no)
        if mpn and not mfr:
            findings.append((line_no, 'MISSING_MFR', 'MPN ' + mpn + ' has no manufacturer'))
        if not refs:
            findings.append((line_no, 'MISSING_REFDES', 'No reference designators'))
        if qty < 0:
            findings.append((line_no, 'BAD_QTY', 'Quantity is not a number'))
        elif refs and qty != len(refs):
            findings.append((line_no, 'QTY_MISMATCH', 'Qty ' + str(qty) + ' but ' + str(len(refs)) + ' refdes'))
        for r in refs:
            all_refs[r.upper()].append(line_no)

for mpn, lines in by_mpn.items():
    if len(lines) > 1:
        findings.append((lines[0], 'DUPLICATE_MPN', mpn + ' appears on lines ' + ', '.join(str(n) for n in lines)))

for ref, lines in all_refs.items():
    if len(lines) > 1:
        findings.append((lines[0], 'DUPLICATE_REFDES', ref + ' used on lines ' + ', '.join(str(n) for n in lines)))

findings.sort(key=lambda x: (x[1], x[0]))
with open(OUT_FILE, 'w', newline='', encoding='utf-8') as out:
    w = csv.writer(out)
    w.writerow(['line', 'issue', 'detail'])
    w.writerows(findings)

print(str(len(findings)) + ' findings written to ' + OUT_FILE)

**Notes:** Run with Python 3.8 or later; it uses only the standard library. Column names must match your CSV header exactly. Duplicate MPN lines can be legitimate (the same part split across assembly options), so treat findings as review prompts. The script does not change your BOM or connect to any ERP.

## Content

**Intro:** A BOM scrub catches the errors that become shortages and wrong builds: missing part numbers, quantities that do not match the designator count, and the same refdes used twice. This generator writes a short Python script that checks a CSV BOM and saves its findings to a file.

**Sections:**

#### How to use the script generator

**Body:** Enter the BOM file name and the column headings your CSV uses for MPN, manufacturer, reference designators and quantity. Copy the script into a file named bom_scrub.py, place it next to your BOM export and run it with Python. It writes a findings CSV listing the line number, the type of issue and a short description for each problem.

**Bullets:**

- Match the column names to your export exactly
- Review findings before changing the BOM
- Rerun after each revision to confirm the issues are gone

#### What each check means

**Body:** A missing MPN blocks sourcing. A missing manufacturer makes an MPN ambiguous, since different makers can reuse the same string. A quantity that differs from the number of designators on the line usually means a refdes was dropped or an extra one was added. A duplicate refdes means two lines claim the same board position. Duplicate MPNs often mean the same part was split over several lines when it should have been merged.

#### Using it in your workflow

**Body:** Run the check before releasing a BOM to purchasing, and again when the ERP BOM is compared against the CAD export. Teams often add a column for lifecycle status or approved alternate checks next. Keep the script in version control and the findings with the engineering change record, so you can show what was checked on each revision.

#### Common mistakes

**Body:** Do not trust a clean run as a full BOM review: the script cannot tell that a 10 kilohm resistor is on a line that should be 1 kilohm. It also treats designator ranges such as R1-R10 as one token, so expand ranges before running if your CAD tool writes them that way. Finally, check the file encoding, since BOM exports from spreadsheets often include a byte order mark, which the script handles.

**Cta:** If you want BOM checks to run automatically against ERP revisions with results routed to the right engineer, we can build that as a private agent inside your network.

**Faq:**

#### Does the script change my BOM file?

**Answer:** No. It only reads the CSV and writes a separate findings file. Review the findings and make changes in your BOM source system or spreadsheet.

#### Why does it flag duplicate MPNs?

**Answer:** Repeated MPNs often show lines that should be merged, but they can be legitimate for assembly options. The flag is a prompt to review, not an error in every case.

#### What if my reference designators are written as ranges?

**Answer:** Expand them first, for example R1-R4 to R1,R2,R3,R4, because the script counts comma or space separated tokens. A range would count as a single designator and trigger a mismatch.

#### Can it check manufacturer name variants?

**Answer:** Not in this version, which only checks that a manufacturer is present. Name normalization needs a mapping table, such as one that maps abbreviations and acquisitions to a single canonical name.

## Search terms

bom check script python, bom csv validation, duplicate mpn check, refdes quantity mismatch, bom scrub tool, bom error checker, pcba bom review script, python bom cleanup

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