HBB File Transformation Engine
A Python transformation engine built on Pandas, regular expressions and OpenPyXL. It detects the layout of each incoming file, extracts and normalizes benefit data, and writes a formatted Excel workbook in the expected industry-standard structure.
Problem statement
Problem statement
Raw benefit files arrive in many shapes: different column names, different column orders, merged headers, values with units and conditions packed into free text. Each file had to be reshaped by hand into a standard template.
Business challenge
Business challenge
A fixed column mapping breaks the first time a source changes its layout. The engine had to work out what each column means from the data itself and still produce exactly the output format the business expects.
Solution architecture
- 01
Dynamic column detection identifies headers and maps source columns to the standard schema using names, patterns and sample values.
- 02
Regex-based extraction pulls amounts, limits, percentages and conditions out of free-text cells.
- 03
Pandas transformations normalize, validate and reshape the data into the target structure.
- 04
OpenPyXL writes the final workbook with the required sheets, column order, formats and styling.
01Input
Raw benefit files
Excel / CSV, varying layouts
02Understand
Header locator
Finds the real header row
Column detector
Maps columns to standard schema
03Transform
Regex extractors
Amounts, limits, conditions
Pandas pipeline
Normalize, validate, reshape
04Output
OpenPyXL writer
Template, formats, styling
Standard workbook
Industry-standard Excel output
Workflow
- 1
Read
The raw file is loaded without assuming where the header row or data starts.
- 2
Detect columns
Columns are matched to the standard schema by name patterns and sample values.
- 3
Extract
Regular expressions pull structured values out of free-text cells.
- 4
Normalize
Units, formats and category names are standardized; rows are validated.
- 5
Reshape
Data is restructured into the layout the output template expects.
- 6
Write
A formatted workbook is generated, with unmapped or suspect values flagged for review.
Technology stack
- Python
- Pandas
- Regex
- OpenPyXL
- Excel automation
Implementation details
- Column detection is rule-driven and configurable, so supporting a new source layout is usually a configuration change rather than new code.
- Regex patterns are kept in one tested module with example inputs, because pattern changes are the most common source of regressions.
- Anything the engine cannot map with confidence is flagged in the output rather than guessed.
- The Excel writer reproduces the expected template exactly — sheet names, column order, number formats and styling.
Engineering challenges
Headers that are not on row one
Many files start with titles, notes or merged cells. The engine locates the real header row before doing anything else.
Meaning hidden in free text
A single cell may hold an amount, a limit and a condition. Extraction rules had to be precise without becoming brittle.
Exact output fidelity
Downstream consumers expect a specific workbook structure. 'Nearly right' output is rejected, so formatting is treated as a requirement.
Business value and results
Business value
- File preparation that was manual and repetitive now runs automatically.
- Consistent, template-accurate output regardless of the source layout.
- New source layouts are absorbed through configuration.
Measured results
No quantified results are published for this project.
Screenshots, code and demo
Screenshots
Not published — the interface belongs to a client engagement.
Source code
Private — client-owned code.
Live demo
No public demo. Ask for a walkthrough.
Have an AI or Data Engineering challenge? Let's turn it into a working product.
Taking on a limited number of AI and data engineering projects. Typical reply within 2 business days.