Converting YAML to and From JSON CSV XML and Excel

Every team that keeps configuration or reference data in YAML eventually has to hand it to something that does not read YAML. An analyst wants a spreadsheet. A service wants JSON. A partner wants XML. A document needs a table. And sometimes the traffic goes the other way: a spreadsheet of settings that must become a file in a repository, or a JSON response that someone wants to read by eye. Each conversion looks like a loop over a tree, and each one changes something, usually without saying so. This guide covers what changes, per target and per direction, from tests we ran, and sets out a method that keeps conversions boring. If you need the language itself first, read YAML syntax you need to stop guessing.

What YAML can say that the others cannot

A conversion is a mapping between two data models, and YAML's is the largest of the five formats in this guide. The model is a tree of mappings, sequences and scalars, with several extra layers on top.

Read the table by column. Going from YAML to JSON loses comments, anchors and multiple documents. Going to CSV loses everything except a table of strings. Going to XML adds attributes you must invent and removes types. Going to Excel keeps cell types and loses the tree. The pairs also differ by direction: JSON to YAML adds nothing that was not there, and CSV to YAML gives you types you must supply.

Two principles follow, and the rest of this guide applies them.

1. One format is the source of truth, and the others are generated. If you edit both, they diverge.

2. Convert with a library that knows both formats, and finish with the check the consumer will run. String templates and regular expressions fail on the first awkward value.

The round-trip matrix

To see the losses rather than assert them, we built one document with eight keys chosen to be awkward and converted it through each format and back, comparing the type and value of every key with the original:

src = {"name": "orders", "version": "3.10", "released": datetime.date(2026, 10, 27),
       "enabled": True, "limit": None, "tags": ["a", "b"], "one": ["x"], "note": "line1\nline2"}

For CSV and Excel the lists were joined with a semicolon first, since a cell holds one value. The results:

key       json     xml      csv      xlsx    
name      ok       ok       ok       ok      
version   ok       ok       3.1      ok      
released  '2026-10-27' '2026-10-27' '2026-10-27' datetime.datetime(2026, 10, 27, 0, 0)
enabled   ok       'true'   ok       ok      
limit     ok       ok       ok       ok      
tags      ok       ok       ok       ok      
one       ok       'x'      ok       ok      
note      ok       ok       ok       ok      

Each non-ok entry shows what the key became. Counting the changed rows: JSON changed one value, the date, which became the string '2026-10-27' because JSON has no date type and we converted with default=str. XML changed three: the date became text, the boolean True became the string 'true', and the one-item list ['x'] became the scalar 'x'. CSV changed two: the date became text, and the version 3.10 came back as the float 3.1 when pandas inferred the column type on re-read. Excel changed one: the date came back as a datetime at midnight rather than a date.

Four lessons come out of eight keys.

Dates are the most fragile type. Every format handled them differently, and none returned a date. If a date matters, decide its text form (YYYY-MM-DD), write that form on purpose and parse it on the way back.

A list of one is not a scalar, but XML cannot tell. An XML element repeated twice is a list, and repeated once is an element. Readers that care wrap the list, or use a schema that says "this repeats".

A version number is a string that looks like a number. CSV lost it only on the re-read, and only because the reader guessed. Reading with dtype=str for that column fixes it. Text in a spreadsheet cell or a quoted YAML scalar has the same effect from the other side.

Multi-line text survived everywhere in this test: JSON escapes the newline, XML keeps it in the text node, CSV quotes the field and Excel stores it in the cell. The risk there is a consumer that splits on newlines.

The parser decides before the conversion starts

A conversion cannot be more accurate than its load step, and the load step varies by tool. We wrote six ambiguous values into one block and loaded it with four implementations.

meta:
  version: 3.10
  flag: no
  octal: 010
  empty:
  sci: 1e3
  port: 22:22

PyYAML, which dbt also uses, returned {'version': 3.1, 'flag': False, 'octal': 8, 'empty': None, 'sci': '1e3', 'port': 1342}. ruamel.yaml returned {'version': 3.1, 'flag': 'no', 'octal': 10, 'empty': None, 'sci': 1000.0, 'port': '22:22'}. yq printed the document as JSON with "flag":"no", "octal":10, "sci":1e3 and "port":"22:22", keeping the numbers' original spelling. Docker Compose, reading the same keys as environment values, produced FLAG: 'no', OCTAL: "8", VERSION: "3.1" and SCI: "1000".

