Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Keep Excel Report Settings in a Config File

Move paths, recipients, thresholds and column mappings out of the code: a layered loader with defaults, a TOML or JSON file, environment variables for secrets, and validation that fails at startup.

Every report script accumulates settings: the network path to the source export, the sheet name, the four people who receive it, the threshold that decides what counts as an exception, the mapping from the source system's column names to the ones the finance team recognises. Left in the code, each of them is a reason to edit and redeploy the script for a change that is not a code change at all — and a reason a non-programmer cannot maintain a report they own.

Moving them into a file is easy. Doing it so that the scheduled run, the test run and the ad-hoc rerun can differ, without the password ending up in version control, takes a little more structure. This guide is part of Testing and Packaging Excel Automation Scripts.

Which settings belong in code, in the config file, in the environment and on the command line Report logic and safe defaults stay in the code. Paths, recipients, thresholds and column mappings belong in a config file that a non-programmer can edit. Passwords and per-machine paths belong in environment variables. Only values that change from run to run — a month, a dry run — belong on the command line. in the code the transform the workbook layout safe defaults changing these is a code review config file source path, sheet recipients thresholds, columns edited by whoever owns the report environment SMTP password database URL per-machine paths never committed, set by the scheduler command line --month 2026-07 --dry-run --output true for one run, not for the next

Prerequisites

Bash
pip install pandas openpyxl

On Python 3.11 and newer, tomllib reads TOML from the standard library — no install. On 3.9 or 3.10, either pip install tomli or use JSON, which json handles everywhere. The loader below supports both.

Step 1: Write the file a non-programmer can edit

TOML is the better default because it survives a trailing comma, allows comments, and reads like an ini file to someone who has never seen one:

Toml
# report.toml — settings for the monthly regional report
[source]
path  = "//fileserver/exports/orders.xlsx"
sheet = "Orders"

[output]
directory = "reports"
filename  = "regional-{month}.xlsx"

[rules]
min_amount     = 100.0
exception_pct  = -5.0        # flag regions down more than 5% year on year
drop_test_rows = true

[columns]
"Order Ref"   = "Order_ID"
"Sales Area"  = "Region"
"Net Value"   = "Amount"

[email]
recipients = ["finance@example.com", "ops@example.com"]
subject    = "Regional sales — {month}"
# The password is NOT here. Set REPORT_SMTP_PASSWORD in the environment.

The [columns] block is worth noticing: a mapping from the source system's names to yours is exactly the kind of thing that changes without warning when someone upgrades the export, and exactly the kind of change that should not require a developer. The same goes for the filename template — {month} is filled in at runtime, so nobody has to touch code to change how files are named.

Step 2: Load it in layers

The loader merges four sources in a fixed order, so the more specific always wins:

Python
# config.py
import json
import os
from pathlib import Path

try:
    import tomllib                      # Python 3.11+
except ModuleNotFoundError:             # 3.9 / 3.10
    tomllib = None

DEFAULTS = {
    "source": {"path": None, "sheet": "Orders"},
    "output": {"directory": "reports", "filename": "report-{month}.xlsx"},
    "rules": {"min_amount": 0.0, "exception_pct": -5.0, "drop_test_rows": True},
    "columns": {},
    "email": {"recipients": [], "subject": "Report", "password": None},
}

ENV_MAP = {                             # environment variable -> (section, key)
    "REPORT_SOURCE_PATH": ("source", "path"),
    "REPORT_SMTP_PASSWORD": ("email", "password"),
    "REPORT_OUTPUT_DIR": ("output", "directory"),
}


def _deep_merge(base, extra):
    """Merge `extra` into a copy of `base`, one level of nesting deep."""
    merged = {k: dict(v) if isinstance(v, dict) else v for k, v in base.items()}
    for section, value in (extra or {}).items():
        if isinstance(value, dict) and isinstance(merged.get(section), dict):
            merged[section].update(value)
        else:
            merged[section] = value
    return merged


def load_config(path=None, env=None, overrides=None):
    """defaults < file < environment < explicit overrides (command-line flags)."""
    env = os.environ if env is None else env
    config = _deep_merge(DEFAULTS, {})

    if path:
        path = Path(path)
        if not path.is_file():
            raise FileNotFoundError(f"config file not found: {path}")
        if path.suffix == ".toml":
            if tomllib is None:
                raise RuntimeError("TOML needs Python 3.11+ or `pip install tomli`")
            config = _deep_merge(config, tomllib.loads(path.read_text()))
        else:
            config = _deep_merge(config, json.loads(path.read_text()))

    for var, (section, key) in ENV_MAP.items():
        if var in env:
            config[section][key] = env[var]

    for (section, key), value in (overrides or {}).items():
        if value is not None:           # an unset flag must not erase the file
            config[section][key] = value
    return config

Two rules do most of the work. The environment layer sits above the file so a machine can override a path without editing a shared file, and secrets can arrive without ever being written down. The if value is not None guard stops argparse's unset defaults from wiping settings — without it, running the tool with no --output flag silently resets the output directory to None.

Step 3: Validate at startup, not at the save

A config mistake that surfaces forty seconds into a run, after the source has been read and the transform has finished, wastes the run and buries the cause. Check everything the moment the file is loaded:

