Converting CSV to JSON XML YAML and Excel in Practice

Converting a CSV to another format is a small job that people repeat all the time and get subtly wrong. The formats are not interchangeable containers. JSON (RFC 8259) has strings, numbers, booleans and null. XML has text. YAML has a type system that guesses. Excel has cells with values and formats. Parquet has real column types. SQL has literals with quoting rules that depend on the engine. A CSV has none of this, so every conversion decides what each field means, and the decisions are where data goes missing. This guide uses one deliberately awkward file to compare eight targets, reads each result back to see what survived, and measures the cost of doing it to 193,708 rows. Each target has its own article, linked where it comes up.

The test file

Nine records and seven columns, chosen so that each format has something to trip on:

id,name,zip,score,active,joined,note
1,Ana Silva,02134,12.50,true,2025-03-01,plain
2,"O'Neil, Pat",10001,0,false,2025-03-02,"has, a comma"
3,"Bo ""Bee"" Chen",07030,7,true,2025-03-03,"says ""hi"""
4,Cy,90210,,true,2025-03-04,"line one
line two"
5,Di,01002,3.14,false,2025-03-05,
6,Ed,60601,100,,2025-03-06,NO
7,Zoë Ünal,00000,1234.50,true,2025-03-07,ünïcode ✓ ñ
8,Fay,1e5,-5.25,yes,2025-03-08, padded 
9,Gus,12345,1.10,true,2025-03-09,"<b>bold</b> a|b & ""q"""

What each row is for:

• zip: 02134, 07030, 01002, 00000 have leading zeros, and 1e5 looks like a float. These are identifiers, not numbers.

• score: 12.50, 1234.50 and 1.10 have trailing zeros that carry meaning (a price, a version). 0 and an empty field sit among them.

• active: true, false, an empty value and the spelling yes.

• note: a comma, double quotes, a line break, an empty value, the word NO (which YAML reads as a boolean), non-ASCII text, leading and trailing spaces, and <b>bold</b> a|b & "q", which has HTML, a pipe, an ampersand and quotes in one cell.

Seven decisions every conversion makes

1. Types. Is 12.50 a string, a double or a decimal? Is 02134 a string or an integer? Is true a boolean or text?

2. Empty. Does an empty field mean an empty string, a null or "absent"? Some formats cannot tell them apart.

3. Names. CSV headers are arbitrary strings; XML names, SQL identifiers and YAML keys have rules.

4. Structure. A flat table maps to a list of objects, rows of elements, a sheet or a table. Nested data maps back with some loss (JSON to CSV).

5. Escaping. Ampersands, quotes, pipes, backslashes, apostrophes and control characters each need handling in at least one target.

6. Encoding. UTF-8 everywhere is the default, and each target has its own way to say so.

7. Scale. Does the writer hold everything in memory or stream it?

The conversions below show what happens with these decisions made carelessly and carefully.

What survives: a round trip through each format

I converted the file to each format twice, once with every value written as a string and once with types a careful person would pick (an integer id, a float score, a boolean active, zip and everything else text, empty fields as nulls). Each result was read back, each value rendered as text and compared with the CSV, over 63 cells (9 rows by 7 columns):

python lab/conv/roundtrip_formats.py

JSON, all strings      63 of 63 cells read back as the same text
JSON, typed            59 of 63 cells read back as the same text   e.g. score '12.50' -> '12.5'; score '1234.50' -> '1234.5'; active 'yes' -> 'true'; score '1.10' -> '1.1'
YAML, all strings      63 of 63 cells read back as the same text
YAML, typed            59 of 63 cells read back as the same text   e.g. score '12.50' -> '12.5'; score '1234.50' -> '1234.5'; active 'yes' -> 'true'; score '1.10' -> '1.1'
XML                    63 of 63 cells read back as the same text
XLSX, all strings      63 of 63 cells read back as the same text
XLSX, typed            59 of 63 cells read back as the same text   e.g. score '12.50' -> '12.5'; score '1234.50' -> '1234.5'; active 'yes' -> 'true'; score '1.10' -> '1.1'
Parquet, all strings   63 of 63 cells read back as the same text
Parquet, typed         59 of 63 cells read back as the same text   e.g. score '12.50' -> '12.5'; score '1234.50' -> '1234.5'; active 'yes' -> 'true'; score '1.10' -> '1.1'

