Converting JSON to CSV XML YAML and Excel Without Surprises

JSON is the format data arrives in. It is rarely the format it ends up in. Someone wants a spreadsheet, a partner demands XML, a build tool reads YAML, the database loads CSV, the documentation needs a Markdown table. Each conversion looks like a one-line job, and in each case the one line works on the example from the tutorial. What goes wrong is specific and repeatable: the target format cannot hold something JSON can, and the converter makes a choice without telling you. A string becomes a number, a null becomes an empty string, a list of one becomes a plain value, a string that starts with an equals sign becomes a formula.

This guide is about those choices. It compares what each format can and cannot hold, runs one deliberately awkward document through each conversion and reports what changed, then walks through each target in turn with the settings that prevent the damage, covers the conversions back to JSON, and ends with a test you can run on any converter you rely on. The details of every conversion are in a shorter article, linked as they come up.

What each format can hold

A conversion is a translation, and the question to ask first is what the target language has no word for. JSON has objects, arrays, strings, numbers, booleans and null, nested to any depth, with keys that are any string. Compare the targets:

Read it as a list of things that will be lost or invented. CSV, Excel, SQL and Markdown tables are flat: nested objects must be flattened and arrays turned into rows or JSON text (turning nested JSON into flat tables covers that step). CSV, XML and Markdown carry no types, so every number and boolean is text and the reader has to guess back. XML has no arrays and no null. YAML guesses types from unquoted text, and the guess depends on the version of the YAML rules.

Two more differences cut across all of them. Key order: JSON objects have none by definition though parsers keep it, XML and CSV columns have it, and some writers sort keys. Comments: JSON has none, XML and YAML do, and a conversion to JSON destroys them.

A round trip with awkward values

Rather than reason in the abstract, I built one document of 23 fields chosen to hit the edges and converted it to each format and back using the default reader:

doc = {
  "str_no": "NO", "str_leading_zero": "007", "str_exp": "1e3", "str_date": "2026-10-26",
  "str_space": " lead", "str_empty": "", "null": None, "bool_true": True, "bool_false": False,
  "int_big": 12345678901234567890, "int": 42, "float_sum": 0.1 + 0.2, "float_big": 1e21,
  "uni": "café 日本", "newline": "line1\nline2", "comma_quote": 'a,b "q"', "pipe": "a|b",
  "formula": "=1+1", "looks_json": "[1]",
  "list": [1, 2], "empty_list": [], "obj": {"x": 1}, "empty_obj": {},
}

I wrote the document to the format, read it back, and compared each field's value and type with the original. The fields that changed:

For XML, the changed fields included True becoming 'true', 42 becoming '42', [1, 2] becoming ['1', '2'], and {"x": 1} becoming {'x': '1'}. The empty list was not in the result at all, because XML has no way to write "a list with nothing in it": no elements, no trace. The empty object and the empty string both came back as None.

Three conclusions follow. First, there is no lossless route except between formats that share a data model. YAML to JSON to YAML through the same library is nearly lossless, and CSV to JSON is not. Second, the reader matters as much as the writer: the YAML rows differ only in which parser read the file. Third, the failures are quiet. Not one of these conversions raised an exception or printed a warning. The only way to find them is the comparison, which is why the last section of this guide is a test harness.

CSV

CSV is the format for people with spreadsheets and for bulk loaders. The conversion needs flattening first, one table per array, and then care with three things (converting JSON to CSV with Python and pandas).

Nested values in cells. pandas writes a list or dict by calling str(), which yields Python code: "[{'sku': 'S2', 'qty': 3}]" with single quotes and True for booleans. It is not JSON and no parser reads it. Convert nested values with json.dumps first, or move the array to its own file.

Types on the way back. CSV is text. Reading it back, pandas turned "007" into 7 and "1e3" into 1000.0 in my test, and read an empty string as missing. Identifiers, codes and anything with leading zeros must be read as strings (dtype=str) and declared as text in the destination. The 20 digit integer survived the round trip as uint64, which is luck of the width; a 25 digit one would not.

Excel's reading of your file. Excel on Windows opens UTF-8 CSV as the local code page unless the file starts with a byte order mark, so café appears garbled. encoding="utf-8-sig" fixes it for Excel users and breaks other tools that treat the BOM as part of the first header. Also, a cell that starts with =, +, - or @ is evaluated as a formula, and pandas writes such values unchanged: that is CSV injection, and a prefix character is the mitigation.

On the command line, jq -r '... | @csv' works for rows of scalars and fails on arrays (array (["a","b"]) is not valid in a csv row), and DuckDB's COPY ... TO 'x.csv' (HEADER) wrote 50,000 order lines in 0.15 s with lowercase booleans. All four tools I tried (pandas, jq, DuckDB, Miller) agreed on the row count.

