This utility was created to modernize data workflows within a Geological Laboratory. It addresses the challenge of digitizing results from legacy equipment that output unstructured Excel binaries (XLS).
The script automates the Extract, Transform, and Load (ETL) process, parsing thousands of raw files and mapping them to standardized reporting templates required by the institution.
- File Parsing: Recursive directory scanning to identify and process non-standard legacy files.
- Data Mapping: Intelligent matching of chemical element columns to the strict database schema.
- Excel Injection: Low-level manipulation using
openpyxlto inject data into official templates while preserving complex formatting.
- Python:
pandas,openpyxl,os,traceback. - Concept: Data Normalization & Process Automation.
Significantly reduced human error in data transcription and cut down reporting time from days to minutes.