With every value as a string, JSON, YAML, XML, XLSX and Parquet each returned 63 of 63 cells. The leading zeros, the newline, the quotes, the NO, the <b> and the non-ASCII text all survived, since none of those formats rewrites text.

With types, JSON, YAML, XLSX and Parquet each returned 59 of 63, and the four cells that changed were the same in all four:

• score 12.50 became 12.5, 1234.50 became 1234.5, and 1.10 became 1.1. A float has no trailing zero.

• active yes became true. The boolean has two values, and the spelling is gone.

XML has no types, so there was no typed variant to try. That is the whole lesson in two lines: typing is where information is lost, and only typing. The text survived every container. So convert everything as text first, then type the columns that you will use as numbers, with a rule that you write down.

Convert and back: byte for byte

The round trip above compares values. A stronger test compares the file. I converted people.csv to JSON, YAML, XML, XLSX and Parquet with every value as a string, read each back, wrote a new CSV with Python's csv module (LF line endings) and compared its bytes with the original:

python lab/conv/byte_identical.py

JSON     identical to the original, byte for byte (468 bytes)
YAML     identical to the original, byte for byte (468 bytes)
XML      identical to the original, byte for byte (468 bytes)
XLSX     identical to the original, byte for byte (468 bytes)
Parquet  identical to the original, byte for byte (468 bytes)
original 468 bytes

All five came back identical to the original, byte for byte, 468 bytes each. That is a useful property and an easy test to automate: for any conversion pipeline that has a way back, the text-only path should give the original file. The reasons it works are the ones the previous section named. Nothing in the path interpreted a value, the quoting is deterministic (csv.writer quoted the same fields the original writer did) and the newline in the note survived every container. It is also the test that fails first when somebody adds a type conversion: the day a column is read as a float, the diff will show 12.5 where the original had 12.50. Keep it in the test suite.

The newline trap in every Python converter

One hazard sits in front of all the Python conversions in this guide, and it is in how the file is opened. A CSV field can contain a line break, and a CRLF file contains \r\n inside quoted fields as data. Python's open() translates line endings by default:

python lab/conv/newline_trap.py

open(path) with defaults   note = 'line one\nline two'
open(path, newline='')     note = 'line one\r\nline two'

With the defaults, the note came back as 'line one\nline two': the carriage return was silently dropped, so the data changed before any converter looked at it. With newline='' it came back as 'line one\r\nline two', as it was written. The csv module documentation requires newline='' for exactly this reason, and every script in this series opens its input that way. A converter that loses a \r is harmless until the day someone compares checksums of the values. The same applies on the way out: open the output with newline='' and let the csv writer choose the line ending.

One target at a time

JSON (the full article). csv.DictReader plus json.dumps is a faithful, streaming conversion. pandas wrote the zip 02134 as 2134.0, because one zip in the column (1e5) made it a float, rounded floats to 10 digits and escaped /. Miller wrote +14192381408 as a number; csvkit's csvjson wrote "id": 1.0 and took 36 s with inference on. Turn inference off, or write strings.

JSON Lines (article). One object per line: a CSV record with a newline took 2 lines in CSV and 1 line in JSON Lines. split, head, jq and a line-by-line Python loop all work on it. A cut-off file shows up as one bad line.

XML (article). The standard library writes <first name> for a column called first name and parsing it back fails; it also wrote a raw \x07 that no parser accepts. Check names and control characters yourself, and stream with XMLGenerator: 22 MB of memory instead of 376 MB for 193,708 rows.

YAML (article). Unquoted, NO loads as false, 02134 as the integer 1116 (octal) and 12:30 as 750. yaml.safe_dump quotes them and the round trip is exact. At 193,708 records YAML took 16.59 s and 1.4 GB to write and over 100 s to read with the pure Python loader: it is a configuration format.

Excel, writing (article). Write each column with the call that matches its meaning. The default write() made the text =1+1 a formula, a row past 1,048,575 returned -1 with no exception, and a 40,000 character string was truncated to 32,767.

Excel, reading (article). pandas read a text cell 02134 as 2134, in2csv did too, and qsv excel kept it. A number with the format 00000 is stored as 2134 in every tool, so the zeros must be rebuilt from the format.

