Converting Excel to XML With a Python Script Safely

XML is old and still everywhere that data crosses an organization boundary: supplier feeds, regulatory filings, accounting imports, product catalogs. Someone keeps the data in a workbook, and the partner wants XML. The conversion is mechanical, with sharp edges at the names, the escaping, the characters and the structure that the receiving system expects. This post builds the conversion with the standard library and lxml, shows how to deal with each edge and how to validate the output against a schema. It is a spoke of converting Excel to CSV, JSON, XML and YAML.

Decide the shape first

There is no single XML form of a table. Agree one with the receiver before writing code. The usual shape:

<?xml version="1.0" encoding="UTF-8"?>
<orders>
  <order id="0007">
    <date>2025-03-14</date>
    <customer>Acme &amp; Sons</customer>
    <qty>2</qty>
  </order>
  <order id="0012">
    <date>2025-03-15</date>
    <customer>Globex</customer>
  </order>
</orders>

Decisions inside it: the name of the root and of the row element; which columns become attributes (identifiers, short flags) and which become child elements (everything else); how to represent an empty cell (omit the element, write an empty one, or use xsi:nil="true"); the format of dates and numbers; and whether a namespace applies. Attributes cannot repeat and have no structure, so use elements for anything that might grow. Missing versus empty is a real distinction in XML: an absent element and an empty element can mean different things to a validator.

If the receiver supplies an XSD or a sample, follow it exactly, including element order, which XSD sequences enforce.

Names

Element and attribute names follow strict rules. A name must start with a letter or underscore, may contain letters, digits, hyphens, underscores and periods, and cannot contain spaces. Names starting with xml (any case) are reserved. A column header such as Order No, 2025 Sales, Unit Price ($) or Café is not a valid name, or not a safe one. Clean headers with a function and check for collisions:

import re

def xml_name(h):
    s = re.sub(r"[^0-9A-Za-z_.-]+", "_", str(h).strip()).strip("_.-")
    if not s or not re.match(r"[A-Za-z_]", s):
        s = "_" + s
    if s.lower().startswith("xml"):
        s = "_" + s
    return s

names = [xml_name(c) for c in df.columns]
assert len(set(names)) == len(names), "duplicate element names"

Accented letters are legal in XML names, but many consumers prefer ASCII; the regular expression above removes them, which can collapse distinct headers, so keep the uniqueness assertion. Keep a mapping of original header to name for the documentation.

Build the document, do not concatenate

Never build XML by gluing strings. A customer called Acme & Sons or O'Brien <test> breaks the document, or worse, injects structure. A library escapes for you:

import xml.etree.ElementTree as ET
import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype={"Order No": str})
df["Order Date"] = pd.to_datetime(df["Order Date"]).dt.strftime("%Y-%m-%d")

root = ET.Element("orders")
for _, r in df.iterrows():
    order = ET.SubElement(root, "order", id=str(r["Order No"]))
    for col, name in (("Order Date", "date"), ("Customer", "customer"), ("Qty", "qty")):
        v = r[col]
        if pd.isna(v):
            continue                       # omit empty elements
        ET.SubElement(order, name).text = str(v)

ET.indent(root)                            # Python 3.9+
ET.ElementTree(root).write("orders.xml", encoding="utf-8", xml_declaration=True)

ElementTree escapes text and attribute values on output. ET.indent makes the file readable. xml_declaration=True writes the declaration line. If you need namespaces, comments, schema validation or pretty-printing control, use lxml, whose API is a superset:

from lxml import etree

root = etree.Element("orders", nsmap={None: "urn:example:orders:1"})

pandas has DataFrame.to_xml, which writes a table to XML in one call (df.to_xml("orders.xml", index=False, root_name="orders", row_name="order", parser="etree")). It is handy for flat tables. It uses lxml by default, and has options for attributes (attr_cols) and elements (elem_cols). For control over structure, build the tree yourself.

Characters and encoding