YAML

YAML to humans is JSON with fewer braces, and the conversion is structurally trivial. The danger is the strings (converting JSON to YAML on the command line). YAML decides the type of an unquoted scalar by its look, and the rules changed between versions 1.1 and 1.2. A YAML 1.1 reader (PyYAML, Ansible, many older libraries) reads NO, yes, off as booleans, 0123 as octal and 12:30 as a number of seconds. A 1.2 reader does not.

The mikefarah yq v4 tool writes country: NO and time: 12:30 bare, which is correct in 1.2, and PyYAML read them back as False and 750. The Python yq and PyYAML's safe_dump quote them. The practical rule: if you do not know the reader's YAML version, use a dumper that quotes ambiguous strings, and test the output with both a 1.1 and a 1.2 parser. Remember sort_keys=False in PyYAML, or the keys come out alphabetical.

Going the other way, YAML to JSON (converting YAML to JSON for tools that need it), the traps are YAML-only values. A date such as 2026-10-26 loads as a date object that json.dumps refuses (Object of type date is not JSON serializable), keys like 1 and ~ become "1" and "null", aliases expand into copies, a file with several documents fails with expected a single document in the stream, and .inf is written as Infinity, which is not valid JSON. Always use safe_load: yaml.load can construct arbitrary objects, while safe_load rejected a !!python/object/apply tag with a ConstructorError.

XML

XML needs a root, has no arrays, no null and restricted element names (converting JSON to XML and handling arrays and names). A converter such as xmltodict requires a single top-level key (Document must have exactly one root), writes arrays as repeated elements, and writes everything as text. The 12 changed fields in the round trip were all consequences of that.

Names are where it breaks without telling you. A key such as first name or a/b raised a ValueError, but 2026-10-01 (starts with a digit) and a:b (a namespace prefix with no declaration) were written, and the result failed to parse (not well-formed). A raw control character, the bell character, was written into the file and also made it malformed with no warning. The defense is to parse your own output with a real XML parser in the same script.

The reverse direction has the opposite problem (mapping XML arrays and mixed content to JSON): a single <item> parses to a value and two parse to a list, so the same code works for most documents and fails on the order with one item. force_list for the elements that can repeat fixes it. Mixed content (text interleaved with elements) cannot be represented by a JSON object at all: <p>Hello <b>bold</b> and <i>italic</i> world</p> became {"b": "bold", "i": "italic", "#text": "Hello and world"}, with the order gone.

Excel

An Excel workbook is typed, which is both an advantage and a trap (converting JSON to an Excel workbook). A date column becomes real dates; a number column becomes numbers stored as 64-bit floats, so 12345678901234567890 was saved as 1.234567890123457e+19 and 0.30000000000000004 read back as 0.3. Write identifiers as text. Two hard limits: 32,767 characters in a cell (longer strings were truncated with only a UserWarning) and 1,048,576 rows per sheet (ValueError: This sheet is too large!).

Both engines I tried (openpyxl and xlsxwriter) wrote a string starting with = as a formula, so =1+1 was a live formula in the saved file. In xlsxwriter the option strings_to_formulas: False stops it, and in openpyxl you set the cell's data_type to "s" after assigning. Each array should be its own sheet with a key column back to its parent, and the header row should be frozen and filtered, which takes three lines.

SQL INSERT statements

Generated SQL is the most dangerous of the conversions, because data becomes code (converting JSON to SQL INSERT statements). Single quotes must be doubled. The tempting jq @sh is shell quoting and produces 'O'\''Brien', a syntax error in SQL. And MySQL, with default settings, reads a backslash in a string literal as an escape: a file with the paths C:\temp and a\b loaded in PostgreSQL and SQLite as written, but in MySQL the bytes stored were 433A09656D70 and 6108, a tab and a backspace, with no warning. NO_BACKSLASH_ESCAPES fixes it.

The performance difference is large. 50,000 single-row statements took 8.04 s in PostgreSQL autocommit mode and 3.63 s in one transaction, while 50 statements of 1,000 rows each took 0.15 s and COPY from CSV 0.04 s. In SQLite, the same 50,000 statements as separate transactions on a file database took 55 s, against 0.06 s inside BEGIN/COMMIT. Better still, avoid SQL text: parameters and bulk loaders do not need quoting at all.

Markdown tables

A Markdown table is a rectangle of pipes, and a pipe inside a value ruins it (converting JSON to a Markdown table). pandas' to_markdown does not escape it; a Markdown parser read 15 cells in a table that should have had 12. Newlines in cells add broken extra rows. Escape the backslash first, then the pipe, and turn newlines into <br>; or let DuckDB's .mode markdown do it, which printed a\|b itself.

