Case study
Automated Routing Pre-Planning
Turning three incompatible operational reports into a standardized, auditable routing workbook.
DisclosureThe portfolio version uses fully synthetic customer, supplier, identifier, service-code, volume and file-name data. The workflow, transformation concepts and control structure are representative of the implemented project.
The problem
Route planning often begins with a less visible problem: getting the operational data into a form a planner can actually use.
In this project, the information needed for daily routing arrived in three differently structured CSV and Excel reports, each with its own units, naming conventions, layouts and business rules.
Preparing the routing workbook required repeated manual work before planning could begin: identifying relevant records, applying source-specific exclusions and conversions, normalizing identifiers, aggregating duplicate orders, locating the correct template row, and investigating records that did not line up cleanly.
A workflow built around the existing planning process
The difficulty was not simply moving data between spreadsheets. Each source had to be interpreted differently, and the operating rules existed largely as process knowledge that had to be applied consistently every time.
I designed and built a Python workflow that converts the inputs into one standardized routing pre-planning workbook while preserving the Excel-based planning process already familiar to the operating team.
Understanding the problem
A useful automation had to accommodate three distinct inputs rather than forcing them into one assumed format.
One source supplied shipment weight in pounds and inconsistent supplier labels. Another Excel report contained metadata above the actual table and already used kilograms. A third required service-code filtering and a package-to-case conversion before the records were suitable for planning.
The existing day-specific routing workbook also mattered. Rather than replacing a familiar operating tool, the automation needed to populate it reliably and preserve its established route order.
The solution
I developed a configuration-driven Python workflow that reads each source using its own extraction rules and then standardizes the resulting records into a common structure.
The process normalizes location and supplier identifiers, converts units where required, filters records using source-specific business rules, aggregates duplicate orders, applies case conversions, and matches the resulting records against the existing routing template using normalized keys.
Matched stops are populated directly into their existing route positions. Records that are valid but have no corresponding template row are appended to an Additional / Unmatched section for planner review rather than being discarded.
Implementation and controls
The workflow uses Python and openpyxl for CSV/XLSX processing and Excel output, with operating assumptions kept in YAML configuration where practical rather than scattered through the code.
The controls include normalized matching keys, explicit service-code filtering, defined unit conversions, canonical supplier aliases, configurable planning assumptions, preservation of unmatched records, and separate audit files for each source.
Each audit record retains the originating source reference, normalized location and supplier, transformed cases and weight, target template row, match status and control note.
Results
The workflow changed the recurring task from multi-file manual preparation into a simpler operating sequence that keeps planner judgement focused on routing decisions.
It also reduced dependence on individual knowledge of how each report needed to be interpreted by moving those repeatable rules into a consistent process.