SQL (article). Format-string INSERTs fail on O'Neil and run a DROP TABLE hidden in a value; doubling the quotes fixes both for PostgreSQL, while MySQL treats the backslash as an escape. One INSERT per row took 16.95 s for 193,708 rows, batches of 1,000 took 1.61 s and COPY 0.3 s.

Parquet (article). Typed columns, dictionary encoding and min/max statistics make it 26% of the CSV's size with zstd and answer a one-column query 5 times faster; DuckDB inferred amount as DOUBLE, so the type decision is yours.

Markdown (article). pandas left a|b unescaped and split the two-line note over two lines; Miller escaped the pipe; csvlook wrote 02134 as 2,134. A short script with \| and <br> produced a table with 7 cells on every line.

What it costs at 193,708 rows

Every target, from the same 17,297,141 byte customer file, every value as text:

python lab/conv/bench_table.py

format          bytes     gzip -6  seconds   peak KB
csv        17,297,141   4,696,571        -         -
json       52,745,597   5,871,412     1.52     12868
jsonl      48,723,768   5,793,794     0.31    215176
xml        62,997,177   5,958,601     3.06     22492
yaml       47,835,898   5,727,905    15.25   1395540
xlsx       12,931,722  12,353,102     8.73     25380
parquet     2,977,784   2,881,094     0.24    140488
markdown   22,333,650   5,136,409     0.90    210336
sql        23,618,179   5,195,016     0.82     13284

Read the columns in turn.

Time. The two fastest writers are DuckDB's: 0.25 s for Parquet and 0.38 s for JSON Lines. The Python streaming scripts for JSON (1.61 s), SQL (0.86 s) and Markdown (1.02 s) are in the same range, because the work is mostly formatting. XML is 3.07 s, Excel 9.36 s (compression and XML for every cell), and YAML is the outlier at 16.59 s even with the C-accelerated dumper, the cost of a general-purpose emitter deciding how to quote each string.

Memory. The streaming scripts held 13 to 25 MB: JSON 12,868 KB, SQL 13,288 KB, XML 22,544 KB, and Excel 25,376 KB in constant-memory mode. DuckDB used 140,052 to 216,452 KB because it reads the CSV in parallel chunks. The Markdown script held its rows in a list and used 210 MB, and YAML needed 1,395,848 KB: a library that builds a Python object for every value before writing.

Size. JSON is 3.0 times the CSV, JSON Lines 2.8, XML 3.6, YAML 2.8, Markdown 1.3 and the SQL batches 1.4. They are bigger because they repeat names or tags on every row. Excel (12.9 MB) and Parquet (3.0 MB) are smaller because they are compressed containers.

Compressed size. The right column is the better comparison of information: gzipped, JSON (5.87 MB), JSON Lines (5.79 MB), XML (5.96 MB), YAML (5.73 MB), Markdown (5.14 MB) and SQL (5.20 MB) are within about 16% of each other and 9 to 27% above the gzipped CSV (4.70 MB). The repetition in text formats compresses away. Excel gzipped to 12.35 MB, since it is already a zip. Parquet gzipped to 2.88 MB, since it is already compressed. If a file goes over a network, compress it and the choice of text format matters little; if it is parsed repeatedly, the format's structure is what counts.

Reading the formats back

A conversion is also a promise about reading. The same 193,708 records, read back by the plain Python route for each format, with time and peak memory:

for k in csv json jsonl xml yaml xlsx parquet; do printf '%-8s' $k; /usr/bin/time -f "%e s, peak %M KB" python lab/conv/read_back.py $k 2>&1 | tr '\n' ' '; echo; done

csv     csv 193708 records 0.24 s, peak 12952 KB 
json    json 193708 records 0.49 s, peak 260460 KB 
jsonl   jsonl 193708 records 0.53 s, peak 13076 KB 
xml     xml 193708 records 1.43 s, peak 30176 KB 
yaml    yaml 193708 records 19.15 s, peak 2071184 KB 
xlsx    xlsx 193708 records 15.43 s, peak 56968 KB 
parquet parquet 193708 records 1.14 s, peak 166156 KB 

