Converting CSV to YAML for Config Seeds Without Surprises

YAML is the format people use for configuration, fixtures and seed data: a short list of reference rows that lives in version control next to the code. Converting a CSV into one looks easy. The catch is that YAML decides what a bare word means. An unquoted NO is a boolean in some versions of the language, an unquoted 02134 can be an octal number, and a time like 12:30 can turn into an integer. The conversion goes wrong exactly for the values that look like something else, and the output is a file that parses without an error. This article shows which values change, how to avoid it, and what happens at 193,708 records.

What a bare word turns into

The trap is easiest to see when the YAML is written by hand, or by a script that formats text. This test writes sixteen values without quoting, loads them with yaml.safe_load and compares each one with the string that was written:

print("\n1. Written by hand, with no quoting, then loaded back")
VALUES = ["NO", "yes", "on", "null", "~", "y", "02134", "1e5", "1.10", "2025-03-01", "12:30", "0x1F", "1_000", "010", "plain", "O'Neil, Pat"]
naive = "\n".join(f"v{i}: {v}" for i, v in enumerate(VALUES))
loaded = yaml.safe_load(naive)
for i, v in enumerate(VALUES):
    got = loaded[f"v{i}"]
    flag = "" if got == v and type(got) is str else "   <-- not the string that was written"
    print(f"  {v!r:14} loads as {type(got).__name__:9} {got!r}{flag}")

python lab/conv/t0148.py

PyYAML 6.0.3 with libyaml: True

1. Written by hand, with no quoting, then loaded back
  'NO'           loads as bool      False   <-- not the string that was written
  'yes'          loads as bool      True   <-- not the string that was written
  'on'           loads as bool      True   <-- not the string that was written
  'null'         loads as NoneType  None   <-- not the string that was written
  '~'            loads as NoneType  None   <-- not the string that was written
  'y'            loads as str       'y'
  '02134'        loads as int       1116   <-- not the string that was written
  '1e5'          loads as str       '1e5'
  '1.10'         loads as float     1.1   <-- not the string that was written
  '2025-03-01'   loads as date      datetime.date(2025, 3, 1)   <-- not the string that was written
  '12:30'        loads as int       750   <-- not the string that was written
  '0x1F'         loads as int       31   <-- not the string that was written
  '1_000'        loads as int       1000   <-- not the string that was written
  '010'          loads as int       8   <-- not the string that was written
  'plain'        loads as str       'plain'
  "O'Neil, Pat"  loads as str       "O'Neil, Pat"

2. The same values through yaml.safe_dump
v0: 'NO'
v1: 'yes'
v2: 'on'
v3: 'null'
v4: '~'
v5: y
v6: '02134'
v7: 1e5
v8: '1.10'
v9: '2025-03-01'
v10: '12:30'
v11: '0x1F'
v12: '1_000'
v13: '010'
v14: plain
v15: O'Neil, Pat

round trip equal: True

3. Two awkward records
- id: '4'
  name: Cy
  zip: '90210'
  score: ''
  active: 'true'
  joined: '2025-03-04'
  note: 'line one

    line two'
- id: '6'
  name: Ed
  zip: '60601'
  score: '100'
  active: ''
  joined: '2025-03-06'
  note: 'NO'

- id: '7'
  name: Zoë Ünal
  zip: '00000'
  score: '1234.50'
  active: 'true'
  joined: '2025-03-07'
  note: ünïcode ✓ ñ
- id: '9'
  name: Gus
  zip: '12345'
  score: '1.10'
  active: 'true'
  joined: '2025-03-09'
  note: <b>bold</b> a|b & "q"

Twelve of the sixteen came back as something other than the string that went in:

• NO loaded as False, yes and on as True: the country code for Norway becomes a boolean, the so-called Norway problem.

• null and ~ loaded as None. A name field that holds the word null is now missing, a problem the missing values article approaches from the CSV side.

• 02134 loaded as the integer 1116. A leading zero marks an octal number in YAML 1.1, and 02134 in octal is 1,116. A zip code was replaced with a different number. 010 became 8 for the same reason.

• 12:30 loaded as 750. YAML 1.1 reads colon-separated digits as a base 60 number (12 times 60 plus 30).

• 0x1F became 31 and 1_000 became 1000.

• 1.10 became the float 1.1, losing the zero that makes it a version number or a price.

• 2025-03-01 became a datetime.date.

Four were left alone: y, 1e5 (PyYAML needs a decimal point to read it as a float), plain and O'Neil, Pat. A CSV with identifiers, codes, versions and times in it will have a few values in each of those categories, and a converter that prints key: value is correct for all the others.

Let the library quote

yaml.safe_dump knows which strings would be read back as something else and quotes exactly those:

print("\n2. The same values through yaml.safe_dump")
dumped = yaml.safe_dump({f"v{i}": v for i, v in enumerate(VALUES)}, sort_keys=False)
print(dumped)
back = yaml.safe_load(dumped)
print("round trip equal:", all(back[f"v{i}"] == v for i, v in enumerate(VALUES)))

The second block of the output shows it: 'NO', 'yes', 'on', 'null', '~', '02134', '1.10', '2025-03-01', '12:30', '0x1F', '1_000' and '010' are all quoted, and y, 1e5, plain and O'Neil, Pat are bare. Loading the dump gave back all sixteen strings: round trip equal: True. The rule is the same whenever you generate YAML, JSON-like or otherwise: use a serializer, not a format string. A serializer applies the rules of the version it targets; a format string applies none.

