Upload an Excel Report over SFTP with Python

Plenty of organisations still exchange reports by dropping files on an SFTP server: a bank expects a reconciliation workbook every morning, a supplier collects a stock report, a regulator's portal accepts uploads over SSH. The protocol is old and simple, which makes it easy to get the dangerous parts wrong — trusting any server key, or letting the partner's import job read a file that is still being written. This guide, part of Publishing Excel Reports to Cloud Storage, uploads a report with paramiko the careful way. Each behaviour described was checked against a local SFTP server.

Prerequisites

pip install paramiko pandas openpyxl

You need the server's host name, a user account and the server's public host key, which the partner should send you through a separate channel. Generate a key pair for the script and give the partner the public half:

ssh-keygen -t ed25519 -f reports_sftp_key -N "" -C "report-uploads"

An empty passphrase lets the job run unattended; protect the private key file with permissions instead (chmod 600).

Step 1 — Connect, and check who you are talking to

Many examples start with set_missing_host_key_policy(paramiko.AutoAddPolicy()), which teaches a script to accept any server's key — including an impostor's. For an unattended job that sends financial data, pin the key. Put the partner's host key in a known_hosts file you control and keep paramiko's default policy, which refuses unknown servers:

import paramiko

def connect(host, port, user, key_file, known_hosts="known_hosts"):
    client = paramiko.SSHClient()
    client.load_host_keys(known_hosts)
    client.set_missing_host_key_policy(paramiko.RejectPolicy())     # the default, made explicit
    client.connect(host, port=port, username=user, key_filename=key_file,
                   look_for_keys=False, allow_agent=False, timeout=30)
    return client

A known_hosts line for a server on a non-standard port looks like [sftp.partner.example]:2222 ssh-ed25519 AAAAC3Nz…. With no matching entry, the connection fails with SSHException: Server '[host]:port' not found in known_hosts; if the server presents a different key, it fails with BadHostKeyException: Host key for server … does not match. Both are exactly what you want: an alert, not an upload. look_for_keys=False and allow_agent=False make the script use only the key you gave it, so it behaves the same on every machine.

Step 2 — Upload under a temporary name, then rename

Partners usually poll a folder and import every .xlsx that appears. If the file appears under its final name while it is still being written, they import half a workbook — a BadZipFile on their side, or worse, a truncated one that happens to open. Upload under a .part name, confirm the size, then rename:

import io

def deliver(sftp, data, remote_dir, filename):
    final = f"{remote_dir}/{filename}"
    partial = final + ".part"
    attrs = sftp.putfo(io.BytesIO(data), partial, file_size=len(data), confirm=True)
    if attrs.st_size != len(data):
        raise IOError(f"{partial}: {attrs.st_size} bytes on server, {len(data)} sent")
    sftp.posix_rename(partial, final)
    return final

putfo uploads from a file object, so a report built in memory never touches the local disk. With confirm=True paramiko checks the size on the server after the upload and raises if it differs; the explicit check above makes the rule visible.

Use posix_rename rather than rename. SFTP's standard rename refuses to replace an existing file — against the test server, delivering the same report twice failed with OSError: File already exists — while posix_rename, an OpenSSH extension most servers support, replaces it in one step. If the partner's rules say a delivered file must never be replaced, use rename precisely because it refuses.

Step 3 — Create dated folders on the server

Many drop zones expect inbound/2026/09/. SFTP has no mkdir -p; create each level that does not exist:

def makedirs(sftp, path):
    current = ""
    for part in [p for p in path.split("/") if p]:
        current += "/" + part
        try:
            sftp.stat(current)
        except FileNotFoundError:
            sftp.mkdir(current)

stat on a missing path raises FileNotFoundError, which makes the check readable. Some servers forbid creating directories; if the partner pre-creates them, skip this step rather than failing on PermissionError.

Step 4 — Put it together, with a read-back check

The complete delivery connects, creates the folder, uploads, renames, and reads the file back to compare its hash — a cheap check for a report of a few megabytes:

import hashlib
import pandas as pd

def upload_report(data, period, host="sftp.partner.example", port=22,
                  user="acme-reports", key_file="reports_sftp_key"):
    remote_dir = f"/inbound/{period[:4]}/{period[5:]}"
    with connect(host, port, user, key_file) as client, client.open_sftp() as sftp:
        makedirs(sftp, remote_dir)
        final = deliver(sftp, data, remote_dir, f"stock_{period}.xlsx")
        with sftp.open(final, "rb") as remote:
            remote.prefetch()
            if hashlib.sha256(remote.read()).digest() != hashlib.sha256(data).digest():
                raise IOError(f"{final}: content differs after upload")
    return final

