Converting XML to JSON CSV YAML and Excel Without Data Loss

Someone gives you XML and asks for it as JSON, or as a spreadsheet, or in a form they can read. Each version of that request sounds like a single operation, and each is really three: decide the shape of the result, write it, and check that nothing went missing. Tools that do it in one call make the first and third decisions for you without telling you.

This guide takes one small file through all four common targets (JSON, CSV, YAML and Excel), shows what each target can and cannot hold, runs the generic converters to show where they surprise you, and ends with a method that I use whenever the conversion matters: parse once, write the shape yourself, and reconcile the result against the source with a count and a sum. The spokes linked throughout go deeper on each pair.

The file and what is in it

<?xml version="1.0" encoding="UTF-8"?>
<library updated="2026-10-01">
  <book id="b1" available="true">
    <title>Cache Patterns</title>
    <author>Ana Lee</author>
    <author>Bo Chen</author>
    <price currency="USD">29.50</price>
    <note>Covers R&amp;D</note>
  </book>
  <book id="b2" available="false">
    <title>SQL Tuning, 2nd Edition</title>
    <author>Cy Ortiz</author>
    <price currency="USD">44.00</price>
  </book>
  <book id="b3" available="true">
    <title>Indexes "Explained"</title>
    <author>Di Park</author>
    <price currency="VND">250000</price>
    <note/>
  </book>
</library>

It is small on purpose, because every feature of it is a conversion decision. author repeats, and in two books it does not. note is present with text in book 1, absent in book 2 and present but empty in book 3. price carries its currency as an attribute, and the third price is in a currency whose amounts are a thousand times larger than the others. available holds the text true or false. The titles contain a comma and a pair of double quotes. The first note contains an ampersand, written & in the file.

Before converting anything, take two measurements from the source. They are the yardstick for every output:

xmllint --xpath 'count(//book)' library.xml; echo
xmllint --xpath 'count(//author)' library.xml; echo
xmllint --xpath 'sum(//price[@currency="USD"])' library.xml; echo

3
4
73.5

Three books, four authors, and 73.5 as the total of the US dollar prices. If a conversion changes any of these, it has lost or invented data. I use this habit on every conversion, however simple, because it costs a minute and has caught more bugs than any amount of reading the output.

What a conversion has to decide

Whatever the target, the same five questions come up. A converter that answers them for you is making your decisions.

1. What is a record? The repeating element that corresponds to one row or object. Here it is book. Every target is easier once this is fixed.

2. Where do attributes go? Beside the child elements (as the same kind of field), in a separate namespace of keys (@id), or nowhere. The id and available attributes are data, and updated on the root is metadata about the file.

3. What happens to a repeated child? A list in a format that has lists, a joined string or a second table in one that does not.

4. What is the type of a value? XML has none. A target with types needs you to pick them: true is a boolean, 29.50 is a decimal or a string, b1 is text.

5. What is missing, and what is empty? The absent note and the empty <note/> are different in XML, and most targets collapse them.

The rest of the guide is those questions answered for each target.

Targets at a glance

Read the table as a list of what the target takes away. JSON and YAML keep the structure and lose the XML specifics. CSV and Excel flatten, so a repeated child becomes a decision. The third row is where the types come from, and the fifth where the distinction between "no note" and "empty note" disappears in the flat formats.

One parse, four writers

The method that has worked for me is to parse the XML once into a list of plain Python dictionaries that already have the shape and types I want, and then give each target a small writer. The parsing code is where the decisions live, and it is the same for every target:

from lxml import etree

def records(path):
    root = etree.parse(path).getroot()
    for b in root.iterfind("book"):
        p = b.find("price")
        yield {
            "id": b.get("id"),
            "available": b.get("available") == "true",
            "title": b.findtext("title"),
            "authors": [a.text for a in b.iterfind("author")],
            "price": p.text,
            "currency": p.get("currency"),
            "note": b.findtext("note") or None,
        }

rows = list(records("library.xml"))