The shape repeats the writing side. CSV and JSON Lines are the cheapest to read and stay flat in memory, because a reader needs one record at a time. A JSON array has to be loaded whole (260 MB for a 53 MB file), XML can be streamed with iterparse, and Excel in read_only mode is slow but light. YAML is the heaviest in both directions: 19 s and 2 GB to read what the CSV reader read in a quarter of a second. Parquet's number is for loading the whole table into memory with pyarrow; reading one column or a filtered slice, as in the Parquet article, is far cheaper. If a file will be read many times, the reading cost is the one to optimize.

Between targets: JSON Lines to Parquet to CSV

Conversions do not have to start from CSV. DuckDB reads JSON, JSON Lines and Parquet, and writes Parquet and CSV, so a chain of them is a few lines. This one took the 193,708 record JSON Lines file from earlier, wrote it as Parquet with every column declared VARCHAR, and wrote the Parquet back out as CSV:

E=work/conv/all
time duckdb -c "COPY (SELECT * FROM read_json('$E/c.jsonl', columns={id:'VARCHAR', first_name:'VARCHAR', last_name:'VARCHAR', email:'VARCHAR', phone:'VARCHAR', phone_unparsed:'VARCHAR', signup_date:'VARCHAR', balance:'VARCHAR', balance_currency:'VARCHAR', units:'VARCHAR', country:'VARCHAR', status:'VARCHAR'})) TO '$E/chain.parquet' (FORMAT parquet, COMPRESSION zstd)"
time duckdb -c "COPY (SELECT * FROM '$E/chain.parquet') TO '$E/chain.csv' (FORMAT csv, HEADER)"
duckdb -noheader -list -c "SELECT count(*) FROM (SELECT * FROM read_csv('$E/c.csv', all_varchar=true) EXCEPT SELECT * FROM read_csv('$E/chain.csv', all_varchar=true))"
duckdb -noheader -list -c "SELECT count(*) FROM (SELECT * FROM read_csv('$E/chain.csv', all_varchar=true) EXCEPT SELECT * FROM read_csv('$E/c.csv', all_varchar=true))"
cmp $E/c.csv $E/chain.csv && echo "the chain returned the original file byte for byte" || echo "files differ in bytes; the rows are the same (EXCEPT is 0 both ways)"

real	0m0.304s
user	0m0.309s
sys	0m0.133s

real	0m0.125s
user	0m0.189s
sys	0m0.056s
0
0
the chain returned the original file byte for byte

The first conversion took 0.30 s and the second 0.13 s. EXCEPT in both directions returned 0, and cmp reported that the final CSV was the original file byte for byte. Three formats, two conversions, and not one value or one quote changed, because every step kept the text, the columns were declared as text rather than inferred, and the CSV writer made the same quoting choices as the one that wrote the original. The null that JSON Lines held for an empty phone_unparsed became a Parquet null and then an empty CSV field, which is the same as the original. When you need to move between formats, this is the property to test for, and when it fails, the diff is the list of decisions the chain made for you.

Deciding the types

Once the text-only conversion is right, the question is which columns to promote. My procedure is short:

1. Identifiers stay strings. Anything you would not add up: zip and postal codes, phone numbers, account and order numbers, SKUs, version numbers. Every tool in this guide turned at least one of them into a number somewhere.

2. Money stays a string, or becomes a decimal with a stated scale. A binary float cannot hold 103.10 exactly, and a JSON number will usually be read as one. If the consumer needs numbers, say how many digits it can rely on.

3. Counts and plain integers become integers, as long as every value in the column is one. Check the whole column, not a sample, and decide what to do with 12x.

4. Booleans become booleans only when the spellings are a closed set you can list (true, false, and perhaps yes) and an empty value has a meaning. The yes that became true above is a reminder that the original spelling is gone.

5. Dates become dates when the format is a single known one; otherwise they stay strings until they are cleaned. ISO 8601 strings are a good home for them in JSON and XML.

6. Everything else stays text. Names, notes, free text, codes.

Each promotion is a line in the script and a test: the column, the rule, the rows that break it and what happens to them. A promotion that fails should fail the conversion, not guess; the validation guide has the checks for it. Keep the schema next to the data, as a JSON Schema, a Table Schema (the Frictionless article) or the DuckDB columns= argument itself, so the next person converts the same way.

Choosing a target

A checklist for each target

How each format spells non-ASCII text

CSV files in this series are UTF-8, and the target formats all handle Unicode, but each has a default for how to write it, and the defaults differ in size and in readability:

python lab/conv/encodings.py

JSON default        : {"n": "Zo\u00eb \u2713"} 24 bytes
JSON ensure_ascii=F : {"n": "Zoë ✓"} 17 bytes
YAML default        : n: "Zo\xEB \u2713"
YAML allow_unicode  : n: Zoë ✓
XML us-ascii        : <n>Zo&#235; &#10003;</n>
XML utf-8           : <n>Zoë ✓</n>
XML unicode (str)   : <n>Zoë ✓</n>

Python's json.dumps writes Zoë ✓ as "Zo\u00eb \u2713" by default, 24 bytes for the whole object, and as the characters themselves with ensure_ascii=False, 17 bytes. Both parse to the same string. yaml.safe_dump does the same with its default ("Zo\xEB \u2713", in double quotes with escapes) and writes Zoë ✓ with allow_unicode=True. ElementTree writes Zoë ✓ as character references when asked for us-ascii, and the characters themselves for UTF-8. For files that people read or diff, the readable spelling is better; for a channel that is not 8-bit clean, the escaped one is safer. Whichever you pick, declare it (an XML declaration, a Content-Type header, the contract) and keep the decision in the script.

Null versus empty, per format

A CSV field that is empty is one thing. The target formats have more than one way to be empty, and some of them cannot keep the two apart. I wrote the two values None and "" into each format and read them back:

python lab/conv/nulls.py

format         None reads back as     empty string reads back as distinguishes
JSON           None                   ''                         yes
YAML           None                   ''                         yes
XML <v/>       None                   None                       NO
XLSX           None                   ''                         yes
Parquet        None                   ''                         yes

JSON, YAML, Parquet and an Excel file written with XlsxWriter all kept the two apart. XML did not: <v/> reads back the same whether the element was written for a null or for an empty string, and ElementTree gives None for both. If a document has to carry the difference, the usual conventions are to omit the element for a null, to add an attribute, or to use xsi:nil with a schema, and the consumer must know which. The CSV itself usually cannot say either: an empty unquoted field is ambiguous, as the export guide showed for four databases. So decide, once, what an empty CSV field means when it becomes a null, and write the decision down: the missing values article lists the options.

The text-only test as part of the pipeline

The byte-for-byte test above is a script, and it is worth keeping next to the converter:

"""Convert people.csv to a format with every value as text, read it back, write a CSV and compare bytes with the original."""
import csv, io, json, os
import xml.etree.ElementTree as ET
import yaml
import pyarrow as pa, pyarrow.parquet as pq
import xlsxwriter
from openpyxl import load_workbook

SRC = "/batches/csv-0001-0200/lab/data/conv/people.csv"
W = "/batches/csv-0001-0200/work/conv/rt2"; os.makedirs(W, exist_ok=True)
orig = open(SRC, "rb").read()
rows = list(csv.DictReader(open(SRC, newline="", encoding="utf-8")))
COLS = list(rows[0])


def as_csv(rows_back):
    buf = io.StringIO(newline="")
    w = csv.DictWriter(buf, fieldnames=COLS, lineterminator="\n")
    w.writeheader(); w.writerows(rows_back)
    return buf.getvalue().encode("utf-8")


def norm(v):
    return "" if v is None else v


results = {}
results["JSON"] = as_csv([{c: norm(r[c]) for c in COLS} for r in json.loads(json.dumps(rows))])
results["YAML"] = as_csv([{c: norm(r[c]) for c in COLS} for r in yaml.safe_load(yaml.safe_dump(rows, allow_unicode=True))])
root = ET.Element("rows")
for r in rows:
    e = ET.SubElement(root, "row")
    for c in COLS:
        ET.SubElement(e, c).text = r[c]
results["XML"] = as_csv([{c: (x.find(c).text or "") for c in COLS} for x in ET.fromstring(ET.tostring(root, encoding="unicode"))])
wb = xlsxwriter.Workbook(f"{W}/s.xlsx", {"strings_to_formulas": False}); ws = wb.add_worksheet()
ws.write_row(0, 0, COLS)
for i, r in enumerate(rows, 1):
    for j, c in enumerate(COLS):
        if r[c] != "":
            ws.write_string(i, j, r[c])
