Watching a Folder for New CSV Files and Importing Them

The tempting design for an import job is a program that sits in a loop, notices a new file in a folder and loads it at once. It feels responsive. It is also where most half-loaded days come from, because "a file appeared" and "a file is finished" are different events, and the gap between them can last minutes on an SFTP upload of a few hundred megabytes. This article builds a watcher that waits for a file to settle, shows it detecting a file that was written slowly, and lays out the better signals a producer can give you. It is the event-driven version of the importer in automating CSV imports into a database.

Why create events are not enough

Operating systems tell programs when files change, and the tools that expose that (inotify on Linux, FSEvents on macOS, ReadDirectoryChangesW on Windows) report a create the moment the directory entry exists. The producer then writes data for as long as it takes. An FTP or SFTP server writes to the final name as bytes arrive, a curl -o does the same, and a database export tool may write for an hour. A watcher that triggers on create opens the file while it is still growing, reads to the end of what is there and stops. The file is syntactically fine at any cut that lands between rows, so the import succeeds and loads part of the day.

Modification events do not fix it by themselves: they fire for every write, so you either react to each one or you wait for events to stop, which is polling by another route. What you need is a notion of finished, and there are three, in descending order of reliability.

Signals the producer can give

An atomic rename. The producer writes orders.csv.part (or into a tmp/ folder on the same file system) and renames it to its final name when done. A rename on one file system is atomic, so the final name appears only when the content is complete. The watcher ignores .part and acts on moved_to or on a glob that matches only final names. This is the cheapest thing to ask a provider for, and the right answer when they control the upload.

A done marker. The producer writes the data file, then a small orders-2026-10-02.done file (or a manifest with the row count and checksum). The watcher imports a data file only when its marker exists, and can verify the count and hash after loading. It costs the producer one more file and gives you a number to check.

Close-after-write events. On Linux, inotify has close_write, which fires when a file opened for writing is closed. Used with moved_to, it is a good trigger for local writes:

inotifywait -m -e close_write -e moved_to --format '%w%f' /srv/incoming \
  | while read -r path; do
      case "$path" in *.csv) /usr/local/bin/import_orders.sh "$path" ;; esac
    done

-m keeps monitoring, and each finished CSV is handed to the import script. This works when the writer is a local process that closes the file at the end. It does not work across a network mount, where the server may not deliver events, and an SFTP daemon that holds the handle open until the session ends delays the event, which is acceptable. macOS has no equivalent of close_write, so a portable watcher polls.

A stable-file poller

When none of the signals is available, test for stability. A file is stable when its size and modification time are the same on two looks in a row. Here is a stdlib poller:

import time
from pathlib import Path

INCOMING = Path("incoming")
INTERVAL = 1.0            # seconds between looks; use 30 or 60 in production
STABLE_LOOKS = 2          # unchanged size and mtime on this many looks in a row

seen = {}                 # path -> (size, mtime, stable_count)

def poll():
    ready = []
    for p in sorted(INCOMING.glob("*.csv")):
        st = p.stat()
        key = (st.st_size, st.st_mtime_ns)
        old = seen.get(p)
        count = old[2] + 1 if old and old[:2] == key else 0
        seen[p] = (*key, count)
        if count >= STABLE_LOOKS:
            ready.append(p)
    return ready

t0 = time.monotonic()
while time.monotonic() - t0 < 8:
    for p in poll():
        print(f"{time.monotonic() - t0:4.1f}s ready: {p.name} ({p.stat().st_size} bytes)")
        p.rename(p.with_suffix(".csv.taken"))
        seen.pop(p, None)
    time.sleep(INTERVAL)

To test it, a second process wrote four lines into incoming/orders-late.csv, flushing after each and sleeping 1.2 seconds between writes, so the file kept growing for about 4.8 seconds:

 6.0s ready: orders-late.csv (24 bytes)

The watcher saw the file at the first look after creation and refused it while the size kept changing. After the last write at about 3.6 seconds, the file stayed the same for two looks, and it was handed over at 6.0 seconds. It never fired on a partial file. In production, set the interval to 30 or 60 seconds and require two or three stable looks, which makes the delay one to three minutes, a fair price for correctness on a daily feed.

The poller renames the file on pickup (to .csv.taken here), which both prevents a second pick-up and acts as a claim: a second watcher that tries the same rename fails. In a real job the importer would take the file under a lock and move it to the archive once the load commits (archiving processed CSV files automatically).

Where polling goes wrong

A stability check can be fooled by a stalled upload. A transfer that pauses for ninety seconds while a link recovers looks identical to a finished file, and the watcher imports half a file. Longer windows reduce the risk and never remove it, which is why a done marker or a rename beats a stability check whenever you can get one. Add a second defense independent of the watcher: after loading, compare the row count with the manifest if there is one, or with the recent average, and flag a file with a fraction of the usual rows (testing that a CSV export was not truncated).

Network file systems add their own surprises. A file on NFS may show a stale size or modification time for seconds, so use a longer window there. An upload from a Windows machine that sets a modification time in the past can make a new file look old, which matters if your logic compares times to the clock: compare only a file against its own previous state, as above.

Do not use the poller's own state as the record. If the watcher restarts, its memory of stable counts is gone, which is fine, since it starts again and the file is still stable. The record of what was imported is the ledger in the database, keyed by content hash. A watcher that misses an event or fires twice cannot harm anything if the importer is idempotent (idempotent CSV imports that are safe to run twice).

Running it as a service

A loop that never exits needs supervision: run it under systemd with Restart=on-failure, log to the journal, and alert if it stops. Make the loop survive an exception per file: a bad file is moved to a failed folder with the reason, and the loop continues with the next, rather than crashing and leaving the rest. Keep it small. The watcher's job is to hand a path to the importer, and everything else belongs in the importer.

Many teams find that the watcher is more machinery than the job needs, which brings up the alternative.

When a schedule is simpler than a watcher

If a file arrives once a day, a cron job at a fixed time that checks the folder is simpler and nearly as responsive. Run it every five minutes during the window the provider uses, let it do nothing when the folder is empty, and use the same stability check or a marker. There is no long-running process to supervise, nothing to restart after a deploy and the log is a series of short runs. Scheduling CSV reports from SQL with cron covers the cron details, including the lock that stops overlapping runs.

A watcher earns its keep when files arrive unpredictably and latency matters: minutes, not hours. For object storage, the equivalent of a watcher is an event notification that queues each new object (loading a CSV from S3 into a database), with the same caveat that the object must be complete before the message is sent. The Linux notification interface is documented in the inotify manual page, and the atomicity of rename in the POSIX specification.