Watching a Folder for New Excel Files and Importing Them

Branches drop their weekly workbook into a shared folder. Someone then has to notice, open it and load it. That someone goes on holiday, and the report is missing two weeks. A folder watcher fixes the dependency on a person: when a file appears, a program reads it, checks it and loads it. Most of the difficulty is in the edge cases: a file seen half-copied, a file seen twice, a crash in the middle of a load. This post builds a watcher that handles them. It is a spoke of automating Excel work without clicking.

Choose how to detect files

Two approaches.

Polling. A script runs every minute (from cron or a loop), lists the incoming folder and processes what it finds. It is trivial, restartable and works on every filesystem, including network shares. The delay is up to a minute, which suits most reporting.

File system events. The watchdog package listens for created and modified events and calls your code immediately. It works well for local disks. On network shares (SMB, NFS) events are unreliable: they can be missed or delayed, and some clients never produce them. A common compromise is to use events for speed and a polling pass as a safety net.

Start with polling. It has fewer ways to fail, and the delay costs nothing for a weekly report.

Wait until the file is finished

The trap in both approaches is seeing a file before it is complete. A large workbook copied over a network appears in the folder at once and grows for seconds or minutes. Reading it then gives a zip error or a truncated sheet (fixing BadZipFile when reading an XLSX).

Options, from simplest to best:

1. Stable size. Check the file size twice a few seconds apart; process only if unchanged.

2. Age. Ignore files modified in the last 30 seconds.

3. A marker file. The sender writes report.xlsx, then an empty report.xlsx.done. The watcher acts on the marker. It is the most reliable and needs the sender's cooperation.

4. Rename on completion. The sender uploads to a temporary name and renames to the final one when done. Renaming within a filesystem is atomic.

import time
from pathlib import Path

def is_stable(path, wait=3):
    s1 = path.stat().st_size
    time.sleep(wait)
    return s1 > 0 and s1 == path.stat().st_size

The size check misses a writer that pauses, which is why the marker or the rename convention is better when you control the senders. Lock files named ~$name.xlsx, created by Excel while a person has the workbook open, must be skipped: they are tiny and not workbooks.

The folder layout

Use folders as states:

inbox/        files arrive here
processing/   a file moves here while being handled
done/         loaded successfully
rejected/     failed a check, with a .reason.txt beside it

Moving a file to processing/ first claims it: a second watcher instance will not see it in inbox/. After the load, move it to done/ or rejected/. A glance at the folders shows the state of the system, and a stuck file in processing/ points at a crash.

Make the load idempotent

A watcher will see the same file twice at some point: a restart, a duplicate drop, a manual copy. The load must be safe to repeat. Record each loaded file by a hash of its contents in a table:

CREATE TABLE loaded_files (
    sha256     text PRIMARY KEY,
    file_name  text NOT NULL,
    rows_loaded integer NOT NULL,
    loaded_at  timestamptz NOT NULL DEFAULT now()
);

Compute the hash and skip if it exists:

import hashlib

def sha256(path):
    h = hashlib.sha256()
    with open(path, "rb") as f:
        for chunk in iter(lambda: f.read(1 << 20), b""):
            h.update(chunk)
    return h.hexdigest()

Do the insert into loaded_files in the same transaction as the data load. Either both commit or neither does, so a crash halfway leaves no trace and the file can be retried. A hash catches the same bytes under a different name; if a corrected file has different bytes and the same business keys, the load needs an upsert or a delete-and-replace keyed on the period (upserting JSON API records into a table shows the pattern in another context).

The processing function

import shutil
import pandas as pd
from sqlalchemy import text

EXPECTED = ["order", "date", "customer", "amount"]

def process(path, engine, dirs):
    digest = sha256(path)
    with engine.begin() as conn:
        if conn.execute(text("SELECT 1 FROM loaded_files WHERE sha256 = :h"), {"h": digest}).first():
            return "duplicate"
        df = pd.read_excel(path, sheet_name="Data", dtype=str)
        df.columns = [str(c).strip().lower() for c in df.columns]
        if df.columns.tolist() != EXPECTED:
            raise ValueError(f"unexpected columns {df.columns.tolist()}")
        df["amount"] = pd.to_numeric(df["amount"], errors="raise")
        df["date"] = pd.to_datetime(df["date"], format="%Y-%m-%d", errors="raise")
        df["source_file"] = path.name
        df.to_sql("staging_orders", conn, if_exists="append", index=False)
        conn.execute(text("INSERT INTO loaded_files (sha256, file_name, rows_loaded) VALUES (:h, :n, :r)"),
                     {"h": digest, "n": path.name, "r": len(df)})
    return "loaded"

engine.begin() opens a transaction that commits on success and rolls back on any exception. errors="raise" makes the conversions strict: a bad amount rejects the whole file instead of loading part of it. Whether to reject the file or load the good rows and report the bad ones is a business decision, and rejecting is the safer default. Load into a staging table first and validate with SQL before moving rows to the real tables (staging Excel data in a table).

The watcher loop

import logging
from pathlib import Path

def run_once(engine, root):
    inbox, proc, done, rej = (root / d for d in ("inbox", "processing", "done", "rejected"))
    for path in sorted(inbox.glob("*.xlsx")):
        if path.name.startswith("~$") or not is_stable(path):
            continue
        claimed = proc / path.name
        shutil.move(path, claimed)
        try:
            result = process(claimed, engine, root)
            shutil.move(claimed, done / path.name)
            logging.info("%s %s", result, path.name)
        except Exception as e:
            shutil.move(claimed, rej / path.name)
            (rej / (path.name + ".reason.txt")).write_text(repr(e))
            logging.exception("rejected %s", path.name)

Run it from cron every minute (* * * * *) with a lock so overlapping runs cannot happen. For the event-driven version, watchdog replaces the schedule: an on_created handler calls run_once. Keep the polling pass as a safety net. Name collisions in done/ are possible if two senders use the same file name; prefix the destination with a timestamp or the hash.

Monitoring

A watcher that fails quietly is the reason to build one in the first place, so give it a pulse. Count files per state each day and send a short message: loaded N, rejected M, pending K. Alert if processing/ holds a file older than an hour, if rejected/ grows, or if nothing arrived on a day when something should have. The last check, the absence of a file, catches the sender's failure, which no amount of code on your side detects otherwise (checking totals between two Excel reports is a good check once the data is in).

Security

A folder that anyone can write to is an input from the outside. Validate size and type before reading, set a limit on what the reader may allocate and never open macro-enabled files with anything that executes them. Run the watcher under an account with write access only to its own folders and the staging table.

When a folder watcher is more than you need

The files arrive on a predictable schedule. A cron job that looks for this week's file at a fixed time is simpler.

People can use a form. A web form or an upload endpoint validates at the point of entry and gives the sender immediate feedback.

Power Query is enough. If the destination is an Excel report, a query over the folder refreshes with new files (combining many files from a folder with Power Query).

The volume is tiny. One file a month does not need a service.

See the watchdog documentation and Python's page on hashlib. To convert a small result to JSON, use the CSV to JSON converter.