buffer = io.BytesIO()
pd.DataFrame({"sku": ["A-100", "B-220"], "on_hand": [42, 7]}).to_excel(buffer, index=False)
print(upload_report(buffer.getvalue(), "2026-09"))

prefetch() speeds up reading a file back by requesting blocks in parallel; without it, paramiko reads over SFTP noticeably slowly. Many drop zones are write-only and refuse reads; there, rely on the size check from Step 2.

Step 5 — Follow the partner's conventions

The upload is only half of an exchange. Agree these with the receiving side and encode them in the job:

• File names. Fixed patterns such as stock_YYYY-MM.xlsx let their import match files without guessing. Avoid spaces and non-ASCII characters.
• Done markers. Some partners want an empty stock_2026-09.ok written after the workbook instead of a rename. Upload it last, with putfo(io.BytesIO(b""), …).
• Retries. Transient network failures are worth two or three retries with a pause, as in Retry a Failed Excel Report Job in Python. Host key and authentication failures are not — alert on them straight away.
• Clean-up. A failed run can leave a .part file behind. Delete leftovers at the start of the next run, or the partner will ask about them.

Step 6 — Test against a local SFTP server

Waiting for the partner's test environment slows every change. asyncssh can run a real SFTP server in a few lines, rooted in a local folder, which is how the behaviour in this guide was checked:

import asyncio
import asyncssh

class DropZone(asyncssh.SFTPServer):
    def __init__(self, channel):
        super().__init__(channel, chroot=b"test_remote")

async def serve():
    await asyncssh.listen("127.0.0.1", 8022, server_host_keys=["test_host_key"],
                          authorized_client_keys="test_client_key.pub", sftp_factory=DropZone)
    await asyncio.Event().wait()

asyncio.run(serve())

Generate the two keys once with asyncssh.generate_private_key("ssh-ed25519"), write the host key's public half into a test known_hosts, and point upload_report at 127.0.0.1:8022. The test server gives the same errors as a real one for an unknown host key, a changed key and a rename onto an existing file, so the failure paths can be tested too — not only the happy path.

Common pitfalls

• AutoAddPolicy in production. It silently trusts the first key it sees and every key after a known_hosts is lost. Pin keys.
• Passwords in the script. If the partner insists on password login, read it from the environment or a keyring and ask them for key-based access.
• Host key rotation. When the partner changes servers, uploads fail with BadHostKeyException until you update known_hosts from the new key they send you — never by switching to AutoAddPolicy "for now".
• Leaving connections open. A job that opens a connection per file and never closes it can hit the server's session limit halfway through a batch. The with blocks in Step 4 close both the SFTP channel and the SSH connection, even when an upload fails.
• Line-ending conversions. SFTP transfers bytes unchanged, unlike FTP's text mode. Do not open the workbook in text mode anywhere in the pipeline.

Conclusion

Pin the server's host key in a known_hosts file and keep RejectPolicy, log in with a dedicated private key, upload from memory with putfo under a .part name, confirm the size, rename with posix_rename (or rename when replacing must fail), create dated folders level by level, and read the file back when the server allows it. Agree names, markers and clean-up with the partner.

Frequently asked questions

How do I upload a file over SFTP in Python? With paramiko, create an SSHClient, load known host keys, connect with a username and private key, call open_sftp() and then put(local_path, remote_path) or putfo(file_object, remote_path) for data in memory.

Is AutoAddPolicy safe for an automated SFTP upload? No. AutoAddPolicy trusts whatever key the server presents on first contact, so the script would happily send reports to an impostor. Store the partner's host key in a known_hosts file you control and keep paramiko's default RejectPolicy.

How do I stop the receiving side picking up a half-uploaded file? Upload under a temporary name such as report.xlsx.part and rename it to the final name once the upload is complete and verified. Partners that poll the folder for .xlsx files then only ever see complete files.

Why does sftp.rename fail with "File already exists"? SFTP's standard rename refuses to replace an existing file. Use sftp.posix_rename, supported by OpenSSH servers, to replace the target in one step, or remove the old file first if the server lacks the extension.

Related

• Up one level: Publishing Excel Reports to Cloud Storage — where finished reports go.
• Write Excel Files to a Network Share from Python — the same temporary-name trick on a Windows share.
• Upload an Excel Report to Azure Blob Storage with Python — when the partner can read from cloud storage instead.
• Skip a Report Run When the Input File Has Not Changed — the receiving side of a file drop.