Spreadsheet-to-Database Consolidator
Every rejected row keeps its source, original values, and a reason someone can act on.
Nothing gets dropped without a reason attached
This pipeline standardizes and validates a folder of inconsistent spreadsheets, then loads the accepted rows into SQLite. Rejected rows go to a separate quarantine table with their original values, source file, row number, and a plain-language reason. A Streamlit dashboard reports what loaded, what failed, and why.
Local mode runs without credentials against any folder of supported files. Google Drive mode accepts a folder ID and OAuth credentials, downloads the files, and passes them through the same processing stages.
Source Code
The source code is on GitHub.
Dashboard Preview
Open the live Streamlit dashboard. The screenshot remains visible if the hosted application is asleep or unavailable.
Motivation
Small teams often collaborate through a shared Drive folder where each person maintains a separate spreadsheet. Without a shared schema, column names drift, dates appear in several formats, and notes end up in numeric cells. A manual consolidation can quietly lose rows, leaving no record of what failed.
Power BI and dedicated ETL products can solve parts of this problem, but they may add licensing or operating overhead that a small team does not need. This project keeps the spreadsheet workflow and adds a repeatable validation layer behind it.
This is a data-cleaning pipeline, not a data warehouse. Its job is to turn inconsistent source files into usable tables and account for every row it rejects.
Quarantining Rejected Rows
Every row that fails validation goes into a quarantine table with the original data unchanged, the source file and row number, and a plain-language reason. The sample files produce errors such as these:
"revenue is negative (-450.00) in row 23 of Q3_2024_sales.xlsx"
"date '2099-01-01' is out of range in row 14 of Q4_2024_sales.xlsx"
"date '2024-13-01' is not a valid date in row 3 of Q3_2024_sales.xlsx"
"quantity 'TBD' is not numeric in row 9 of Q3_2024_sales.xlsx"
"required field 'customer' is empty in row 12 of Q1_2024_sales.xlsx"
"date 'Mike please update this by Friday' is not a valid date in row 31 of Q2_2024_sales.xlsx"
The last example contains a note in a date cell. The consolidator cannot parse it, so it preserves the row and identifies the exact problem. The file owner can correct the source without guessing what the pipeline discarded.
At the end of a run the dashboard shows something like: "347 rows loaded successfully, 12 rows quarantined for review." The quarantine table is filterable by source file, so you can hand back a targeted list of fixes to whichever team member owns each file.
Design Tradeoffs
Power BI and Tableau Prep may be the right choice when a team already uses them. This project is aimed at cases that need a lightweight, queryable SQLite output and an explicit rejection trail without adding another managed platform.
SQLite fits the intended scale of a few hundred to a few thousand rows. It needs no server or credentials, produces one inspectable file, and runs immediately after cloning the project.
Validation rules live in YAML rather than Python so the pipeline can be reused without code changes. Adapting it to a different dataset means opening one configuration file, changing the column names and range limits, and running the pipeline again. That keeps the rules readable by the person who owns the data rather than the person who wrote the code.
The pipeline does not guess how to fix invalid data. If a revenue cell says "see notes below," the intended value could be 50,000. The row stays in quarantine until the source owner resolves it.
The tool accepts CSV, spreadsheet, and Google Sheets exports interchangeably. Keeping it format-agnostic means anyone on the team can contribute files however they prefer, and the pipeline handles everything without a conversion step.
Before/After: What the Pipeline Does to Messy Files
The project ships with eight sample files in data/sample_files/, six spreadsheet and two CSV, built to reproduce the
inconsistencies a shared Drive folder accumulates. The consolidator resolves the following before any row reaches the
validator:
| Issue in source files | How the consolidator resolves it |
|---|---|
| Column called "Revenue", "Rev.", "Total Revenue" across different files | Mapped to canonical name revenue via alias dictionary |
Dates formatted as 2024-01-15, 01/15/2024, Jan 15 2024, 15-Jan-24 | Normalized to ISO 8601 (YYYY-MM-DD) |
Numbers stored as text, currency symbols like $1,200.00 | Stripped and cast to numeric, failures preserved for quarantine |
west_region_2024.xlsx has two title rows above the actual header | Header row auto-detected, title rows skipped |
| Same row appearing in two different quarterly files | Exact cross-file duplicates removed, first occurrence kept |
File has no region column but filename contains "west" | Region column injected: "West" |
Every one of those transformations gets written to the cleaning_log table: what changed, in which file, and when.
Configurable Validation Rules
Rules live in config/validation_rules.yaml. This is the configuration the project ships with:
columns: revenue: type: numeric min: 0 required: true date: type: date min: "2015-01-01" max: "2026-12-31" required: true quantity: type: numeric min: 0 max: 100000 required: true region: type: text required: false sales_rep: type: text required: false customer: type: text required: false product: type: text required: false min_non_null_fields: 3 flag_non_conforming_types: true negative_revenue_allowed_files: - returns_flagged.csv
The returns_flagged.csv file contains refunds, so negative revenue is intentional. A file-level exception keeps those rows out of quarantine. This makes the business exception explicit in configuration instead of burying it in Python.
To adapt this tool to a completely different dataset, you change the YAML. No Python edits required.
Architecture
data/sample_files/ ──► consolidator.py ──► validator.py
(or Google Drive) │
▲ ┌───────┴────────┐
│ ▼ ▼
drive_connector.py clean_df quarantine_df
│ │
└──────┬─────────┘
▼
db_loader.py
┌──────┴──────┐
▼ ▼
consolidated quarantine
table table
│
cleaning_log
table
│
┌─────────────┼─────────────┐
▼ ▼ ▼
export.py report.py dashboard/app.py
(Spreadsheet out) (HTML report) (Streamlit UI)
The pipeline has clean separation between stages. consolidator.py handles all the messy standardization work
(column names, dates, numeric cleaning, duplicates) and outputs a single merged DataFrame. validator.py does not
modify the data. It only decides which rows pass and which are quarantined. db_loader.py writes to SQLite. Each
module has one responsibility, so a failure can be traced to the stage that caused it.
Each module uses functional programming patterns so transformations are easy to swap out. If you need a different
connector, say Dropbox instead of Google Drive, you replace drive_connector.py without touching anything
downstream.
Database tables:
| Table | Contents |
|---|---|
consolidated | Clean rows that passed all validation rules |
quarantine | Failed rows with original data preserved, source file, row number, and plain-English reason |
cleaning_log | Every transformation applied: column renames, date normalization, numeric cleaning, duplicate removals |
Google Drive Integration
The Drive connector is the optional layer. It authenticates via OAuth 2.0, lists all .xlsx/.xls/.csv files in
a specified folder, downloads them, and hands the local folder path to consolidator.py. The same pipeline runs as
if the files were always local. The integration code is fully readable in the repo. The credentials live in .env
and are never committed.
# Run the pipeline against a Drive folder .venv/bin/python src/consolidator.py --source gdrive --folder-id YOUR_FOLDER_ID .venv/bin/python src/db_loader.py --full .venv/bin/streamlit run dashboard/app.py
This pattern, separating the integration code (public, readable) from the credentials (private, gitignored), is a deliberate design choice. It means anyone can review exactly how the Drive connection works without needing access to your Google Cloud project.
Engineering Notes
The source_file and source_row metadata has to be attached before any cleaning happens, not after. If you tag
rows after duplicate removal and empty-row filtering, the row numbers in your quarantine table point to positions in
the cleaned DataFrame, not the original file. That makes the quarantine reasons useless for tracing back to the
source. Tagging first, cleaning second keeps every reference accurate.
The sample files are the test spec. Writing validation logic against an abstract rule list and backfilling test data afterward means you miss edge cases. Define the inputs first, then write the code that handles them.
Live Dashboard
The application below is hosted separately from this static site. If it is asleep or unavailable, use the preview and direct link near the top of this page.
Quick Start
git clone <repo> cd excel_consolidator python3 -m venv .venv .venv/bin/pip install -r requirements.txt .venv/bin/python src/consolidator.py --input data/sample_files/ .venv/bin/python src/db_loader.py .venv/bin/streamlit run dashboard/app.py
No credentials needed. The repo ships with eight sample files that demonstrate every edge case the pipeline handles.
Tools: Python, pandas, openpyxl, SQLite, PyYAML, Streamlit, google-api-python-client