Convert Excel Sheets to YAML or TOML Config

Many automation projects keep their configuration in a workbook because that is where the people who own it are comfortable: thresholds, report recipients, region-to-manager mappings, product categories. The programs that use it, though, are happier with a text config file — YAML for a deployment or a scheduler, TOML for a Python tool's pyproject-style settings — which can be versioned, diffed and reviewed in a pull request. A small converter bridges the two: the business edits the workbook, the converter writes the config file, and the job reads the config file. Doing that well means getting the types right — Excel stores every number as a float and every date as a datetime — handling empty cells, which TOML cannot express, and checking that the generated file reads back to exactly the data in the workbook. This page builds that converter for both formats. It belongs to Handling Excel File Formats and Conversions.

Prerequisites

pip install openpyxl pyyaml tomli-w

tomllib, used to read TOML back, is in the standard library from Python 3.11; tomli-w writes TOML. The workbook has a Settings sheet of dotted keys and values with a notes column, and a Recipients table — the two shapes most configuration takes.

Step 1 — Read the sheets with the right types

import datetime as dt
from openpyxl import load_workbook

TRUE_WORDS, FALSE_WORDS = {"yes", "true", "y", "1"}, {"no", "false", "n", "0"}

def clean(value):
    if isinstance(value, float) and value.is_integer():
        return int(value)                                     # 50000.0 -> 50000
    if isinstance(value, dt.datetime):
        return value.date() if value.time() == dt.time() else value
    if isinstance(value, str):
        text = value.strip()
        if text.lower() in TRUE_WORDS:
            return True
        if text.lower() in FALSE_WORDS:
            return False
        return text
    return value

def nest(pairs):
    root = {}
    for key, value in pairs:
        *parents, leaf = key.split(".")
        node = root
        for p in parents:
            node = node.setdefault(p, {})
        node[leaf] = value
    return root

wb = load_workbook("config.xlsx", data_only=True)
settings = nest((k, clean(v)) for k, v, *_ in wb["Settings"].iter_rows(min_row=2, values_only=True) if k)
rows = list(wb["Recipients"].iter_rows(values_only=True))
recipients = [{h.lower(): clean(v) for h, v in zip(rows[0], r)} for r in rows[1:]]
config = {**settings, "recipients": recipients}
print(config["report"], "|", config["smtp"])

Three type problems are fixed on the way in. Whole numbers come out of Excel as floats in some workflows — a value typed into a cell formatted as currency, or a column that once held decimals — so 50000.0 is turned back into 50000. Dates come out as datetimes at midnight, so a pure date is reduced to a date. And "Yes", "No", "TRUE" typed as text become real booleans; a real Excel boolean, like the Active column, already arrives as True or False. The dotted keys — report.title, smtp.port — are turned into nested sections, which is how both YAML and TOML structure configuration. data_only=True reads formula results, so a setting computed by a formula in the workbook converts as its value.

Step 2 — Write YAML

import yaml

with open("config.yaml", "w", encoding="utf-8") as f:
    f.write("# Generated from config.xlsx — edit the workbook, not this file\n")
    yaml.safe_dump(config, f, sort_keys=False, allow_unicode=True, default_flow_style=False)
from pathlib import Path
print(Path("config.yaml").read_text(encoding="utf-8"))

safe_dump writes only standard YAML types — no Python-specific tags — so any YAML reader can load the file. sort_keys=False keeps the order of the workbook, which makes the file easier to compare with it; allow_unicode=True writes names such as "Café Señor" as they are rather than as escape sequences. Dates are written as plain ISO dates, which YAML loaders read back as dates, and the empty reply_to becomes null. The comment at the top tells anyone who opens the file where to make changes. Two YAML details are worth knowing when the values come from a spreadsheet. Unquoted words such as yes, no, on and off are read as booleans by older YAML 1.1 loaders, and codes such as 0123 can be read as numbers; safe_dump quotes strings that would be misread, — 'yes', '0123' — which is one more reason to generate the file rather than write it by hand. And the order of keys in the output follows the workbook only because sort_keys=False is set — without it, keys are sorted alphabetically and the file no longer mirrors the sheet.

Step 3 — Write TOML, which has no null