XML 1.0 forbids most control characters (everything below U+0020 except tab, line feed and carriage return), and the Unicode noncharacters. A cell that contains a stray vertical tab or a null from a bad copy will make the document invalid, and some libraries will write it anyway. Filter values:

ILLEGAL = re.compile("[\x00-\x08\x0b\x0c\x0e-\x1f\ud800-\udfff￾￿]")
clean = ILLEGAL.sub("", str(v))

Write UTF-8, declare it, and let the library handle entity references. Do not write a declaration of one encoding and bytes of another; that is the cause of a long family of parse errors (XML encoding declarations and wrong encodings). Leading and trailing whitespace in values is significant in element text, and trimming it is a decision to make: sheets often carry trailing spaces (trimming spaces and non breaking spaces). Line breaks inside cells become line feeds in the text; if they matter, keep them, and be aware that attribute values normalize them to spaces.

Values: write dates in ISO 8601, numbers with a dot as decimal separator and no thousands separators, booleans as true/false (the XSD forms) unless the receiver says otherwise. Do not write Excel's display text.

Large sheets

A DOM tree in memory is fine for tens of thousands of rows. For more, write incrementally. lxml's xmlfile writes elements as you go with constant memory:

from lxml import etree

with etree.xmlfile("orders.xml", encoding="utf-8") as xf:
    xf.write_declaration()
    with xf.element("orders"):
        for row in rows():                        # a generator from openpyxl read-only mode
            el = etree.Element("order", id=row["order_no"])
            etree.SubElement(el, "customer").text = row["customer"]
            xf.write(el)

Feed it rows from an openpyxl read-only iterator (converting a huge Excel file to CSV or Parquet describes the reading side). Each element is written and released.

Validate against a schema

An XSD turns the agreed structure into an automatic check:

from lxml import etree

schema = etree.XMLSchema(etree.parse("orders.xsd"))
doc = etree.parse("orders.xml")
if not schema.validate(doc):
    for err in schema.error_log:
        print(err.line, err.message)
    raise SystemExit("invalid output")

Validation catches wrong element order, a missing required element, a value that does not fit its type (a date in the wrong form, a negative quantity where a positive integer is required). If no XSD exists, write a small one; the exercise forces agreement on types and optionality. Well-formedness (that the document parses) is the minimum, and validity (that it matches the schema) is the real test. An XML file that parses but fails the receiver's schema is the usual cause of a rejected feed (well-formed versus valid XML).

Verify the content

Count: the number of row elements equals the sheet's data rows. Total: parse the XML back, sum qty, and compare with Excel's sum. Check that an identifier with leading zeros kept them, that special characters survived (Acme & Sons, an accented name, a quote), that dates are in the agreed form and that empty cells appear the way the receiver expects. Open the file in a browser or an XML editor: a parse error shows at once. For queries against the result, XPath tests are quick (XPath queries that cover most real needs).

Pitfalls

• Names built from headers that begin with a digit or contain spaces.

• String concatenation instead of a library, with unescaped ampersands.

• Control characters copied from other systems.

• Mixed encodings between declaration and bytes.

• Numbers as Excel displayed them, with thousands separators or currency symbols.

• Floating-point artifacts, 0.30000000000000004; round before writing.

• Empty versus absent, which validators treat differently.

• Order of elements, which XSD sequences enforce.

• Very long text, beyond limits the receiver sets.

When XML is not the right target

The consumer accepts JSON or CSV. Those are simpler, smaller and easier to produce (converting Excel to JSON with Python for clean output).

The structure is deeply nested. A flat sheet flattens badly into nested XML; model the hierarchy first.

No schema exists and nobody can say what the receiver needs. Find out; XML without an agreed structure is just text with angle brackets.

You need to preserve formatting. XML here carries data only. For formatted documents consider other formats.

See the Python documentation for xml.etree.ElementTree, the lxml tutorial and the W3C XML 1.0 specification for the rules on names and characters. To format a snippet, use the XML formatter.