The consequence is that "convert this YAML to JSON" has no single answer. It means "convert what this parser makes of it". If a downstream consumer will read the same YAML with a different parser, convert with that parser, or quote the ambiguous values until both agree. The version-rules comparison is in YAML 1.1 versus 1.2, and the most common surprise is the Norway problem. Quoting values that are identifiers, whatever the target, removes most of the divergence.

YAML and JSON

JSON is the easy neighbor: every JSON document is nearly a YAML document, and the reverse holds for the subset of YAML that uses only JSON's types. Converting YAML to JSON in Python covers the traps in the YAML-to-JSON direction, and they all come from YAML features JSON lacks:

• A date loads as a date object that json.dumps rejects with TypeError: Object of type date is not JSON serializable. Pass a default function that returns isoformat() for dates and sorted lists for sets, and raise on anything unknown.

• An integer key 1 comes out as the string "1", so a round trip does not return the same keys. A complex key written with ? raised found unhashable key and has no JSON form.

• .nan is written by json.dumps as NaN, which is invalid JSON; allow_nan=False turns that into a ValueError at write time.

• Anchors and merge keys expand: use: *b produced three independent objects in the output. Treat the JSON as a build artifact.

• Several documents in one file need safe_load_all and a choice of output shape, usually an array.

The opposite direction, converting JSON to YAML without losing types, is where string handling matters. Dumping a dictionary with PyYAML quoted 'yes', '3.10', '02134', 'null', '2024-01-01' and '~' and round-tripped equal. Writing the same data as key: value lines changed seven of ten values: the key yes became True, the version became 3.1, the zip code became the integer 1116 and the string "null" became None. Never generate YAML with an f-string. For the command line, yq -p=json -o=yaml converts, with one caution: it left yes: yes unquoted, which is correct YAML 1.2 and a boolean to a 1.1 reader, so choose the converter by the consumer. The related tool-level view is in converting YAML to JSON with yq, and the YAML to JSON converter in the browser does the same job without installing anything.

YAML and CSV

CSV is a table of text, so the real question is how a tree becomes a table. Converting YAML to CSV for review in a spreadsheet uses pandas.json_normalize to flatten nested mappings into dotted columns. Four details decide whether the CSV is usable.

• Lists in cells. Left alone, a list lands as the Python repr ['web', 'public']. Join with a separator that is not a comma, such as a semicolon.

• Integers turned into floats. One missing value made pandas store limits.memory as floats, and 512 was written 512.0. The nullable Int64 type fixes it.

• Encoding. Excel reads a CSV as the legacy code page unless the file starts with the byte order mark EF BB BF, which encoding="utf-8-sig" writes.

• Records with different shapes. A key that exists in some records gives empty cells in the others. That is information; do not fill it.

The other direction, converting a CSV into a YAML list, has the opposite problem: no types come in. csv.DictReader returns strings, and yaml.safe_dump faithfully wrote id: '1' and active: 'yes'. A conversion map from column to function gave id: 1, active: true, ratio: 0.5 and note: null, and the zip code stayed '02134' because the dumper quotes a string that would otherwise load as a number. Types should come from a schema you maintain, not from sampling rows. For configuration seeds in particular, see converting CSV to YAML for configuration and seed data.

YAML and XML

XML adds a concept YAML lacks, the attribute, and removes types. Converting YAML to XML with Python and valid names uses xmltodict.unparse with the convention that a key starting with @ becomes an attribute. Under that convention a list becomes repeated elements, a null and an empty string both become an empty element, and Python's True was written as true. The surprises are about names and roots:

• A key named 1st was written as an element and the output failed to parse in lxml with invalid element name. The writer did not validate. A key with a space raised ValueError, and a top-level mapping with two keys raised Document must have exactly one root.

• Mixed content, text with inline elements, has no YAML form at all, so do not start from YAML for document-like XML.