Each line answers one of the five questions. The record is book. The attributes id and available sit with the other fields, and the root's updated is dropped on purpose. authors is always a list, so the shape does not depend on how many there are. available is converted to a real boolean. price stays as the text 29.50, because a decimal is best parsed at the point of use and a float would turn 29.50 into 29.5. The currency is its own field, since an amount without its currency is a number with no meaning. And note maps both the absent and the empty element to None, with or None doing the collapse: that is a decision to merge two states, written in one visible place.

The writers are then short:

import csv, json, yaml
from decimal import Decimal
from openpyxl import Workbook

json.dump(rows, open("all.json", "w"), indent=1)
yaml.safe_dump(rows, open("all.yaml", "w"), sort_keys=False, allow_unicode=True)

with open("all.csv", "w", newline="", encoding="utf-8-sig") as f:
    w = csv.writer(f, lineterminator="\n")
    w.writerow(["id", "available", "title", "authors", "price", "currency", "note"])
    for r in rows:
        w.writerow([r["id"], str(r["available"]).lower(), r["title"], "; ".join(r["authors"]),
                    r["price"], r["currency"], r["note"] or ""])

wb = Workbook(); ws = wb.active
ws.append(["id", "available", "title", "authors", "price", "currency", "note"])
for r in rows:
    ws.append([r["id"], r["available"], r["title"], "; ".join(r["authors"]),
               float(Decimal(r["price"])), r["currency"], r["note"]])
wb.save("all.xlsx")

The CSV is written with encoding="utf-8-sig" so that Excel recognizes UTF-8 when it opens the file; the three bytes ef bb bf at the start are the byte order mark. The CSV module does the quoting, and the Excel cells are typed by the Python types of the values. The result for the CSV, byte for byte apart from the mark:

id,available,title,authors,price,currency,note
b1,true,Cache Patterns,Ana Lee; Bo Chen,29.50,USD,Covers R&D
b2,false,"SQL Tuning, 2nd Edition",Cy Ortiz,44.00,USD,
b3,true,"Indexes ""Explained""",Di Park,250000,VND,

Reconciling every output against the source

The last step of the script reads each output back with the matching reader, and compares it with the measurements from the XML:

source books 3 authors 4
json 3 4
yaml 3 4 same as json: True
csv  3 4
xlsx 3 4
USD total json: 73.50 | xlsx: 73.5

All four have 3 books and 4 authors, the YAML loads back to the same structure as the JSON, and the USD total agrees. The two totals print differently (73.50 and 73.5) because one is a decimal summed from text and the other a float read from cells, and they are the same quantity. If you want a stricter check, compare as decimals after rounding to two places. This reconciliation is the evidence that the conversion is complete, and it is the thing I would put in the commit message or the email to the person who asked.

Notice what the script did not check: that the YAML, loaded by a different tool, means the same thing. That depends on the loader, as the section on YAML below shows.

JSON

JSON is the target when a program will read the result, and the details are in converting XML to JSON and the attribute problem. Two things matter most. The shape should not change with the data: a book with one author should still have a list. And the types should be the ones you want.

For comparison, here is what a generic converter, xmltodict, did to the first book:

{
 "@id": "b1",
 "@available": "true",
 "title": "Cache Patterns",
 "author": ["Ana Lee", "Bo Chen"],
 "price": {"@currency": "USD", "#text": "29.50"},
 "note": "Covers R&D"
}

It is a faithful picture of the XML, with the @ convention for attributes and #text for the value beside an attribute, and author a list only because the first book has two. The second book has "author": "Cy Ortiz", a string. A program that loops over book["author"] gets the characters of the name for the second book. The call force_list=("book", "author") fixes it, and you only know to add it if you looked. My own records are better for a consumer: flat keys, authors always a list, a boolean flag and a separate currency field.

Use the generic converter for exploration, because it needs no thought, and the explicit mapping for anything another program will depend on.

YAML