Python
def validate(config):
    problems = []

    source = config["source"]["path"]
    if not source:
        problems.append("source.path is not set")
    elif not Path(source).exists():
        problems.append(f"source.path does not exist: {source}")

    if not config["email"]["recipients"]:
        problems.append("email.recipients is empty — nobody would receive the report")

    if not isinstance(config["rules"]["min_amount"], (int, float)):
        problems.append("rules.min_amount must be a number")

    if config["email"]["password"] is None:
        problems.append("REPORT_SMTP_PASSWORD is not set in the environment")

    if problems:
        raise SystemExit("Configuration problems:\n  - " + "\n  - ".join(problems))
    return config

Collecting every problem before raising is deliberate: a loader that stops at the first mistake makes someone fix a typo, rerun, and discover the next one. One message listing all four is one round trip. Raising SystemExit with a string prints the message and exits non-zero without a traceback, which is what a scheduled job's log should contain.

Validating at startup versus discovering the problem mid-run Without startup validation the job reads the source, transforms the data and only fails at the delivery step, forty seconds in, with an error that names a missing recipient rather than the setting behind it. With validation the same problem is reported in under a second, listing every issue at once, before any work is done. no startup validation read transform write send ✗ fails after 40 seconds of work the message names an empty recipient list, not the config key that caused it validate on load load + check read write send stops in under a second and lists every problem at once, by key, so one edit fixes all of them Rule: a setting is checked where it is loaded, not where it is used the cost of the check is the same; the cost of the failure is not

Step 4: Wire it to the command line

The config file and the argparse layer meet in three lines of main:

Python
def main(argv=None):
    args = parse_args(argv)
    config = validate(load_config(
        path=args.config or os.environ.get("REPORT_CONFIG", "report.toml"),
        overrides={
            ("source", "path"): args.source,        # None unless the flag was given
            ("output", "directory"): args.output_dir,
        },
    ))
    return run(config, month=args.month, dry_run=args.dry_run)
Choosing the config file for each environment The scheduled production job sets REPORT_CONFIG once in its own environment and never passes a flag. A developer passes --config with the development file, which wins over the variable. Neither has to edit a shared file, so the two environments cannot cross over. One binary, two environments, no shared file to edit the scheduled job REPORT_CONFIG set once in the unit or crontab config.prod.toml a developer's run --config passed on the command line, one run config.dev.toml neither one falls back to report.toml beside the script so a bare run still works

Selecting the file itself through a flag and an environment variable is what makes multiple environments workable: the scheduled production job sets REPORT_CONFIG=/etc/report/config.prod.toml once, and a developer runs --config config.dev.toml without changing anything that persists.

Step 5: Keep the secrets out of the repository

Commit an example, never the real thing:

Bash
# .gitignore
report.toml
.env

# committed instead
report.example.toml

report.example.toml carries every key with a placeholder value, so a new machine is set up by copying and editing rather than by guessing. For local development a .env file loaded by your shell is fine; on a server, set the variables in the scheduler's environment — cron reads /etc/environment and a systemd unit takes Environment= lines, both of which keep the secret out of any file the report code can accidentally print.

Common pitfalls and gotchas

SymptomCauseFix
A flag has no effectMerge overwrote it with the file, or the guard is missingApply overrides last, skip None
Works locally, fails under cronRelative config path resolved against a different working directoryResolve with Path(__file__).parent or pass an absolute path
Password appears in a logConfig dict logged wholesaleRedact known secret keys before logging
tomllib import errorPython 3.10 or olderpip install tomli, or use JSON
Recipients arrive as one stringJSON edited to "a@x.com, b@x.com"Keep it a list; validate the type
Numbers read as textQuoted in the file: min_amount = "100"Drop the quotes; validate with isinstance
Two environments cross overOne file with an internal switchOne file per environment, selected explicitly

Performance and scale notes

Loading and validating a config file costs a millisecond, so the only scale question is how many reports share one. Past three or four jobs, prefer one file per job over a large file with a section per report: a shared file makes every change a change to every job, and the blast radius of a typo grows with the number of readers. Where several jobs genuinely share settings — an SMTP host, a shared export directory — put those in a small common file and merge it underneath the per-job one, using the same _deep_merge in one more layer.

Conclusion

Settings that change without the logic changing do not belong in the code. Put them in a TOML file the report's owner can edit, layer environment variables above it for secrets and per-machine paths, and let command-line flags win for the values that differ run to run. Validate the whole thing at startup and report every problem at once, commit an example rather than the real file, and the report becomes something that can be maintained by whoever owns it rather than only by whoever wrote it.

Frequently asked questions

TOML, JSON or YAML for the config file? TOML, if you are on Python 3.11 or newer — tomllib is in the standard library and the format tolerates comments and trailing commas. JSON is the fallback with no dependency on older versions; YAML needs PyYAML and its whitespace rules trip up the non-programmers who most often edit these files.

Where should the SMTP password live? In an environment variable or a secret store, never in the file. A config file gets committed, copied into a ticket and attached to an email; a variable set by the scheduler does not.

How do I keep separate settings for test and production? One file per environment — config.dev.toml, config.prod.toml — selected by a --config flag or an environment variable. Avoid a single file with an "environment" switch inside it; the wrong branch is too easy to hit.

Should the config file live next to the script or in the user's home directory? Next to the script for a scheduled job, so the settings travel with the deployment. Use a home-directory path only for a tool people install and personalise.

Up to the parent guide:

Related guides: