Converting Excel to YAML Config Files Without Surprises

Configuration, feature flags, translation tables, lookup lists, fixtures: more of these live in spreadsheets than anyone admits, because non-programmers can edit a spreadsheet. The application wants YAML. A conversion script lets the people who know the values keep editing in Excel while the repository holds the YAML. The format looks forgiving and has more traps than JSON, especially around implicit types. This post shows how to shape the data, dump it safely, avoid the well-known YAML type pitfalls and check the result. It is a spoke of converting Excel to CSV, JSON, XML and YAML.

Two shapes of sheet

A table of records. Each row is an item with the same fields: users, products, routes, translations.

As YAML, a list of mappings:

- key: greeting
  en: Hello
  fr: Bonjour
- key: farewell
  en: Goodbye
  fr: Au revoir

or, when one column is a unique key, a mapping from that key:

greeting:
  en: Hello
  fr: Bonjour
farewell:
  en: Goodbye
  fr: Au revoir

A sheet of settings. Two or three columns: name, value and maybe a note. Dots in names express nesting.

server:
  port: 8080
  host: localhost
features:
  beta: false
mail:
  retries: 3

Decide which shape the application's loader expects before coding. The conversion is easy once the target is clear.

Read the sheet

Read everything as text first, so you control every conversion:

import pandas as pd

df = pd.read_excel("settings.xlsx", sheet_name="Settings", dtype=str, keep_default_na=False)
df = df.apply(lambda col: col.str.strip())

dtype=str keeps 0123 as text. keep_default_na=False stops NA and null from being converted into missing values, which matters for settings where NA could be a country code (Namibia) or a literal. Strip whitespace; a trailing space in a cell is a common source of values that look identical and are not.

Convert values on purpose

A settings sheet holds strings; the application expects booleans, integers and strings. Convert with a typed rule per setting, not with a guess from the text. A small table of expected types is the clearest way:

TYPES = {"server.port": int, "mail.retries": int, "features.beta": "bool"}

def convert(name, raw):
    t = TYPES.get(name, str)
    if t == "bool":
        v = raw.strip().lower()
        if v in ("yes", "true", "1", "on"):  return True
        if v in ("no", "false", "0", "off"): return False
        raise ValueError(f"{name}: not a boolean: {raw!r}")
    if t is int:
        return int(raw)
    return raw

An unrecognized boolean raises an error instead of silently becoming a string. Any setting not listed stays a string. Missing values (an empty cell) deserve a decision: omit the key, or write null.

Expand dotted names into nested mappings, and detect conflicts:

def put(tree, dotted, value):
    parts = dotted.split(".")
    node = tree
    for p in parts[:-1]:
        node = node.setdefault(p, {})
        if not isinstance(node, dict):
            raise ValueError(f"{dotted}: {p} is both a value and a parent")
    if parts[-1] in node:
        raise ValueError(f"{dotted}: defined twice")
    node[parts[-1]] = value

config = {}
for _, r in df.iterrows():
    put(config, r["setting"], convert(r["setting"], r["value"]))

If one row sets server to a value and another sets server.port, there is a conflict that the helper reports. Duplicate rows are errors too.

Dump safely

Use safe_dump. The plain yaml.dump can serialize arbitrary Python objects with tags that only make sense to Python, and loading such files is dangerous. safe_dump writes only standard types.

import yaml

with open("config.yaml", "w", encoding="utf-8") as f:
    yaml.safe_dump(config, f, sort_keys=False, allow_unicode=True, default_flow_style=False)

sort_keys=False keeps the order of the sheet; the default sorts alphabetically. allow_unicode=True writes accented characters instead of escapes. default_flow_style=False forces block style on older versions. If you need comments, anchors or exact round-tripping of an existing file, use ruamel.yaml, which preserves them and supports YAML 1.2.

The type traps

YAML decides types from the look of plain scalars, and the rules differ by version. The well-known pitfalls:

PyYAML implements YAML 1.1. When it dumps a Python string that would be read back as another type, it quotes it: yaml.safe_dump({"code": "NO"}) writes code: 'NO'. That works only if the value is a Python str. If you pass the word through the conversion above and get the Python boolean False, it is written as false, which is what you asked for. The trap is the other direction: a value that must stay a string but was converted, such as a version 1.10 that went through float. Keep versions, codes and identifiers as strings.

YAML 1.2 (which ruamel.yaml defaults to) removed most of the surprises: yes and no are plain strings and only true and false are booleans. The consumer may still load with a 1.1 loader, so quote to be safe, whatever the dumper's version. The reliable test is to load the output with the loader the application uses (the Norway problem and other YAML type surprises explains the history).

Strings with special characters are quoted automatically: those starting with @, *, &, !, %, containing : or #, or beginning with whitespace. Multi-line strings are written in quoted form unless you use a custom representer for literal blocks.

Lists and tables

For a table of records:

records = df.to_dict(orient="records")
yaml.safe_dump(records, open("items.yaml", "w", encoding="utf-8"), sort_keys=False, allow_unicode=True)

For a mapping keyed by a unique column:

by_key = {r["key"]: {k: v for k, v in r.items() if k != "key"} for r in records}
assert len(by_key) == len(records), "duplicate keys"

Check key uniqueness, which a mapping silently enforces by overwriting. Convert numeric columns to numbers on purpose, and blanks to None (written as null) or omit them. Multi-valued cells, such as red, blue, become YAML lists after splitting on the delimiter and trimming.

Secrets and sensitivity

Spreadsheets get shared in ways repositories should not be. Do not put passwords, tokens or keys in a sheet that becomes a YAML file in version control. If a setting is secret, the sheet should hold a reference (an environment variable name or a secret manager path) and not the value. Review the generated file before committing, and keep the source workbook out of the repository or access-controlled.

Validate

Do three checks before using the file.

1. Load it back with the loader the application uses and compare with the intended structure: yaml.safe_load(open("config.yaml")) should equal config.

2. Check types: server.port is an int, features.beta a bool, codes are strings.

3. Validate against a schema. JSON Schema works for YAML data loaded into Python (jsonschema.validate(config, schema)), and tools such as yamllint check style and duplicate keys (validating YAML with schemas).

Count the keys or records and compare with the sheet's rows. Diff the new file against the previous version in version control; a conversion run should change only the lines that someone edited in the sheet, which is also a good test that output is stable (stable ordering, same quoting).

When YAML is the wrong place for the data

The data is large. YAML is slow to parse and awkward for thousands of rows. Use CSV, JSON Lines or Parquet.

Non-programmers must edit the YAML itself. Indentation errors are easy to make; keep the sheet as the source and generate.

The consumer wants JSON. YAML is a superset of JSON in practice; write JSON if that is what is read (converting Excel to JSON with Python for clean output).

The values are secrets. Keep them out of the file.

See the PyYAML documentation, the YAML 1.2 specification and the ruamel.yaml documentation. To format a snippet, use the YAML formatter.