Download New Excel Files from an SFTP Server

Banks, logistics providers, payroll bureaus and marketplaces often deliver their reports by dropping files on an SFTP server: a new stock file every night, a settlement report every morning. The receiving side needs a job that runs on a schedule, picks up the files it has not seen before, and leaves the rest alone. The details are where these jobs fail: downloading a file that the partner is still uploading and getting half a workbook, fetching everything again on every run, missing a file the partner re-uploaded with corrections under the same name, or connecting to whatever server answers because the host key was never checked. This page builds a downloader with paramiko that handles each of those: a pinned host key, a listing with sizes and times, a rule for files still being written, a state file that records what was fetched, downloads that only appear under their final name when complete, and a check that each workbook opens. Everything is tested against an SFTP server running locally. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install paramiko openpyxl

The partner publishes files in an outgoing folder on their SFTP server. The job authenticates with its own SSH key, and the partner's host key has been received out of band — by email from the partner or read from their documentation — and stored in a known_hosts file. The local test server here is started with asyncssh; the downloader does not know or care that it is local.

Step 1 — Connect, refusing unknown servers

import paramiko

def connect(host="127.0.0.1", port=8022, user="reports"):
    client = paramiko.SSHClient()
    client.load_host_keys("known_hosts")                       # the partner's published host key
    client.set_missing_host_key_policy(paramiko.RejectPolicy())
    client.connect(host, port=port, username=user, key_filename="id_ed25519_reports",
                   look_for_keys=False, allow_agent=False, timeout=30)
    return client

client = connect()
sftp = client.open_sftp()
print(sorted(sftp.listdir("/outgoing")))

The connection loads the partner's host key from known_hosts and uses RejectPolicy, which refuses to connect if the server presents any other key. That is the point of SFTP's security: without the check, the job would hand its credentials to, and accept files from, any machine that answered at that address. The job logs in with a dedicated key, not a person's, and turns off the agent and default key search so it only ever uses that one. A timeout stops a stuck connection from hanging the job. The listing shows what the partner has published: three finished workbooks, one workbook that was written moments ago, a partial upload with a .part suffix and a text file.

Step 2 — Decide which files to fetch

import json, stat, time
from pathlib import Path

STATE = Path("sftp_state.json")
seen = json.loads(STATE.read_text()) if STATE.exists() else {}
SETTLE = 120                                                    # seconds without change before a file counts as finished

def candidates(sftp, folder="/outgoing"):
    now = time.time()
    picked, skipped = [], []
    for a in sftp.listdir_attr(folder):
        name, reason = a.filename, None
        if not stat.S_ISREG(a.st_mode):
            reason = "not a file"
        elif not name.lower().endswith(".xlsx") or name.startswith("~$"):
            reason = "not a workbook"
        elif now - a.st_mtime < SETTLE:
            reason = "still being written"
        elif seen.get(name) == [a.st_size, int(a.st_mtime)]:
            reason = "already downloaded"
        (skipped if reason else picked).append((name, a.st_size, int(a.st_mtime), reason))
    return picked, skipped

picked, skipped = candidates(sftp)
print("fetch:", [p[0] for p in picked])
print("skip:", [(s[0], s[3]) for s in skipped])

listdir_attr returns each entry with its size, modification time and type in a single request, which is all the information the decision needs. Directories and other non-files are skipped, as is anything that is not an .xlsx workbook — the partial upload ends in .part, the readme is text. A workbook modified in the last two minutes is treated as still being written and left for the next run: there is no reliable way to know from the outside that an upload has finished, but a file whose time has not moved for a couple of minutes almost always has. Finally, the state file records each downloaded file's size and modification time; a file with the same name, size and time as before is skipped, while a file the partner re-uploaded with corrections — same name, new time or size — is fetched again.

Step 3 — Download safely and check each workbook

from openpyxl import load_workbook