Finish every YAML-to-XML job by parsing the result with a real parser, and with an XSD if one exists. That is the consumer's first move, so make it yours. The XML cluster covers the other side, including converting XML to JSON and the attribute problem.

YAML and Excel

Excel is the one target that keeps cell types, which makes it the best choice for people and the hardest to automate. Converting an Excel sheet of settings into YAML reads cells with openpyxl and builds nested mappings from dotted keys such as server.port. Cell types came through: strings stayed strings, 5432 an integer, TRUE a boolean. The problems are Excel's, not YAML's:

• A date is a datetime, written as 2026-10-27 00:00:00, unless you convert it.

• A cell holding the number 3.1 formatted as 0.00 shows 3.10 and reads back as 3.1, while a text cell 3.10 stays a string. The two look identical on screen, so check the cell's data type rather than the display.

• A blank cell is None and becomes null, which is not an empty string.

• A formula cell returns the formula text, or with data_only=True the last calculated value, which is None if the workbook was generated by a script and never opened in Excel.

The reverse direction, YAML to a workbook, is a pandas to_excel call after flattening, and it preserves types the way the matrix showed. Excel also reinterprets values when a person opens a CSV: leading zeros vanish and gene names become dates (Excel drops leading zeros when opening a CSV). Write an .xlsx for people when the data has codes.

YAML to Markdown and other documents

Documentation is the last destination. Converting YAML to a Markdown table takes about fifteen lines of Python and one escaping rule: a pipe inside a cell must be written \|, or the row splits. Lists are joined with a comma and space, nulls become empty cells, and the table is regenerated in CI so it cannot go stale. Keep the generated region marked with comments in the README, and keep tables narrow; wide or nested data reads better as headed sections. The same pattern applies to HTML and reStructuredText.

Comments, layout and the edit problem

All of the conversions above are one-way with respect to layout. The more common need is a different one: change a value in a YAML file and leave the rest alone. Round tripping YAML without losing comments compared three tools on a twelve-line file. PyYAML removed both comments, replaced the anchor and merge key with copied values, and stripped the quotes from 'single'. ruamel.yaml with preserve_quotes = True wrote the file back identical except for the changed value. yq kept comments, the anchor and the quote, but moved the list items two spaces right and changed three spaces before a comment to one. The rule: use ruamel.yaml for edits in code, yq for edits in a shell where whitespace churn is acceptable, and a text patch followed by a data comparison when the diff must be a single line. Test any tool with a no-op round trip first and read the diff.

One script, four targets, verified

The pieces above fit in one short script that converts the same list of services to JSON, XML, CSV and Excel, and then reads each output back and compares it with the source. The checks are the point: a converter that cannot prove its output is a converter that has not been run.

import csv
import json
import sys

import openpyxl
import pandas as pd
import xmltodict
import yaml

src = yaml.safe_load(open("services.yaml"))

# JSON
json.dump(src, open("out.json", "w"), indent=2)
assert json.load(open("out.json")) == src

# XML: wrap lists so one-item lists survive
doc = {"services": {"service": [
    {"name": s["name"], "port": s["port"], "tags": {"tag": s["tags"]},
     "limits": s["limits"]} for s in src]}}
xml = xmltodict.unparse(doc, pretty=True)
open("out.xml", "w").write(xml)
back = xmltodict.parse(xml, force_list=("service", "tag"))["services"]["service"]
assert [b["name"] for b in back] == [s["name"] for s in src]
assert [b["tags"]["tag"] for b in back] == [s["tags"] for s in src]
assert [int(b["port"]) for b in back] == [s["port"] for s in src]

# CSV and XLSX
df = pd.json_normalize(src, sep=".")
df["tags"] = df["tags"].map(";".join)
df["limits.memory"] = df["limits.memory"].astype("Int64")
df.to_csv("out.csv", index=False, encoding="utf-8-sig")
df.to_excel("out.xlsx", index=False)
rows = list(csv.DictReader(open("out.csv", encoding="utf-8-sig")))
assert [r["name"] for r in rows] == [s["name"] for s in src]
assert [r["tags"].split(";") for r in rows] == [s["tags"] for s in src]
assert rows[1]["limits.memory"] == ""
ws = openpyxl.load_workbook("out.xlsx").active
cells = [[c.value for c in r] for r in ws.iter_rows(min_row=2)]
assert [c[1] for c in cells] == [s["port"] for s in src]
print("all four targets verified:", ", ".join(["json", "xml", "csv", "xlsx"]))
print(open("out.xml").read())