YAML is the target when a person will read or review the result. It nests like JSON and has the same list handling, and it drops the braces and quotes, which is what makes it comfortable to read. The conversion from the records is one line (safe_dump), and the issues are all about scalars. PyYAML quotes strings that would be read as something else: '2026-10-01', 'true', '29.50'. That quoting is correct, and if someone edits the file and removes the quotes because they look noisy, a YAML 1.1 loader will turn no and NO into False, 1.10 into 1.1 and 0755 into the integer 493, with no error. The full demonstration is in converting XML to YAML for config review, which also shows the xq -y command for a quick look from the shell.

The rule I keep: a converted YAML file is for reading and diffing. If it becomes the source of truth that a program loads, give it a schema, or load it with a tool that reports the type of every scalar.

CSV

CSV is the target when the result is a table that a spreadsheet or a database will import. It cannot nest, so the repeated author needs a rule (here, joined with a semicolon and a space), and it has no types, so everything arrives as text and the reader guesses. The details, including why the generic pandas.read_xml route lost data, are in converting XML to CSV for spreadsheet users. The summary of that comparison is worth repeating, because it is the clearest example of a silent loss in this whole guide:

   id  available                    title    author     price        note
0  b1       True           Cache Patterns   Bo Chen      29.5  Covers R&D
1  b2      False  SQL Tuning, 2nd Edition  Cy Ortiz      44.0         NaN
2  b3       True      Indexes "Explained"   Di Park  250000.0         NaN

The first book has two authors, and the frame has Bo Chen. Ana Lee is gone, with no warning, and the price column has lost its currency and its trailing zeros. Counting the authors in the output (3 instead of 4) catches it immediately, which is why the reconciliation counts them.

Two practical points about CSV that the file format cannot express. Write the file in UTF-8 with a byte order mark if the recipient uses Excel, or accented names arrive garbled (opening UTF-8 CSV in Excel). And put identifiers that look like numbers in a text-aware format, since a spreadsheet will drop the leading zero of 00123 unless the column is imported as text.

Excel

An xlsx workbook is the target when the reader lives in Excel and wants typed cells. You get what CSV cannot give: numbers that are numbers, a boolean that filters as TRUE and FALSE, text that stays text, no quoting rules. Reading the workbook back showed ('b2', False, 'SQL Tuning, 2nd Edition', 'Cy Ortiz', 44, 'USD'): the boolean is real, the price is numeric (stored as 44, displayed as 44.00 through a cell format), and the title with its comma needed no escaping. The full script, the number formats and the pitfalls (dates, long identifiers, formulas) are in converting XML to Excel with Python.

The currency column is not decoration. A column that holds 29.5 US dollars next to 250000 dong is a total waiting to be wrong. Whatever the target, keep the unit with the number.

The reverse direction, and the case where Excel itself reads the XML, are covered in importing XML into Excel with an XML map and in the spreadsheet guide at the end of this cluster, moving XML into and out of spreadsheets.

The XSLT route

A different way to convert is to write a stylesheet. XSLT transforms XML to XML or to text, and it needs no programming language, only xsltproc. For an XML-to-XML change or an HTML report it is the right tool (using XSLT to transform XML into another shape, transforming XML to HTML with XSLT, running XSLT from the command line). For CSV it is a trap. The stylesheet in the command-line article wrote this row for the third book:

b3,"Indexes "Explained"",250000

The inner quotes were not doubled, so the row is not valid CSV, and the first two rows look fine. XSLT 1.0 has no string replace, so doubling a quote needs a recursive template. Keep text-format output (CSV, SQL, JSON) in a language with a library for that format, and use XSLT where the output is markup.

Going the other way

All of the above is XML to something. The opposite directions have their own problems: names that are legal in JSON and illegal in XML, arrays of one that lose their list-ness, nulls with no XML equivalent, and strings that break when pasted into a template. They are covered in converting JSON to XML with sensible element names and converting CSV to XML with a template, and the JSON side of the family is in converting JSON to CSV, XML and YAML.

Choosing a target

The question to ask first is who or what reads the result. The answer picks the target, and the target picks the shape.

When two readers need the result, produce two outputs from the same records. That is the reason for parsing once: adding a target is a new writer, and it cannot disagree with the others about what the data is.

Large files

