Converting an Excel Sheet of Settings Into YAML Using Python

Somebody on your team keeps application settings in a spreadsheet. It has columns for the key, the value and sometimes who owns it, and it gets emailed around because that is how the team works. Eventually a program needs those settings, and the program reads YAML. This post converts a settings sheet into YAML with openpyxl and PyYAML, and covers the places where Excel's idea of a value differs from what the cell shows. It belongs to the cluster on converting YAML to and from JSON, CSV, XML and Excel. For the CSV route to the same place, see converting a CSV into a YAML list.

The sheet

A settings sheet has a header row and one setting per row. The key column uses dots to show structure, the value column holds the value, and a type column documents the intent:

We built this sheet with openpyxl so each cell carries a type: the host is text, the port an integer, tls a boolean, version a text cell, the date a date cell and cpu a float. In a real workbook, those types are whatever Excel decided when someone typed the value, which is the first source of surprises.

Reading the cells

openpyxl reads a workbook and, with values_only=True, returns plain Python values:

import openpyxl
import yaml

wb = openpyxl.load_workbook("settings.xlsx")
out = {}
for key, value, typ in list(wb.active.iter_rows(values_only=True))[1:]:
    node = out
    *parents, leaf = key.split(".")
    for p in parents:
        node = node.setdefault(p, {})
    node[leaf] = value
print(yaml.safe_dump(out, sort_keys=False))

key.split(".") turns server.port into ["server", "port"]. The loop walks down the dictionary, creating each level with setdefault, and sets the leaf. The result:

server:
  host: db.internal
  port: 5432
  tls: true
  version: '3.10'
release:
  date: 2026-10-27 00:00:00
limits:
  cpu: 2.5
notes: null

The types arrived as stored. The column of Python types read from column B was ['str', 'int', 'bool', 'str', 'datetime', 'float', 'NoneType']. The version stayed a string and was quoted by the dumper, so the YAML will not turn it into 3.1. The date is the odd one out, and two cells are worth a closer look.

A date is a datetime

Excel has no pure date type: a date is a number with a date format, and openpyxl returns a datetime for it. The dumped value, 2026-10-27 00:00:00, is a YAML timestamp. A consumer that wanted a date gets a datetime at midnight, and one that wanted text gets a timestamp. Decide what the setting means and convert explicitly:

import datetime

if isinstance(value, datetime.datetime) and typ == "date":
    value = value.date()

PyYAML writes a date as 2026-10-27. If the YAML is read by a tool that treats unquoted dates as strings, quote them by converting to isoformat(). The sheet's type column is what makes this possible. Use it: dispatch on typ, and raise on a mismatch between the type column and the cell.

A number formatted to look like text

The version is the setting people get wrong in Excel. If someone types 3.10 into a General cell, Excel stores the number 3.1 and displays 3.1. If they format the cell as 0.00, it displays 3.10 and still stores 3.1. We tested the second case:

[(3.1, '0.00', 'n'), ('3.10', 'General', 's'), (5432, 'General', 'n'), (0.3, 'General', 'n')]

The tuple is the value, the number format and the cell's data type (n number, s string). The first cell holds 3.1 with the format 0.00; the second holds the string 3.10. A person looking at the sheet sees 3.10 in both, and a program sees a float in one and a string in the other. So a settings sheet for versions needs the cell formatted as Text before the value is typed, or a leading apostrophe, and the converter should check data_type rather than trust the display. For any column that holds identifiers, check cell.data_type == "s" and fail otherwise. The same family of problems is in the number that turns into something else, and the CSV side is in Excel dropping leading zeros.

The last value, 0.3, is 0.1 + 0.2 written into a cell. Excel stores up to 15 significant digits, so the usual floating-point tail disappears when the workbook round trips. That is a kindness for settings and a hazard for exact arithmetic.

Blanks and booleans

A blank cell comes back as None, and yaml.safe_dump writes null. That is usually right, since the setting has no value. If a blank should mean an empty string, convert it, because the two behave differently in code: a program that checks if value: treats both as false, and one that checks is None does not.

Booleans are better than most spreadsheets give credit for. A cell holding TRUE as a boolean comes back as True. A cell with the text TRUE, or yes, comes back as a string, and nothing converts it. Use the type column: for bool, accept a boolean cell, or map known strings and raise on others, as in converting a CSV into a YAML list.

Formulas

By default openpyxl returns a formula cell as its formula text, such as =B2*2. Passing data_only=True returns the value Excel last calculated and saved, which is None if the workbook was never opened in Excel (for example, one produced by a script):

wb = openpyxl.load_workbook("settings.xlsx", data_only=True)

Choose on purpose. For a settings sheet, formulas are a sign that someone derived a value from another, and the converted YAML will hold the result, not the relationship. Prefer to make derived settings explicit in the sheet's type column and compute them in code, so the YAML does not freeze a calculation.

Validating what you produced

A converter that runs unattended on a spreadsheet owned by someone else needs a check at the end. Three cheap ones:

1. Duplicate keys. Two rows with server.port silently overwrite each other in the loop above. Detect and fail.

2. Conflicting paths. A row server with a value and a row server.port cannot both exist. The loop would try to call setdefault on a string and raise AttributeError; catch it and print the key.

3. A schema. Validate the produced YAML against a JSON Schema or a Pydantic model, as in validating YAML configuration with Pydantic. It catches a port that arrived as a string, a missing required key and an unknown one.

Then diff the new YAML against the previous run's output, so a reviewer sees which settings changed this time. Reviewing YAML config changes in a pull request shows a script that prints changed paths.

Where the spreadsheet fits in the workflow

The conversion is the easy part. The harder question is who owns the truth. If the sheet is the source, regenerate the YAML on every release and never edit it by hand, with a header comment saying so. If the YAML is the source, the sheet is a report and the conversion runs the other way, as in converting YAML to CSV for review in a spreadsheet. Having both editable is how settings diverge.

When an Excel sheet should not be the source of settings

Settings change often or in an emergency. A file attached to an email is not a change process. A repository with review and history is.

Several people edit at once. Workbooks do not merge. Two edited copies mean someone redoes their work, and nobody sees the diff.

Values need types you cannot enforce. Excel formats are a display layer. Data validation can restrict a column, but a pasted value bypasses it.

Secrets are in the sheet. A password in a workbook is in every copy of the workbook. Move it to a secrets store.

The reading API is described in the openpyxl documentation, including data_only and cell types. Excel's number formats and how it stores dates are specified in ECMA-376. To preview the YAML you will get, use the YAML formatter.