It printed all four targets verified: json, xml, csv, xlsx and the XML below, with each service wrapped in a <service> element and each tag in its own <tag>:

<services>
	<service>
		<name>api</name>
		<port>8080</port>
		<tags>
			<tag>web</tag>
			<tag>public</tag>
		</tags>
		<limits>
			<cpu>2</cpu>
			<memory>512</memory>
		</limits>
	</service>
	...
</services>

Several decisions in that script are the ones to copy. The XML wraps lists (<tags><tag>) so that a one-item list reads back as a list, and the read-back passes force_list for the same reason. The CSV joins tags with a semicolon and the check splits them again. The memory column is converted to a nullable integer so 512 stays 512 and the missing value stays empty, and the assertion rows[1]["limits.memory"] == "" proves the second service's blank survived. The workbook check reads the port column and expects integers, because Excel keeps the cell type. Each assertion is cheap and each one corresponds to a way a conversion fails in practice.

A date policy, written once

Dates caused a change in every target in the matrix, so give them a policy before you convert anything. The one that holds up is: **dates are text in ISO 8601 form, YYYY-MM-DD for dates and YYYY-MM-DDTHH:MM:SSZ for timestamps in UTC, quoted in the YAML.** Quoting stops the YAML parser from turning them into date objects, so every format carries the same string, and a consumer parses it when it needs a date. The cost is that the YAML no longer has a native timestamp, and a schema should say the string has a date format. If you prefer native dates, handle each target explicitly: isoformat() for JSON and XML, a date format string for CSV, and cell.number_format for Excel. Do not leave it to defaults, since the defaults differ: JSON refuses, XML writes whatever str gives, CSV writes str and Excel writes a serial number with a format.

Testing a converter

A converter is a function from one tree to another, so it can be tested like one. Three tests pay for themselves.

A golden file. Keep a small input with every awkward value in it, the eight keys of the matrix above, and the expected output next to it. A failing test shows a diff. Add a key whenever a bug is found.

A round trip. Convert and convert back, then compare the two data structures after normalizing the known differences (dates to strings, lists of one to lists). When the test fails, the diff names the key that changed, which is faster than reading output files.

A hostile input. A key with a space, a value with a pipe, a string no, an emoji, a very long line and an empty file. The converter should either handle each or fail loudly. Silent acceptance is the failure.

Run these in CI along with the regeneration check. A conversion script that nobody tests will meet its first hostile input in production.

Command-line conversions with yq

yq converts between formats without a script, which is convenient for pipelines. From a small file a: 1, b: [x, y], c: {d: 2}, yq -o=json -I=0 printed {"a":1,"b":["x","y"],"c":{"d":2}}, yq -o=xml printed <a>1</a>, two <b> elements and a <c> with a <d>, and yq -o=props printed a = 1, b.0 = x, b.1 = y, c.d = 2. For a list of flat mappings, yq -o=csv printed a header row and one line per record. Two cautions. yq -P file.json kept the JSON layout, because the output format follows the input unless you name it, so use -p=json -o=yaml. And yq follows YAML 1.2 rules for scalars, so what it quotes differs from PyYAML. Use it for edits and quick conversions, and a library when types matter.

Large inputs

A YAML file of hundreds of megabytes is unusual, and a CSV or JSON Lines file of that size is not. Converting YAML to those formats means loading the whole tree, since PyYAML builds it in memory. For large data, store it in a format that streams (CSV, JSON Lines, Parquet) and keep YAML for configuration. When you must convert a big multi-document YAML, yaml.safe_load_all yields one document at a time, so write each as it arrives instead of collecting a list. Measurements for parse speed are in large YAML files and parsing speed.

Security notes for converters

