CSV to XML

Why a spreadsheet header often cannot be an element name

This comes up whenever something on the receiving end predates JSON — a bank format, a customs or tax filing, an older ERP import, an industry schema. You have the data in a spreadsheet, the system wants XML, and the gap between the two is smaller than it looks but has one sharp edge in it.

The edge is naming. A CSV header is whatever someone typed: First Name, Cost (£), 3rd Party. An XML element name cannot contain a space or most punctuation, cannot begin with a digit, and cannot begin with the letters xml in any case, which the XML specification reserves. Emit those unchanged and you get a file that no parser will load, usually discovered by whoever tries to import it rather than by you.

So headers are rewritten where they have to be, and — this is the part that matters more than the rewriting — you are told which ones changed and what they became. A column silently renamed between what you pasted and what you sent is the kind of thing that surfaces a week later as a mapping that does not match.

The other half is escaping. An ampersand in a company name, an angle bracket in a note, a quote inside a value: each of those turns a document invalid if written straight through. Everything is escaped on the way out, and the & is replaced before < and >, because doing it the other way round escapes your own output twice and produces < in the file.

You can write fields as child elements or as attributes on the row. Neither is more correct — XML has argued about it since the beginning. Attributes give a shorter document, elements handle multi-line values and can be extended later, which is why they are the default here.

How to use it

1. Paste or upload the CSV – Commas, semicolons, tabs and pipes are all detected, so a European export does not need converting first.
2. Name the root and row elements – Defaults are rows and row. Whatever you type is checked, and fixed if it would not be a legal name.
3. Choose elements or attributes – Child elements by default. Attributes make a more compact file, at the cost of not being able to hold a value with a newline in it.
4. Read the grey notes – They name every header that had to be rewritten. If a column matters to whoever receives this, check it here before sending.
5. Copy or download – The output is well-formed, with a declaration on the front so encoding is not left to chance.

Paste the result into the XML Validator before you send it anywhere. It takes two seconds and it is the difference between finding a problem now and having it bounced back by a system whose error message is a stack trace. If the receiving end sent you a sample of what it expects, open that in the XML to Table view and compare element names against yours.

Four headers, three of them illegal

The header row below is completely ordinary for a spreadsheet and only one of its four names can be used as-is. Note also the ampersand in the second row, which would break the document if it were written straight through.

First Name,3rd Party,Cost (£),msisdn
Rosalind,yes,12.50,447700900112
Sigrid,"Marks & Spencer",8.00,447700900187

# three of the four headers are not legal
# XML names, and row two has an ampersand

<rows>
  <row>
    <First_Name>Rosalind</First_Name>
    <_3rd_Party>yes</_3rd_Party>
    <Cost____>12.50</Cost____>
    <msisdn>447700900112</msisdn>
  </row>
  …
    <_3rd_Party>Marks &amp; Spencer</_3rd_Party>
</rows>

# a space becomes _, a leading digit gets a
# prefix, and & is escaped or nothing parses

Where this actually gets used

Feeding a system that only speaks XML

Payment initiation, regulatory returns, EDI-adjacent formats and a great deal of healthcare tooling all take XML and nothing else. The data usually starts life in a spreadsheet regardless, so this conversion sits between the two more often than anybody plans for.

Building a fixture for a test

Writing forty XML records by hand is miserable and error-prone. Typing them as rows and converting takes a minute, and the result is well-formed by construction rather than by careful proofreading. Once you have it, the XPath Tester is a quick way to confirm the selectors your code uses actually hit it.

Round-tripping to check an assumption

Convert to XML, then bring it back through XML to CSV. If a value comes back different, the interesting question is which step changed it — and it is almost always quoting or an encoding assumption rather than the conversion itself.

What it takes care of

• Headers that are not legal element names rewritten, and every rewrite reported rather than done quietly.
• A header beginning with a digit gets a prefix rather than losing the digit — otherwise 1st and 2nd would collapse into the same column.
• Two headers that sanitise to the same name kept distinct, so Cost (£) and Cost (€) do not become one column.
• Every value escaped, with & handled first so nothing is escaped twice.
• Quoted CSV fields containing the delimiter, newlines or doubled quotes read correctly rather than split.
• Comma, semicolon, tab and pipe delimiters detected from the content.
• Root and row names sanitised too, since a name typed into a box is no more trustworthy than one read from a file.
• An empty value written as a self-closing tag, which reads back as an empty string rather than as whitespace.

Questions worth answering

Why was my column renamed?

Because the name would not have been legal. XML element names cannot contain spaces or most punctuation, cannot start with a digit, and cannot start with xml in any capitalisation — that last one is reserved by the specification for its own use, which surprises people whose data genuinely has a column called XMLVersion. Every rename is listed under the output. If a particular name matters to whoever receives the file, rename the column in your CSV first so the choice is yours rather than the tool's.

Should I use elements or attributes?

Elements unless you have a reason not to. Attributes give a noticeably smaller file and read well for short identifiers, but an attribute value cannot sensibly contain a newline, and you cannot add structure underneath one later without restructuring the document. If a schema on the receiving end dictates the shape, follow it — that is the only version of this argument with a correct answer.

Are numbers still numbers?

XML has no types of its own, so every value is character data. 00071 keeps its leading zeros and a 19-digit identifier keeps all nineteen digits, which is one genuine advantage XML has over pushing the same data through a spreadsheet. What a value means is decided by the schema on the other side, not by the document.

What happens to a semicolon-delimited file?

It is detected and handled. Semicolons are the norm wherever the comma is a decimal separator, so a file exported from Excel in much of Europe is semicolon-delimited and looks like a single broken column to anything that assumes commas. Tabs and pipes are detected too. If detection gets it wrong, the CSV Formatter will normalise the file first.

Does the output have a namespace?

No, and that is deliberate. A namespace only means something when it is the one the receiving schema expects, and inventing a plausible-looking URI would produce a document that looks more correct than it is. Add the declaration to the root element yourself if the target requires it — and if you then need to query the result, be aware that an unprefixed XPath expression stops matching the moment a namespace exists, which the XPath Tester explains at more length.

Related tools

Further reading

• RFC 4180 – The closest thing CSV has to a specification, including the quoting rules this page follows.
• XML introduction (MDN) – A short refresher on well-formedness, and the difference between elements and attributes.