All of the above holds records in a list. For a file of a few hundred megabytes, that is the wrong structure, and the fix is the streaming loop from parsing large XML files with iterparse: read one book at a time, hand it to the writers, clear it. CSV and JSON Lines can be written row by row, YAML and a single JSON array cannot be streamed so easily, and an xlsx workbook is built in memory by openpyxl unless you use its write-only mode. For the common case of a file too large for a spreadsheet anyway (over 1,048,576 rows), CSV or a database is the answer, not xlsx. Counting is also streamable: iterparse with a counter gives the number of records without loading the tree.

Numbers, dates and booleans across the targets

Types are where the four targets disagree most, and where a conversion that looked right gives a wrong answer a month later. XML gives you text. Each target then wants a decision.

Money. I kept 29.50 as the string "29.50" in JSON, YAML and CSV, and as a float in the Excel cell. The string survives every format unchanged: JSON has only a binary float for numbers in most parsers, so 29.50 read back as a number is 29.5, and sums of many floats drift by fractions of a cent. The workbook is the exception, because a spreadsheet only has doubles, and the display format puts the two decimals back. If a consumer will add the amounts to another system's, send integer cents or a decimal string and say which in the documentation. The same value in a different currency, as the third book shows, needs its currency beside it in every format.

Dates. The sample has none, and real files have several shapes per file, as the CRM export in importing an XML export from another system showed with 2019-04-02 and 02/05/2020 side by side. Convert to ISO 8601 at the parsing step and keep it as text in JSON, YAML and CSV. Only the workbook has a real date type, and Excel dates have no time zone, so a timestamp with an offset has to be converted to the reader's zone and labeled. A YAML loader will also turn an unquoted 2026-10-01 into a date object, which is why the dumper quotes it.

Booleans. XML spells them true, false, 1, 0, Y, N and yes depending on the producer. Normalize in the parsing step with a dictionary that raises on an unknown value, so a flag with an unexpected spelling fails the conversion and does not become false. JSON and YAML take a real boolean, Excel takes one too, and CSV takes whatever text you choose, so choose true and false and keep it for every file you produce.

Identifiers. Anything that looks like a number and is not one (a ZIP code, a part number, a phone number, 00123) stays a string in every format, and in a spreadsheet it needs a text format as well. The JSON and YAML writers handle it without help. CSV does not, because the reader decides, and Excel will strip the zeros of a column it believes is numeric.

Characters, encodings and line endings

The parse step is where characters become characters, so the writers can be strict. The parser reads the XML encoding declaration and gives you Unicode, whatever the file was. Each writer then picks its own encoding, and these choices are easy to leave to a default:

• JSON is UTF-8 by specification, and json.dump with the default ensure_ascii=True writes non-ASCII characters as escape sequences. That is valid, and unreadable to a human, so pass ensure_ascii=False and open the file as UTF-8 if people will read it.

• YAML written with allow_unicode=True keeps accented characters as they are. Without it, PyYAML escapes them.

• CSV for Excel wants UTF-8 with a byte order mark, as in the sample, or a legacy code page. For anything else, write UTF-8 without the mark, which a mark can confuse.

• xlsx stores strings as UTF-8 inside the zip, and the encoding is not a decision you make.

Line endings matter in CSV only. The writer above uses \n and passes newline="" to open, so Python does not translate them; RFC 4180 specifies CRLF, and most readers accept both. A value with an embedded line break is quoted, and survives. XML itself normalizes line endings to a line feed when it parses, so the input's CRLF is already gone by the time the records exist.

Characters that XML 1.0 forbids, such as a bell or a vertical tab, will not be in a parsed file, because the parser refuses it. They can be in the JSON or CSV that you convert from, which matters for the reverse direction and is the reason converting CSV to XML with a template deals with them explicitly.

Namespaces and attributes in the conversion

The sample has no namespaces, and most real files do, so the parsing function needs to be namespace-aware from the start. An element <g:price> in a product feed is a different element from <price>, and findtext("price") will not find it. Pass a namespace dictionary, as in loading a product feed in XML into PostgreSQL, and decide what the output key is called: the local name (price) is usually right for a table or a JSON object, unless two namespaces use the same local name in one record, in which case you keep the prefix or rename one. The generic converters keep the prefix as written in the file, so g:price appears as a key, and the key then depends on the prefix the producer chose; a producer that changes g to gm changes your column names.

