Read Excel Attachments into a Database Nightly

Branches, suppliers and partners still send data the simplest way they can: an Excel file attached to an email. Once a job saves those attachments into a folder — the subject of Download Excel Attachments from an Email Inbox — the next job has to get them into a database, every night, unattended. That second job is where the trouble usually is. The same file gets sent twice and loaded twice. A file with a renamed column loads half its rows before failing. A corrected file arrives for a day already loaded, and the database ends up with both versions. This page builds a nightly loader that handles all of that: files identified by a hash of their content so a resend is skipped, each workbook validated before anything is written, each file loaded in its own transaction with an upsert on the business key so a corrected file replaces rather than duplicates, every attempt recorded in a load log, rejected files moved to a quarantine folder, and a short summary at the end. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install pandas openpyxl

The examples use SQLite, which is part of Python; the same pattern works with PostgreSQL or SQL Server through SQLAlchemy, using their own upsert syntax. Tonight's inbox folder holds six files: two branch returns, an identical resend of one of them, a file with renamed columns, a corrected version of the York return, and an attachment that is not a workbook at all.

Step 1 — The tables: data and a load log

import sqlite3
from contextlib import closing

DB = "sales.db"
with closing(sqlite3.connect(DB)) as con, con:
    con.executescript("""
    CREATE TABLE IF NOT EXISTS daily_sales (
        branch TEXT NOT NULL, date TEXT NOT NULL, product TEXT NOT NULL,
        units INTEGER NOT NULL CHECK (units >= 0), revenue REAL NOT NULL,
        source_file TEXT NOT NULL, loaded_at TEXT NOT NULL,
        PRIMARY KEY (branch, date, product));
    CREATE TABLE IF NOT EXISTS load_log (
        file_hash TEXT PRIMARY KEY, file_name TEXT, status TEXT, rows INTEGER, message TEXT, loaded_at TEXT);
    """)
print("tables ready")

The data table's primary key is the business key — branch, date and product — the combination that identifies one fact. Making it the key is what allows a corrected file to replace the earlier figures instead of adding a second set: there can only ever be one row for York, 1 October, product A. Each row also records the file it came from and when, which is how a figure in a report can be traced back to an email. The load log has one row per file content, keyed by a hash of the bytes, with the outcome and a message. It is both the job's memory — which files have been handled — and its audit trail.

Step 2 — Validate a workbook before writing anything

import pandas as pd

EXPECTED = ["Branch", "Date", "Product", "Units", "Revenue"]

def read_and_validate(path):
    df = pd.read_excel(path)
    missing = [c for c in EXPECTED if c not in df.columns]
    if missing:
        raise ValueError(f"missing columns {missing}; found {list(df.columns)}")
    df = df[EXPECTED].copy()
    df["Date"] = pd.to_datetime(df["Date"], errors="coerce").dt.strftime("%Y-%m-%d")
    bad = df[df["Date"].isna() | (pd.to_numeric(df["Units"], errors="coerce") < 0) | df[["Branch", "Product"]].isna().any(axis=1)]
    if len(bad):
        raise ValueError(f"{len(bad)} invalid row(s), first at row {bad.index[0] + 2}")
    if df.duplicated(["Branch", "Date", "Product"]).any():
        raise ValueError("the same branch, date and product appears twice in the file")
    return df

Every check runs before the database is touched. The columns must be the expected ones — the Hull file, with Day and Qty instead of Date and Units, fails here with a message that lists what it found. Dates must parse, units must not be negative, keys must be present, and the file must not contain the same branch, date and product twice. A file is either entirely valid or rejected entirely; loading the good rows of a bad file leaves a day half-reported, which is worse than not loading it at all. More thorough row-level checks are covered in Validate Excel Columns Before Import with pandas.

Step 3 — Load each file once, in its own transaction

import hashlib, shutil
from datetime import datetime
from pathlib import Path

UPSERT = """INSERT INTO daily_sales (branch, date, product, units, revenue, source_file, loaded_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT (branch, date, product) DO UPDATE SET
              units = excluded.units, revenue = excluded.revenue,
              source_file = excluded.source_file, loaded_at = excluded.loaded_at"""

