Automating commodity catalogue update
Short answer: automate catalogue updates by standardising inbound files, applying deterministic transformation rules, and validating against the existing catalogue before import. That removes most manual fixes and keeps daily updates predictable.
What this automation needs to solve
Teams often receive daily catalogue updates in spreadsheets. Usually from different business streams, usually with slightly different structure, and usually five minutes before somebody asks for a clean import file.
The objective is simple: process updates consistently, enrich missing fields, and only push valid records into the target catalogue. In practice, that means no ad-hoc spreadsheet surgery and fewer “why is this code duplicated?” emails.
For background on data integration patterns and why validation-first pipelines matter, IBM’s ETL reference is still a useful baseline: What is ETL.
Input model: PO and MTO update files
A common pattern is two inbound file types:
- PO files with more complete classification fields.
- MTO files where project number or unit metadata may be missing.
The clean approach is to map both into one canonical schema, then run shared rules. For MTO records without project markers, use a maintained translation table to assign project context before downstream validation.
Core transformation and validation rules
1) Normalise size values
Convert fractional and mixed formats into numeric-standard values, trim spaces, and sort populated size fields from smallest to largest.
2) Fill unit-of-measure gaps deterministically
If quantity includes decimals, assign meter-style units; otherwise assign each/unit count based on agreed business rule.
3) Validate against existing catalogue patterns
Check both underscore and dash variants where legacy coding styles differ, then suppress duplicates before export.
Rule of thumb: if you cannot explain a transformation rule in one sentence, it is probably too vague to automate safely.
Reliable material code generation
Material code logic should be deterministic and fallback-safe:
- Use commodity code + ordered size fields when available.
- Skip missing size segments instead of inserting placeholders.
- If commodity + sizes are empty, fallback to tag-based identifier.
Practical view: inconsistent key generation is where otherwise good ETL projects go to have a long, difficult meeting.
For teams moving from spreadsheet-only handling to repeatable workflows, compare Advanced ETL Processor editions and run a trial using your own sample files via downloads.
Implementation checklist for production
- Lock canonical schema for all inbound file types.
- Version your business rules and mapping tables.
- Validate before write, not after write.
- Export only net-new valid catalogue rows.
- Log rejects with rule-level reason codes.
Tell-people-when-not-to-hire-us moment: if your catalogue changes once a quarter and has 30 rows, manual update may be perfectly fine.
This job was done for "GT Services experts in Materials Management, Order Managementment processess and reporting".
Related reads: The Price of ETL Software and Monitoring Directories for New Files.