5A Distrubutions
BOM enrichment workflow for messy supplier spreadsheets, component matching, and automated Excel output.
- Client
- 5A Distrubutions
- Service
- BOM enrichment & data automation
- Industry
- Electronics & component distribution
- Status
- Delivered
Service used
BOM enrichment & data automation
The work was shaped around the operating problem first, then translated into the right mix of automation, systems, and workflow design.
Stack
n8n
Workflow orchestration
Outlook
Email trigger and delivery
OneDrive
Template storage
Digi-Key API
Component enrichment
AI header mapping
Header normalization
Excel
Final BOM output
Regex mapping
Manufacturer cleanup
The idea
A private electronics components company regularly received bills of materials from clients in messy and inconsistent formats. Some files were incomplete, some used different languages, and many were missing the clean structure needed to quickly identify components, compare quantities, and prepare a usable enriched BOM.
The goal was to create a workflow that could take an incoming BOM from email, normalize the structure, enrich the component data through Digi-Key, calculate useful commercial values, and return a finished Excel file without requiring manual cleanup of every spreadsheet.
The workflow was built as an internal prototype for the distributor, focused on testing whether messy client BOMs could be cleaned, enriched, and returned in a consistent format with less manual review before being considered for regular operational use.
The solution
A custom n8n workflow was built around an Outlook folder dedicated to incoming BOM files. When a new email arrived in the BOM folder, the workflow extracted the attached spreadsheet and used a OneDrive-hosted Excel template as the target structure for the final output.
The workflow was split into separate paths to reduce errors and keep each step focused. One path handled the original BOM extraction and header analysis. Headers were checked against the expected structure, and when they did not match, only the header data was sent to an instructed AI step for normalization. This kept token usage low and reduced the risk of hallucinated changes to the actual component rows.
Another path handled Digi-Key API authentication. The bearer token flow was separated so the system did not generate a new token for every component lookup. This made the enrichment process faster, cleaner, and easier to control.
Before enrichment, manufacturer names were cleaned through a regex-based mapping table. Common variations and messy manufacturer labels were transformed into formats more likely to match Digi-Key’s search behavior. The component rows were then processed in batches to work around API limits, including separate passes for larger BOMs and different search strategies to improve matching accuracy.
Once Digi-Key returned component data, the workflow mapped the enriched values back into the required Excel headers. It also calculated commercial fields such as unit pricing at different BOM quantities, including larger quantity scenarios such as 10,000 units. The final enriched rows were merged into the Excel template, exported as a completed BOM file, and sent back through Outlook as an email attachment.
The workflow turned a manual BOM cleanup process into an automated enrichment pipeline. Instead of checking headers, cleaning manufacturer names, searching component data, calculating quantity-based values, and rebuilding the spreadsheet by hand, the system handled the repetitive structure and lookup work automatically.
The result was a finished Excel BOM that followed the required format, included enriched component information, and was returned through the same email workflow the business already used.