Two options matter. sort_keys=False keeps the columns in the CSV's order, which is what a reader of the file expects. And allow_unicode=True keeps Zoë Ünal and ✓ readable; without it PyYAML writes escape sequences.

What the records look like

Four of the awkward records, dumped with those options (print once per pair):

print("\n3. Two awkward records")
print(yaml.safe_dump([rows[3], rows[5]], sort_keys=False, allow_unicode=True))
print(yaml.safe_dump([rows[6], rows[8]], sort_keys=False, allow_unicode=True))

Look at the third block of the output. Every value in these records is a string (the converter did not type anything), so the numbers are quoted: id: '4', zip: '90210', score: '100'. That is correct for a first pass and awkward to read; if the seed file is for people, convert the columns that are really numbers first, and keep the identifiers as strings. The empty score became score: ''. The note with a line break was written as a quoted multi-line string:

  note: 'line one

    line two'

In a YAML single-quoted string, a blank line stands for one newline, so this is line one\nline two. It loaded back correctly in the round trip, and it is ugly to review. For text with line breaks in a seed file, I would use a literal block (|), which PyYAML does not choose on its own. Ed's note is 'NO' in quotes, because the dumper knew; the line note: <b>bold</b> a|b & "q" needs no quoting in YAML.

The converter

import csv, sys, yaml
src, out, kind = sys.argv[1], sys.argv[2], sys.argv[3]
rows = list(csv.DictReader(open(src, newline="", encoding="utf-8")))
dumper = yaml.CSafeDumper if kind == "c" else yaml.SafeDumper
with open(out, "w", encoding="utf-8") as f:
    yaml.dump(rows, f, Dumper=dumper, sort_keys=False, allow_unicode=True)

It reads the CSV as text, dumps the list of records with the safe dumper, and takes the C dumper when asked. For seeds, two layouts are common. A list of mappings, as above, keeps the CSV's row order. A mapping keyed by a unique column ({ "02134": {...}, ...}) is better for lookups and for reviews, since a changed row shows up as a changed block under its key; build it with a dictionary comprehension and check for duplicate keys first, because a duplicate silently overwrites.

At 193,708 records

A seed file is a few dozen records. Out of curiosity and for a rule of thumb, I dumped the whole customer export:

/usr/bin/time -f "%e s, peak %M KB" python lab/conv/y_dump.py work/val/customers.csv work/conv/c_py.yaml py

53.10 s, peak 1395424 KB

/usr/bin/time -f "%e s, peak %M KB" python lab/conv/y_dump.py work/val/customers.csv work/conv/c_c.yaml c

15.12 s, peak 1395540 KB

The pure Python dumper took 53.10 s and 1,395,424 KB. CSafeDumper, which uses libyaml, took 15.12 s and the same memory. Loading is the slower direction:

/usr/bin/time -f "%e s, peak %M KB" python lab/conv/y_load.py work/conv/c_c.yaml py
/usr/bin/time -f "%e s, peak %M KB" python lab/conv/y_load.py work/conv/c_c.yaml c
ls -l work/conv/c_py.yaml work/conv/c_c.yaml | awk '{printf "%-12s %d bytes\n", $9, $5}' | sed 's#work/conv/##'
cmp work/conv/c_py.yaml work/conv/c_c.yaml && echo "the two dumpers wrote identical files"
head -n 14 work/conv/c_c.yaml

193708 records; first: {'id': '1', 'first_name': 'Cy', 'last_name': 'Riga', 'email': '[email protected]'}
106.82 s, peak 2902132 KB
193708 records; first: {'id': '1', 'first_name': 'Cy', 'last_name': 'Riga', 'email': '[email protected]'}
25.50 s, peak 2071720 KB
c_c.yaml 47835898 bytes
c_py.yaml 47835898 bytes
the two dumpers wrote identical files
- id: '1'
  first_name: Cy
  last_name: Riga
  email: [email protected]
  phone: '+14192381408'
  phone_unparsed: ''
  signup_date: '2025-04-03'
  balance: '10485.58'
  balance_currency: USD
  units: '4156'
  country: IT
  status: active
- id: '2'
  first_name: Bo

SafeLoader needed 106.82 s and 2,902,132 KB (about 2.8 GB) to read 193,708 records, and CSafeLoader 25.50 s and 2,071,720 KB. The two dumps were byte-identical (cmp found no difference), and the file was 47,835,898 bytes against 17,297,141 for the CSV. The lesson is the size of the tool: YAML is meant to be read and edited by people. For tens of thousands of rows or more, keep the data in CSV, JSON Lines or a database, and let YAML hold the configuration that points at it.

How to tell it worked

Load the file back with the loader your application will use and compare every value with the CSV text. Include the troublemakers on purpose: a Norwegian country code, a zip with a leading zero, a version like 1.10, a time such as 12:30, a word like null and a date. If you use a different YAML library, or one that implements YAML 1.2, run the same test through that one. Run the validation checks on the CSV before converting, since a seed file built from bad rows only moves the problem. Diff the file after a regeneration: with sort_keys=False and a stable input order, the diff should show only the changed records.

When YAML is the wrong container

When the data is large, as above. When it must round-trip through several tools, because YAML's type rules differ between parsers and versions, and the difference only shows up for values like these. When the consumer is a program and not a person, because JSON has fewer surprises. And when the content is only strings anyway: then the YAML's advantage, being readable, is a small one, and quoting every number is noise. If you do need YAML, convert deliberately: type the columns you mean to type, quote the rest, use the serializer and keep the troublemakers in a test. The conversion hub compares the formats, and the JSON article covers the same typing decisions for JSON.