Converting XML to CSV for Spreadsheet Users Safely

A person who needs the contents of an XML file in a spreadsheet does not want to hear about trees. They want rows and columns they can sort and filter. CSV is the right bridge, as long as you make the decisions that a flat file forces: what is a row, what happens to repeated children, and what the empty cells mean.

I will do it in Python, because the decisions are clearer in code, and then show what the shortcut does to the data.

Decide the row first

A CSV file is one table. An XML file with books, each with several authors, is at least two: books, and book-author pairs. You can either produce a table for each, or flatten to one table with a rule. For a spreadsheet user who wants to scan the file, one row per book is almost always right, and the repeated authors go into a single cell. If the user will filter by author, one row per author with the book columns repeated is better, but then a sum of prices counts a book once per author. Ask what they will do with it.

I take one row per book and join the authors. The choice goes in the script, so it is visible:

import csv, sys
from lxml import etree

root = etree.parse("library.xml").getroot()
w = csv.writer(sys.stdout, lineterminator="\n")
w.writerow(["id", "available", "title", "authors", "price", "currency", "note"])
for b in root.iterfind("book"):
    p = b.find("price")
    w.writerow([b.get("id"), b.get("available"), b.findtext("title"),
                "; ".join(a.text for a in b.iterfind("author")),
                p.text, p.get("currency"), b.findtext("note") or ""])

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,

Look at the quoting, which is the part that goes wrong when people build CSV by hand with string formatting. The title SQL Tuning, 2nd Edition contains a comma, so the writer wrapped it in quotes. Indexes "Explained" contains quote characters, so the whole field is quoted and each inner quote is doubled. That is the RFC 4180 rule (how CSV quoting works), and the standard library's csv module applies it. The R&D in the first note shows the other half of the job: the XML file wrote R&D, the parser decoded it, and the CSV holds the plain ampersand, because CSV has no concept of entities. Do not escape it again.

The separator inside the authors cell is a decision too. A semicolon followed by a space is readable and unlikely to appear in a name, but pick one that cannot appear in the data. If a name could contain it, use a different delimiter or produce the second table.

The missing and empty cells

Two of the three books have an empty note cell, for different reasons: book 2 has no note element and book 3 has an empty <note/>. In the CSV they look identical, and that is a lossy step to be aware of. For a spreadsheet it is usually correct, since nobody will read the difference, but if the file is going on to a database, say what the empty cell means in a note next to the file (handling optional XML elements and missing values).

The currency column

Look at the price column: 29.50, 44.00 and 250000. The third is Vietnamese dong and the first two are US dollars. A user who sums the column gets 250073.50, a number that means nothing. The currency is an attribute on the price in the XML, which is why I wrote it as its own column. Attributes that qualify a value, such as units, currencies and languages, should travel with the value. If the spreadsheet user wants a single number column, convert in the script with a rate table, and write down the date of the rates.

What the shortcut does

pandas has read_xml, and then to_csv finishes the job in two lines. It is worth knowing what it returns for this file, because it looks right.

import pandas as pd
df = pd.read_xml("library.xml", xpath="//book", parser="lxml")
print(df.to_string())

   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

Four problems, each silent. The first book shows author as Bo Chen only: it has two authors, and the reader kept the last one it saw, so Ana Lee is gone. There was no warning. The price column lost its currency because the currency is an attribute of a child, which the flat reader does not see, so the 250000 and the 29.5 are indistinguishable now. The price 29.50 became 29.5 and 44.00 became 44.0, because the column was parsed as a float (the .00 is a formatting loss, not an error, but a spreadsheet user who expects two decimals will notice). And available became a Python boolean, which to_csv writes as True and False.

None of that is a bug in pandas. read_xml is documented to take one level of the tree as rows and to read the child elements and attributes of each as columns. A document with repeated children and nested values is outside what it models. The reader is useful when the XML is already table-shaped (a list of records with simple fields), and the cost of using it elsewhere is losing data without an error. The check that catches it is the one I use everywhere: count the source. xmllint --xpath 'count(//author)' library.xml gives 4, and the CSV has three rows with four names in total if you count the joined cell, but the pandas frame has three names.

Opening the file in a spreadsheet

The CSV is correct, and a spreadsheet may still mangle it on open. Excel reads a UTF-8 file without a byte order mark as the system code page, so accented names come out garbled. Write the file with encoding="utf-8-sig" to add the BOM, which Excel recognizes. It also converts values that look like numbers, dates or gene names: a part number 1-2 becomes a date, and an identifier 00123 loses its zeros. The CSV articles cover the cause and the fixes (opening CSV files in Excel, Excel drops leading zeros when opening a CSV). If the recipient is going to use Excel anyway, skip CSV and give them a workbook with typed cells, which is the subject of converting XML to Excel with Python.

Checking the result

Three checks catch nearly every problem. The row count equals the record count in the XML (count(//book), which is 3). The number of authors across the file matches (count(//author) is 4; splitting the cell on the separator and summing should give the same). And reading the CSV back with a different tool, for example python -c "import csv; ..." or a spreadsheet, produces the same cells. The second one is the one that would have caught the pandas problem.

For larger files, the same script works as a stream: use iterparse(tag="book"), write each row as it is finished and clear() the element, so memory stays flat (parsing large XML files with iterparse).

Related reading

For the Python library used for writing, see the csv module documentation, and for the reader used by the shortcut, pandas.read_xml. The format rules are in RFC 4180. The bigger map of conversions is converting XML to JSON, CSV, YAML and Excel. Test the paths you will use on the sample with the XPath tester.

When CSV is the wrong target

The data is deeply nested. Three levels of repeating children do not fit in a row. Produce one CSV per level with a shared key, or use a database, or a format that nests (JSON).

The meaning is in the order or the mixed text. A document with paragraphs cannot be a table.

The values need types. A recipient who will sum, sort by date or filter by number is better served by an Excel workbook with typed cells than by text that their software has to guess about.

Many columns are mostly empty. A hundred optional fields produce a sparse table that nobody can read. Pick the columns that matter, or give them a long format with one row per field and value.