wb.close()
w2 = load_workbook(f"{W}/s.xlsx")["Sheet1"]
results["XLSX"] = as_csv([{c: norm(w2.cell(i + 2, j + 1).value) for j, c in enumerate(COLS)} for i in range(len(rows))])
pq.write_table(pa.table({c: [r[c] for r in rows] for c in COLS}), f"{W}/s.parquet")
results["Parquet"] = as_csv([{c: norm(r[c]) for c in COLS} for r in pq.read_table(f"{W}/s.parquet").to_pylist()])
for name, data in results.items():
    print(f"{name:8} {'identical to the original, byte for byte' if data == orig else 'DIFFERENT'} ({len(data)} bytes)")
print("original", len(orig), "bytes")

It builds each file from the strings, reads it back with the library a consumer would use, writes a CSV, and compares bytes. When a new format or a new version of a library arrives, run it first: a library that begins to infer types by default, or that changes how it escapes, shows up as a DIFFERENT line before it touches production data. Run it again after each change to a type rule, expecting the differences you introduced and no others.

What the tools do by default

Three kinds of surprise recur, and they come from the tool more than from the format. Type inference is the main one: pandas reads a column with one 1e5 as a float and writes 2134.0; Miller writes +14192381408 as the number 14192381408; csvkit's csvjson writes "id": 1.0, and csvlook formats a zip as 2,134; DuckDB's reader inferred amount as DOUBLE. Each has a switch that turns the inference off (dtype=str, -S, -I, all_varchar=true) and in every case it is the first thing to turn on. Escaping is the second: ElementTree escapes text and not names, tabulate escapes nothing, Miller escapes the pipe and not the newline, and XlsxWriter's write() guesses a formula. Resource use is the third: pandas, ElementTree and PyYAML hold the whole document, and the streaming alternatives (a generator and json.dumps, XMLGenerator, DuckDB's COPY, XlsxWriter's constant-memory mode) keep memory flat.

None of this is a reason to avoid the tools. It is a reason to read each tool's defaults once, write the settings down in the conversion script and test the awkward file.

Habits that carry across formats

Write strings first. The 63-of-63 result is the argument. Produce a faithful text conversion, check it, then promote columns to numbers, booleans and dates one by one, each with a stated rule and a test. The cleaning hub is where dirty values get fixed before this step.

Never use a format string to make structured text. The JSON, XML, YAML and SQL articles each found a way that a hand-built string goes wrong: an apostrophe, an ampersand, a NO, a quote. Use the serializer for the target.

Keep identifiers out of numbers. Zip codes, phone numbers, account numbers and long ids are labels. Every tool that infers types will eventually turn one into a number: pandas into a float, Miller into an integer, csvlook into 2,134, PyYAML into an octal. Write them as strings and say so in the schema.

Test with the awkward file. The nine rows above cost nothing to run. Any new converter should pass them before it touches real data. They are in the repository next to the scripts.

Stream. A writer that holds a row at a time scales to any file. One that builds the whole document, like ElementTree for XML, pandas for JSON or Excel in normal mode, needs 15 to 40 times the file's size in memory.

Count and compare. The number of records out equals the number in, counted by a parser. For anything that matters, compare a checksum of the values, not only the count, as the PostgreSQL INSERT-against-COPY comparison did, which found 193,708 empty strings where COPY had NULLs.

Write down the decisions. The conversion script is the documentation: which columns are strings, how empty is represented, which names were changed, which characters were removed. A contract is the place to agree them with the people who receive the file.

How to tell it worked

Convert the awkward file, read it back with the tool the consumer will use and compare it, as the round trip above does. Check the count of records. Check the three or four values you care most about by eye: an identifier with a leading zero, a number with a trailing zero, a text with a quote and one with a newline. Run the full file once and note the time and peak memory. And run the validation checks on the output where the format allows it.

When you should not convert at all

If the consumer can read CSV, convert nothing: every conversion is a chance to change the data and the CSV already is the data. If the consumer wants a format only because of habit, ask what it needs, because a CSV with a documented contract is often enough. If the conversion is one-way and the original is thrown away, keep the CSV: it is the source, and every other format here is derived. And if the data has real structure, with parents and children, one table is the wrong shape for any of these targets: convert to several related tables, or to a document format that matches, and state the keys. For files too large to handle comfortably in any format, the large-file hub covers the other approaches.