A converter reads input you may not control. Four rules cover most of the risk.

• Load YAML with safe_load, never the unsafe loader. The unsafe one can construct Python objects from tags, which is how loading untrusted YAML runs code.

• Treat aliases as a resource limit. A small document with nested aliases can expand to something enormous; see Billion Laughs style attacks through YAML aliases.

• When writing CSV or Excel for people, remember that a cell starting with =, +, - or @ can be executed as a formula by a spreadsheet. OWASP calls this CSV injection; prefix such values with a single quote or tab if the data is untrusted.

• Do not put secrets in a converted file because it is "just a report". Reports travel.

Five situations and the recipe for each

Most conversions fall into a handful of situations, and each has a default answer.

A service reads JSON, and your source is YAML. Generate the JSON in the build from the YAML, with safe_load_all if there are several documents, default for dates and allow_nan=False. Commit neither the JSON nor a hand edit of it; ship it as a build output. The service gets one meaning for every value, and the YAML keeps its comments and anchors.

An analyst wants the data in a spreadsheet. Flatten with dotted column names, join lists with a semicolon, write utf-8-sig for a CSV or use to_excel if the data has codes. Tell them the file is a view, and take their corrections as a list of changes you apply to the YAML. Do not let the spreadsheet become a second source.

Operations keeps settings in a workbook, and the application wants YAML. Add a type column to the sheet, read cells with openpyxl, check each cell's data type against it and fail on a mismatch, then validate the YAML against a schema. Version numbers and identifiers belong in text-formatted cells. Run the conversion in CI so a bad cell fails a build instead of a deploy.

A partner needs XML. Agree on an XSD first, because the attribute and wrapper conventions come from it. Convert with a library, wrap lists, and validate the output against the XSD before sending. If the schema is document-like with mixed content, build the XML with an XML tool and not from YAML.

A README needs a table of what is in the config. Generate it from the YAML between marker comments, escape pipes, and have CI fail when the committed table differs from a fresh one. The table is the only place where nobody will notice drift until a user is confused.

In every one of them the pattern is the same: one source, a script, a verification step and a check in CI. The formats change, and the discipline does not.

A method that keeps conversions boring

1. Name the source of truth. Write it in the file header of every generated file: "Generated from services.yaml. Do not edit."

2. Declare types in one place. A JSON Schema, a Pydantic model or a column map. Convert by schema, not by sniffing.

3. Use the parser the consumer uses. Or quote the ambiguous values.

4. Convert with libraries. No f-strings for YAML, XML or CSV.

5. Verify with the consumer's parser. Parse the XML, load the JSON, open the workbook, and compare the loaded data with the source.

6. Regenerate in CI and compare. Fail the build when the committed derivative differs from a fresh one.

7. Diff before you commit. Reviewing YAML config changes in a pull request shows a script that prints changed paths, which also works for checking a regenerated file.

Step six is the one that matters most over time. Derived files rot. A table in a README, a JSON copy for a service, a CSV someone attached to a ticket: each is correct on the day it is made. Generating them in the build and failing on drift moves the problem from the reader to the author.

Choosing a target format

Many conversions are avoidable. If both ends can read YAML, pass the YAML. If a service needs JSON, ask whether the service can accept YAML, since many do.

When a conversion is the wrong answer

The data has no stable shape. If every record has different keys and depth, a flat target will be mostly empty. Keep it in YAML or JSON.

The target will be edited by hand. A generated spreadsheet that a manager corrects, then needs converting back, is a second source of truth. Decide who owns the data, and have the other side read it.

You need comments and anchors to survive. Only YAML holds them. Generate the other formats and keep the YAML as the file people edit.

The reader can read YAML directly. A CSV exported for someone who opens files in a text editor adds a step and loses information.

The volume is large. Move large datasets out of YAML into a format built for tables.

For reference: the YAML 1.2.2 specification defines the data model, the JSON specification, RFC 8259, defines the target, RFC 4180 the CSV format, and the W3C XML specification the XML rules. The OWASP page on CSV injection covers the formula risk. For quick checks in the browser, use the YAML formatter and the CSV and JSON converter.