Attributes deserve the same deliberate treatment. In the records above, id and available are attributes and title is an element, and the output makes no distinction: they are all fields. That is the right default for data. When an attribute and an element have the same name in one record, rename one (id and id_attr). When an attribute is metadata about the document (xml:lang, xsi:schemaLocation, version), leave it out of the rows and keep it in a header record. Mapping XML attributes to columns without mistakes goes through the whitespace and default-value rules that apply.

Making the conversion repeatable

A conversion run once by hand will be run again by someone else, and the second run is where differences appear. A few habits keep it stable:

• Keep the parsing function and the writers in one script in version control, with the library versions pinned. The same converter on a newer xmltodict or PyYAML can emit different quoting or key order.

• Read the file as bytes and let the parser decode it. Never open it in text mode.

• Write the output to a new file and rename it into place when the run succeeds, so a half-written file is never picked up by the next stage.

• Run the reconciliation on every run and fail on a mismatch. The script above printed its counts; a real one compares them and exits non-zero.

• Keep a sample input and its expected outputs in the repository, and compare against them when you change the script. The three-book file is a good start, because it has the awkward cases.

• Record the source file's checksum next to the output, so you can tell which input produced which result.

For recurring feeds, the script is also the place to notice that the sender changed something. Count the elements by name on each run (counting elements and attributes in an unknown XML file) and compare with the last run; a new element name or a count that falls to zero is a change in the sender's format, and it is much better to learn that from a failed run than from a wrong spreadsheet.

A checklist before you send the result

1. Counted the records and the repeating children in the source and in every output.

2. Compared at least one sum or checksum across source and output.

3. Looked at the three oddest rows: the one with the most repeated children, the one with the most empty fields and the one with the strangest characters.

4. Opened each output with the reader that the recipient will use, or one like it.

5. Written down the decisions: what is a record, where attributes went, how lists were joined, what empty means, what encoding.

6. Kept the script. A conversion you cannot repeat is a conversion you cannot fix.

Where to read next

The related spokes in this cluster are converting XML to JSON and the attribute problem, converting XML to CSV for spreadsheet users, converting XML to Excel with Python, converting XML to YAML for config review, converting JSON to XML with sensible element names, converting CSV to XML with a template, using XSLT to transform XML into another shape, running XSLT from the command line and transforming XML to HTML with XSLT. The flattening step that precedes CSV and Excel output is in flattening repeating XML elements into rows, and the pipeline that loads the same records into a database is in turning XML feeds into relational tables.

For references, the XML rules the parsers apply are in the XML 1.0 specification, the CSV rules in RFC 4180, the JSON grammar in RFC 8259, and the YAML scalar resolution that causes the 1.1 surprises in the YAML 1.2.2 specification. To try a path on the sample before you write the mapping, use the XPath tester.

When a conversion is the wrong move

The data will be edited and sent back. If the recipient changes the spreadsheet and you must merge the changes into the XML, you need a keyed, reversible mapping and a validation step. Plan the round trip first, or keep the XML as the single source and hand out a read-only view.

The XML is document-like. Paragraphs with inline markup, footnotes and cross-references are a tree of mixed content, and every target in this guide flattens or reorders it. Keep it as XML, and render it with a stylesheet when people need to read it.

A schema or an exchange standard defines the shape. Invoices, healthcare messages and government filings come with a required structure. A generic conversion to a convenient shape is a different document, not a copy of the original; keep the original as the record.

The consumer can read XML. Many tools read XML directly: databases have XML functions, Excel has XML maps, DuckDB has an extension. Converting for the sake of converting adds a step that can go wrong, and a copy that can go stale. Read the source in place when you can.

The conversion is lossy and nobody has agreed to the loss. Dropping comments, merging empty and absent, joining repeated values into one cell, rounding prices: each is acceptable when it is a stated choice and a surprise when it is not. If you cannot name what is lost, do not convert yet.