TOML is stricter: it has no null value at all, so an empty cell cannot be written as "nothing" — tomli_w raises TypeError: Object of type 'NoneType' is not TOML serializable. Drop missing values before writing, and make sure the reading program treats a missing key as "not set":

import tomli_w

def drop_none(obj):
    if isinstance(obj, dict):
        return {k: drop_none(v) for k, v in obj.items() if v is not None}
    if isinstance(obj, list):
        return [drop_none(v) for v in obj]
    return obj

try:
    tomli_w.dumps(config)
except TypeError as e:
    print("with None:", e)
toml_text = "# Generated from config.xlsx — edit the workbook, not this file\n" + tomli_w.dumps(drop_none(config))
Path("config.toml").write_text(toml_text, encoding="utf-8")
print(toml_text)

Nested dictionaries become [report], [thresholds] and [smtp] tables, and the list of recipient dictionaries is written as an array of inline tables, one line per person. It comes first in the file because TOML requires plain keys to appear before any [section] header; that is a rule of the format, and the reading program sees the same structure either way. Chloe's empty region and the empty reply_to are simply absent. TOML dates are a native type too, so cutoff = 2026-10-02 is written without quotes and read back as a date.

Step 4 — Prove the round trip

A generated config file is only trustworthy if reading it back gives exactly the workbook's data:

import tomllib

from_yaml = yaml.safe_load(Path("config.yaml").read_text(encoding="utf-8"))
from_toml = tomllib.loads(Path("config.toml").read_text(encoding="utf-8"))
print("YAML round trip equal:", from_yaml == config)
print("TOML round trip equal:", from_toml == drop_none(config))
print("types:", type(from_toml["report"]["cutoff"]).__name__, type(from_toml["smtp"]["port"]).__name__, type(from_toml["report"]["send"]).__name__)

Both files load back to the same structure and types that were written: the cut-off date is a date, the port an int and the send flag a bool — not strings, which is what a hand-maintained copy often ends up with. Run this check every time the converter runs, and fail the conversion if it does not hold. Loading such a config into a report script, with defaults and validation, is covered in Keep Excel Report Settings in a Config File.

Where the converter fits

Run the converter as part of the change process rather than by hand. A common arrangement is a small script in the repository that regenerates the config file from the workbook and fails if the round trip does not match; the person who changes the workbook runs it, and the resulting diff — "margin_alert: 0.15 → 0.12" — is what gets reviewed and committed. That gives the business their spreadsheet, the engineers a reviewable text change, and everyone a history of who changed which setting and when. Validating the values as well as the types — a port between 1 and 65535, a threshold between 0 and 1, every recipient with an email address — belongs in the same script, so a typo in the workbook stops at conversion instead of surfacing in a failed overnight job.

Common pitfalls

• Floats where integers belong. Convert whole-number floats such as 587.0 back to int.
• "Yes" and "No" left as text. Map them to booleans explicitly.
• None in TOML. TOML has no null; omit missing keys.
• yaml.dump instead of safe_dump. It can write Python-specific tags other tools cannot read.
• Editing the generated file. Say in a header comment that the workbook is the source, and regenerate rather than patch.

Conclusion

To turn a configuration workbook into YAML or TOML, read the sheets with openpyxl, fix Excel's types on the way — whole-number floats to integers, midnight datetimes to dates, yes/no text to booleans — and build nested sections from dotted keys and lists of dictionaries from tables. Write YAML with yaml.safe_dump, write TOML with tomli_w after dropping empty values, and load both back to prove they match the workbook before the file is used.

Frequently asked questions

How do I convert an Excel sheet to YAML in Python? Read the rows with openpyxl or pandas into dictionaries and lists, fix the value types, and write them with yaml.safe_dump with sort_keys=False.

How do I convert Excel to TOML? Build the same nested dictionary and write it with tomli_w.dumps, leaving out empty cells because TOML has no null value.

Why does tomli_w fail with None? TOML cannot represent null; remove keys whose value is None before writing, and treat missing keys as unset when reading.

Why are my integers written as 587.0? Excel stores numbers as floats; convert floats with no fractional part to int before writing the config.

Related

• Up one level: Handling Excel File Formats and Conversions — converting between formats.
• Convert an Excel Sheet to JSON with Python — the JSON equivalent.
• Keep Excel Report Settings in a Config File — using the config.
• Read Boolean Columns from Excel with pandas — more on yes/no values.