Parquet

Parquet is the destination for analysis (converting between JSON and Parquet). It kept the nesting, compressed 36.4 MB of events to 0.5 MB with zstd, and answered a grouped query in 0.03 s against 0.13 s on the JSON. The round trip back to JSON changed the timestamp text (2026-10-02T01:01:00Z became 2026-10-02 01:01:00+00) and turned absent keys into explicit nulls. A column that mixes numbers with a string made pandas fail (Could not convert 'slow' with type str: tried to convert to int64). Declare types for the columns that matter.

Converting from CSV back to nested JSON

The reverse of flattening groups rows by a key (converting CSV to nested JSON grouped by a key). itertools.groupby built 20,000 orders from 50,000 lines in 0.13 s, and it groups only neighboring rows: shuffled input gave 49,999 groups for 20,000 orders. DuckDB's GROUP BY with list() needs no sorting and ran in 0.10 s, and a pandas loop over the groups took 8.36 s. Types must be restored explicitly, since every CSV field is a string.

Flatten before you convert

Four of the seven targets (CSV, Excel, SQL, Markdown) are flat, and the flattening decisions come before any format decision. A document with an array is two tables, and the choice of how to split it is the same whatever the destination file is. In the order data, 20,000 orders hold 50,000 items. The orders go to one table with 20,000 rows and the items to another with 50,000 rows and a column that holds the order id. A single wide table that repeats each order on each of its items is also possible, with 50,000 rows and repeated customer columns, but then a sum over an order-level field counts it once per item. Two arrays on the same record are worse: joined into one table they multiply, and a count of comments in my blog data came out at 23,217 rows instead of 19,147, with a like total 2.15 times the true one. The whole discussion, with the checks, is in turning nested JSON into flat tables. The relevant rule here is that the converter should be fed tables, not documents.

Then keep the key. Whatever the format, every child row carries its parent's identifier, and the position inside the array if order matters (keeping the parent key when you unnest child records). A spreadsheet sheet of items without an order id is a list nobody can use.

What the formats cost in size and time

The same 20,000 orders with their 50,000 items, written in each format with the usual Python tools, measured as bytes, gzip size and the time to produce them:

Several things are worth noticing. CSV is the smallest text format because it names the columns once, and compact JSON is within a factor of 2.5 of it. After gzip, all the text formats are within 20% of each other (488 KB to 599 KB), so size on the wire hardly depends on the format when the transport compresses. The Excel file is already compressed (it is a zip), so gzip makes it smaller by almost nothing, and Parquet is two orders of magnitude smaller than everything else because it stores columns of repeated values with dictionary and zstd compression.

The time column holds the real surprise: YAML takes 6.77 s to write what JSON writes in 0.04 s, a factor of about 170. The C dumper helps (2.20 s) and is still 50 times slower. Reading is worse: yaml.safe_load of that 5 MB file took 13.8 s, and 3.9 s with the C loader, against 0.1 s for a JSON round trip of the same data. For a configuration file of a few kilobytes the difference is invisible. For a data set of megabytes, YAML is the wrong container, and nothing about the conversion hides that. Excel is slow to write for another reason: it builds a zip of XML for every cell.

Parsers and writers are attack surface

Conversion code reads and writes files that other people influence, and every format has a way to hurt you.

Spreadsheets and CSV. A string that starts with =, +, - or @ is a formula in a spreadsheet. Both Excel engines and pandas' CSV writer passed such strings through unchanged. If a name field in the JSON came from a user, opening your export in Excel runs their formula. Neutralize the leading character in the export, or switch off formula writing in the library.