def load_folder(inbox, processed, quarantine):
    results = []
    for path in sorted(Path(inbox).glob("*.xlsx"), key=lambda p: p.stat().st_mtime):
        digest = hashlib.sha256(path.read_bytes()).hexdigest()
        now = datetime.now().isoformat(timespec="seconds")
        with closing(sqlite3.connect(DB)) as con:
            if con.execute("SELECT 1 FROM load_log WHERE file_hash = ? AND status = 'loaded'", (digest,)).fetchone():
                results.append((path.name, "skipped", 0, "identical file already loaded"))
                shutil.move(path, Path(processed) / path.name)
                continue
            try:
                df = read_and_validate(path)
                with con:                                            # one transaction per file
                    con.executemany(UPSERT, [(*r, path.name, now) for r in df.itertuples(index=False)])
                    con.execute("INSERT OR REPLACE INTO load_log VALUES (?, ?, 'loaded', ?, '', ?)", (digest, path.name, len(df), now))
                results.append((path.name, "loaded", len(df), ""))
                shutil.move(path, Path(processed) / path.name)
            except Exception as e:
                with con:
                    con.execute("INSERT OR REPLACE INTO load_log VALUES (?, ?, 'rejected', 0, ?, ?)", (digest, path.name, str(e)[:200], now))
                results.append((path.name, "rejected", 0, str(e)[:80]))
                shutil.move(path, Path(quarantine) / path.name)
    return pd.DataFrame(results, columns=["File", "Status", "Rows", "Message"])

for d in ("processed", "quarantine"):
    Path(d).mkdir(exist_ok=True)
summary = load_folder("inbox", "processed", "quarantine")
print(summary.to_string(index=False))

Files are processed oldest first, so when a corrected file arrives after the original, the correction is applied last. Each file's content is hashed with SHA-256 and looked up in the load log: a file whose exact bytes were already loaded is skipped and filed away, which is how the resent Leeds return is handled — its name differs, its content does not. A valid file is written inside a single transaction together with its log entry: either every row and the log line are committed, or, if anything fails, none of them are. The upsert inserts new rows and, for a branch, date and product that already exist, overwrites the figures — so the corrected York file updates product A from 7 units to 8 without creating a duplicate. Rejected files are logged with the reason and moved to a quarantine folder, out of the way of the next run but kept for whoever has to follow up.

Step 4 — Check the database and report

with closing(sqlite3.connect(DB)) as con:
    print(pd.read_sql("SELECT branch, date, product, units, revenue, source_file FROM daily_sales ORDER BY branch, product", con).to_string(index=False))
    print(pd.read_sql("SELECT status, COUNT(*) AS files FROM load_log GROUP BY status", con).to_string(index=False))

The table now holds one row per branch, date and product: Leeds' two products from the original file and York's two from the corrected one, with product A showing 8 units and the corrected file as its source. Nothing from the Hull file or the non-workbook is there, and the resend added nothing. The load log records every file the job has seen, so tomorrow's run will not reprocess any of them. The night's summary table is what goes to whoever looks after the data — a short email listing loaded, skipped and rejected files, as in Send an Alert When an Excel Report Job Fails, with the rejected ones needing a reply to the sender.

Common pitfalls

• Deduplicating by file name. Resends get new names; hash the content.
• Loading row by row with autocommit. One failure leaves a file half-loaded; use one transaction per file.
• Plain INSERT for daily returns. Corrections then duplicate; upsert on the business key.
• Deleting bad files. Move them to quarantine with the reason in the log.
• No load log. Without it there is no audit trail and no protection against reprocessing.

Conclusion

A nightly loader for emailed workbooks should identify files by a hash of their content and skip ones already loaded, validate each workbook completely before writing, write each file in a single transaction with an upsert on the business key so corrections replace earlier figures, log every file's outcome in a table, and move rejected files to quarantine with the reason. The result is a database that matches the latest version of every return, and a log that explains how it got there.

Frequently asked questions

How do I stop the same Excel file being loaded twice? Hash the file's bytes with SHA-256 and record the hash in a load log; skip any file whose hash is already logged as loaded.

How do I load a corrected file without creating duplicates? Make the business key the primary key and use an upsert — INSERT … ON CONFLICT … DO UPDATE in SQLite and PostgreSQL.

Why load each file in its own transaction? So a failure part-way through leaves the database unchanged for that file instead of half-loaded.

What should happen to files that fail validation? Move them to a quarantine folder, record the reason in the load log and tell the sender.

Related

• Up one level: Moving Data Between Excel and Databases — Excel and databases.
• Download Excel Attachments from an Email Inbox — getting the files.
• Load Excel into SQLite with a Defined Schema — table design.
• Quarantine Bad Excel Files in a Batch Job — handling rejects.