Author: Erin Weiss Portfolio | LinkedIn | GitHub
View the Full Project Write-Up | Live Streamlit App
Provide an interactive web application for cleaning, standardizing, and comparing Excel files, particularly spreadsheets produced by PDF-to-Excel invoice conversions. The tool reduces manual effort by automating header correction, row realignment, date standardization, missing-value imputation, and cross-file comparison on a shared key column. Originally built for Aftermath Disaster Recovery, the application replaced a multi-hour manual reconciliation process, improving both turnaround time and accuracy in catching invoice discrepancies.
| Property | Detail |
|---|---|
| Source | User-uploaded Excel files (.xlsx, .xls) |
| Domain | Invoice reconciliation, monitor data auditing |
| Typical Layout | Messy tables from PDF conversion with shifted headers, extra rows, and inconsistent date formats |
- Workflow-First Design — Built around a concrete use case: reconciling invoice Excel files converted from PDF against monitor data. Cleaning logic tolerates header noise, extra rows, shifted columns, and inconsistent date formats rather than assuming clean, rectangular tables.
- Dual-Mode Architecture — The application exposes two interfaces. The Aftermath App encodes a known workflow with fixed column names and minimal user input for repeatable, fast processing. The General App reuses the same underlying logic but surfaces configurable options so users can describe what is wrong with each sheet and apply only the relevant transformations.
- Transparent, Reproducible Outputs — At each stage, intermediate results (cleaned DataFrames, comparison tables) are displayed in-app and downloadable as Excel files. A dedicated Tutorial page with sample files mirrors the internal logic step-by-step so users can verify how parameter choices affect final results.
| Category | Capability |
|---|---|
| Cleaning | Header repositioning, row cropping, left-side column trimming, row realignment, keyword-based row removal, date parsing and standardization, forward/backward/constant fill for missing values, optional fill-tracking columns |
| Comparison | Case-insensitive key matching, flexible cross-file column pairing (e.g., QTY vs Quantity), missing-row detection from both sources, value-mismatch reporting with side-by-side output |
| Export | Individual cleaned Excel downloads; multi-sheet comparison workbook bundling missing rows, mismatches, and combined missing IDs |
| Tutorial | Guided walkthrough with screenshots, GIFs, and a downloadable ZIP of sample invoices and monitor data |
| Contact | Built-in support form with email validation, CAPTCHA verification, and Formspree integration |
| Category | Tool |
|---|---|
| Language | Python |
| Framework | Streamlit |
| Data Processing | pandas, openpyxl |
| Frontend | HTML, CSS, JavaScript, Bootstrap |
| Utilities | email-validator, captcha, Pillow, requests, streamlit-js-eval, streamlit-pdf-viewer, python-dotenv |
| Deployment | Streamlit Cloud |
Excel-cleaning-and-compare-app/
├── 👉_Introduction.py # Landing page and app overview
├── pages/
│ ├── 1_💡_Tutorial.py # Guided walkthrough with sample data
│ ├── 2_💻_General_App.py # Configurable cleaning and comparison tool
│ ├── 3_🦺_Aftermath_App.py # Streamlined Aftermath Disaster Recovery tool
│ ├── 4_✉️_Contact.py # Contact form with CAPTCHA
│ ├── Sample_Excels.zip # Downloadable tutorial sample files
│ ├── Tutorial_Table_of_Contents.pdf # Sample file index
│ ├── excel-gif.gif # Tutorial page animation
│ ├── gen-gif.gif # General App page animation
│ ├── invoice-1-screenshot.jpg # Tutorial screenshot
│ ├── invoice-empty-row.jpg # Tutorial screenshot
│ ├── invoice-headings.jpg # Tutorial screenshot
│ ├── last-line.jpg # Tutorial screenshot
│ ├── num-cols.jpg # Tutorial screenshot
│ ├── row-fixed.jpg # Tutorial screenshot
│ └── logo.png # Aftermath App logo
├── app.jpg # App preview image
├── clean-compare.png # Introduction page hero graphic
├── clean_dataframe.png # Carousel — cleaned DataFrame example
├── dirty-invoice-pic.png # Carousel — original dirty invoice
├── dirty_dataframe.png # Carousel — raw imported DataFrame
├── logo.png # App logo
├── .devcontainer/
│ └── devcontainer.json # Dev container configuration
├── .streamlit/
│ └── config.toml # Streamlit theme configuration
├── requirements.txt # Python dependencies
└── README.md
The application is designed for deployment on Streamlit Cloud and is available at the live link above. To run a local copy for development:
git clone https://github.com/Erin-Weiss/Excel-cleaning-and-compare-app.git
cd Excel-cleaning-and-compare-app
pip install -r requirements.txt
streamlit run 👉_Introduction.pyNavigate through the sidebar to access the Tutorial, General App, Aftermath App, or Contact page. Sample Excel files are available for download directly from the Tutorial page. Note that the Contact form requires a formspree_endpoint entry in .streamlit/secrets.toml to function.
- Add support for CSV input files alongside Excel formats.
- Implement fuzzy matching on key columns to handle minor ID discrepancies such as typos across source files.
- Extend comparison logic to support more than two files simultaneously.
- Integrate an AI component that reads a dirty Excel file and automatically infers which cleaning steps and parameters to apply.