SQL. Generated statements are code. A doubled quote makes a value harmless; a missed escape (MySQL's backslash) corrupts it, and a bad one is an injection. Use parameters and bulk loaders, which carry values separately.

YAML. yaml.load can construct Python objects, and safe_load refuses them (ConstructorError for a !!python/object/apply tag). Aliases can expand: a small file of nested anchors can become a very large structure. Never load YAML from outside your control with anything but a safe loader and a size limit.

XML. The format has entities, external references and namespaces, and parsers differ in what they allow by default. For input you do not control, use a parser configured to refuse external entities and DTDs, and limit the size.

Markdown. A cell can contain raw HTML, which many renderers pass through. Escape < and & when the values come from users.

Your own writer. Do not log or export a field you were asked to keep secret just because it was in the JSON. A converter takes every key, and a column of tokens in a spreadsheet is a leak.

An order of work for a new conversion

When someone asks for "that data as X", the sequence that avoids the surprises:

1. Ask who reads it, with what tool and what version, and what they do with it. The reader decides the format, the types and the encoding.

2. Look at the data for the awkward cases: identifiers with leading zeros, values that start with =, strings that look like numbers or dates, embedded newlines, empty strings against nulls, numbers beyond 15 digits, non-ASCII text, keys that are not names.

3. Decide the flat shape or the nesting, and write the grain of each table in a sentence.

4. Convert a small, awkward sample first, read it back with the reader's own tool, and compare with the harness above.

5. Convert everything, and check the row counts and one numeric total against the JSON.

6. Look at the file as the recipient will: open the spreadsheet, load the CSV, render the Markdown.

7. Keep the JSON. The converted file is a copy.

The seven steps take much less time than the first round of "the number looks wrong" emails.

Numbers, dates and text across the formats

Three kinds of value cause most of the damage, and each format treats them differently.

Long numbers. JSON allows any number of digits; most parsers read them into 64-bit doubles. The 20 digit integer 12345678901234567890 in my test document survived pandas CSV (read back as uint64), both YAML parsers and XML (as the string '12345678901234567890'), and did not survive Excel, which stored it as a number and read back 1.234567890123457e+19. Written as text it is exact everywhere. The rule that avoids all of it: identifiers, account numbers and anything you would not add up are strings in every format (how JSON handles big numbers).

Decimals. 0.1 + 0.2 is 0.30000000000000004 as a double. CSV and YAML preserved those digits, XML preserved them as text, and Excel reduced the value to 15 significant digits (0.3) when read back. For money, use a decimal type where the target has one (DECIMAL(10,2) in Parquet and SQL) or strings with a declared scale. DuckDB inferred a double for a price column and summed 50,000 line totals to 6375490.000000892; a declared DECIMAL(10,2) gave exactly 6375490.00.

Dates. JSON has no date type, so dates are strings, and each target decides whether to parse them. Excel and Parquet will make them real dates if you convert first. CSV, XML and Markdown keep them as text. YAML treats an unquoted 2026-10-26 as a date, and json.dumps then fails on it. Parquet and DuckDB rewrote 2026-10-02T01:01:00Z as 2026-10-02 01:01:00+00 on the way out. The only portable form is ISO 8601 text with an explicit zone, produced by you.

Text encoding. UTF-8 is the default for JSON, XML and YAML, and the safe choice for CSV. Excel on Windows needs the byte order mark to recognize UTF-8 in a CSV; adding it (utf-8-sig) fixed the garbled café and the Japanese test string and is wrong for every other consumer. Python's json.dumps writes non-ASCII as \u escapes unless you pass ensure_ascii=False, which is why Japanese text in a JSON file can look like escape sequences. Both forms are the same data.

Line endings and newlines inside values. CSV and Markdown are line-oriented, and a newline in a value changes the number of physical lines: a quoted CSV field can span several lines, a Markdown cell cannot at all. JSON Lines escapes the newline as the two characters in a string and keeps one record per line (JSON Lines versus CSV for event data). The line terminator written by pandas on Linux was a line feed; Windows tools may expect a carriage return and line feed.

Choosing the target

Pick by the reader, not by habit:

When two targets could do the job, pick the one that keeps more of the data's meaning.

A test you can run on any converter

The round trip above is a harness, and it is worth keeping. A dozen lines per format:

def diff(a, b):
    out = {}
    for k in a:
        if k not in b:
            out[k] = (a[k], "<missing>")
        elif a[k] != b[k] or type(a[k]) != type(b[k]):
            out[k] = (a[k], b[k])
    return out

# write doc with the converter, read it back with the consumer's reader
print(diff(doc, restored))

Keep the 23 field document next to your converter and run it in CI with the reader that your downstream actually uses. The output is a list of exactly the fields that your pipeline will corrupt, which is better than discovering them one at a time in production. Compare the type as well as the value: 42 and '42' are equal in many tools' printed output. For the real data, add the row count, a numeric total and a spot check of a record containing the awkward characters (profiling an unknown JSON file before analysis lists what to measure).

When conversion is the wrong approach

The consumer can take JSON. Most modern tools accept JSON or JSON Lines directly, and every conversion costs you fidelity. Send JSON and validate it (validating JSON with JSON Schema in real projects).

You need the exact original. Keep the JSON as the system of record and treat every converted file as a disposable copy.

The conversion is the product. If the output feeds a standard (an invoice format, a regulatory filing), build it from the specification and validate against the schema, not from a generic converter.

The standards behind the formats are the JSON specification, RFC 8259, the CSV definition in RFC 4180, the XML 1.0 specification and the YAML 1.2.2 specification. The family of conversion articles starts with converting JSON to CSV with Python and pandas. For a quick look at a document before converting it, use the JSON formatter.