dest = Path("incoming")
dest.mkdir(exist_ok=True)
for name, size, mtime, _ in picked:
    tmp = dest / (name + ".part")
    sftp.get(f"/outgoing/{name}", str(tmp))
    if tmp.stat().st_size != size:
        tmp.unlink()
        print(f"{name}: size changed during download, will retry next run")
        continue
    try:
        with open(tmp, "rb") as fh:                             # a file object: openpyxl rejects the .part name
            load_workbook(fh, read_only=True).close()           # a quick check that it is a real workbook
    except Exception as e:
        tmp.rename(dest / (name + ".bad"))
        print(f"{name}: not a valid workbook ({type(e).__name__})")
        continue
    final = dest / name
    if final.exists():                                          # a corrected re-upload: keep the earlier copy
        final.rename(dest / f"{final.stem}.{time.strftime('%Y%m%d%H%M%S', time.localtime(seen[name][1]))}{final.suffix}")
    tmp.rename(final)
    seen[name] = [size, mtime]
    print(f"{name}: downloaded ({size:,} bytes)")
STATE.write_text(json.dumps(seen, indent=1))
client.close()

Each file is downloaded to a .part name and renamed only when it is complete and checked, so anything that watches the incoming folder never sees half a file. Two checks run before the rename: the downloaded size must match the size in the listing — if the partner changed the file mid-download, the copy is discarded and the next run tries again — and the workbook must open with openpyxl in read-only mode, which takes a moment even for large files. The check opens the file through a file object rather than by path, because openpyxl refuses to open a path whose extension is not one it recognises, and .part is not; an earlier version of this code, passing the path, rejected every download as invalid. A file that fails the second check is kept under a .bad name for investigation rather than deleted. When a corrected version of a file already downloaded arrives, the earlier copy is renamed with its timestamp rather than overwritten, so nothing that has already been processed is lost. The state file is written at the end of the run with every file that made it.

Step 4 — Run it again

client = connect()
sftp = client.open_sftp()
picked, skipped = candidates(sftp)
print("second run fetches:", [p[0] for p in picked])
print("already downloaded:", [s[0] for s in skipped if s[3] == "already downloaded"])
client.close()

A second run straight afterwards fetches nothing new: the three files from the first run are recognised from the state file, and the newest stock file is still within its settling time. Once it has been untouched for two minutes, the next scheduled run picks it up. That is the behaviour a scheduled job needs — safe to run every fifteen minutes, never fetching twice, never fetching early. If the partner allows it, an alternative to the state file is to move each fetched file into a processed folder on the server with sftp.rename, which makes the server itself the record; many partners give read-only access, so the state file is the approach that always works.

Common pitfalls

• AutoAddPolicy. It trusts any server; pin the partner's host key and use RejectPolicy.
• Fetching files still being uploaded. Wait until a file's time has not changed for a while.
• Deduplicating by name only. Corrected re-uploads keep the name; compare size and time too.
• Writing straight to the final name. Download to .part and rename when complete.
• Overwriting earlier versions. Keep the previous copy when a corrected file arrives.

Conclusion

To pick up new Excel files from an SFTP server, connect with paramiko using a pinned host key and RejectPolicy, list the folder with listdir_attr, and fetch only .xlsx files that have settled and whose name, size and time are not in the state file. Download each to a temporary name, check its size and that it opens, then rename it, keeping any earlier version, and record it in the state file so the job can run as often as needed without fetching anything twice.

Frequently asked questions

How do I download only new files from SFTP in Python? Keep a record of each downloaded file's name, size and modification time, and fetch only files whose entry is missing or different.

How do I avoid downloading a file that is still being uploaded? Skip files whose modification time is within the last few minutes, and check the downloaded size against the listing.

Should I use AutoAddPolicy with paramiko? No; load the partner's host key from a known_hosts file and use RejectPolicy so the job refuses unknown servers.

How do I test SFTP code without a real server? Run a local SFTP server, for example with asyncssh, with its own host key and an authorised client key.

Related

• Up one level: Moving Data Between Excel and Databases — Excel and data sources.
• Upload an Excel Report over SFTP with Python — the sending side.
• Read Excel Attachments into a Database Nightly — loading what arrives.
• Wait for Upstream Data Before Running a Report — when the file is late.