Reservation Data Transformation Tool
EFE OZAN AKAY
Reservation Data Transformation Tool
Rule-based spreadsheet validation and transformation for operations workflows.
A configurable Python application that turns irregular reservation spreadsheets into standardized arrival and departure datasets, while preserving traceability through validation issues and audit-friendly outputs.
Overview
Operational reservation lists often combine block metadata, passenger rows, property names, transport details, and identifiers in layouts designed for people rather than software. This project demonstrates how that spreadsheet workflow can be expressed as explicit, testable transformation rules.
All included records are fictional and were generated from scratch for this portfolio repository.
The business problem
Manual spreadsheet conversion is vulnerable to inconsistent identifiers, ambiguous property names, malformed ages, mixed date formats, incomplete flight details, and direction-classification mistakes. The most dangerous failures are often silent: a row may appear valid while being routed to the wrong output or assigned the wrong reference data.
The solution
The tool separates configuration from transformation logic. A transformation profile defines source markers, column names, hub codes, voucher validation, age thresholds, aliases, and fallback rules. The engine then:
Parses reservation blocks and passenger rows.
Validates and normalizes identifiers, dates, times, properties, and flight data.
Classifies arrivals and departures.
Quarantines unresolved-direction records.
Produces consistent CSV datasets and a structured validation report.
Core workflow
Business rules demonstrated
Configurable block, header, and end markers.
Voucher extraction using a configurable regular expression and length limit.
Voucher inheritance across passenger rows within a reservation block.
Collision detection when distinct source reservations produce the same target voucher.
Configurable infant, child, and adult age thresholds.
Strict rejection of blank, negative, fractional, and nonnumeric ages.
Exact and approved property matching without automatic fuzzy assignment.
Explicit missing, unmapped, and ambiguous property states.
Flight route, number, and time parsing.
Configurable carrier-code aliases and hub-airport sets.
Configurable connection/destination overrides.
Date and time normalization.
Arrival/departure separation with unresolved records quarantined.
Fixed, documented output schema.
Configurable output filenames and validation-report title.
Validation and safety checks
The validation report records missing passengers, flights, and properties; invalid ages and vouchers; ambiguous or unmapped properties; voucher collisions; and unknown directions.
Unknown-direction records are never silently treated as arrivals. They are excluded from normal outputs and placed in a quarantine dataset for manual review.
Fictional demo dataset
The
examples/fictional directory contains a workbook, property master, approved mappings, expected outputs, and validation report created entirely from invented values. The scenarios cover successful transformations and deliberate edge cases.Run the example:
Run the tests:
Tech stack
Python 3.11+
openpyxl for spreadsheet readingStandard-library CSV and JSON output
Standard-library
unittest for automated testsJSON transformation profiles
Architecture and project structure
Example transformation flow
A fictional passenger row with a source voucher, property alias, age, and route is validated against the active profile. The voucher identifier is extracted, the property alias resolves through an approved mapping, the age is categorized, and the route determines the output direction. Invalid or ambiguous values generate explicit issues instead of guessed corrections.
Current project status
Portfolio-ready reference implementation. It demonstrates the transformation architecture and safety controls but is not presented as a drop-in production integration.
Portfolio and case-study context
This project shows how business-specific spreadsheet procedures can be translated into maintainable software: configurable rules, deterministic outputs, explicit exception handling, repeatable tests, and privacy-safe demonstration artifacts.
Author: Efe Akay — AI Automation & Digital Product Builder
Privacy note
No production bookings, customer records, operational mappings, proprietary templates, internal system identifiers, or company-local configuration are included. Every demonstration record was invented specifically for this repository.
Like this project
Posted Aug 14, 2026
Built a rule-based tool that validates and transforms complex reservation spreadsheets into standardized operational datasets with audit reporting.
