CSV to XML Converter

The XML 1.0 Name production lets a name begin with a letter, an underscore, or a colon. Digits, hyphens, and periods are legal only after the first character, and a space is never legal anywhere. That disqualifies a great many real spreadsheet headers: 2024 Revenue fails twice, First Name once, while unit-price and line.total are fine because the awkward characters are not in first position. Names beginning with the letters xml in any combination of cases are reserved by the spec as well.

Colons are worse than illegal, because they are legal and mean something else. A name containing a colon is read as a namespace-prefixed name, so the header order:id produces an element in a prefix called order that was never declared, and any namespace-aware parser rejects the whole document over it.

The failure that costs the most time is invisible in an editor. Excel writes UTF-8 CSV with a byte order mark, and that BOM lands inside the first header cell — so the first element name silently starts with U+FEFF, which is not a valid name-start character. Strip the BOM, and normalise headers to ASCII snake_case before converting; the text case converter handles the renaming in bulk. This tool uses your header text verbatim and wraps each record as <row> inside a <rows> root, so sanitising the names is your job, not its.

XML predefines exactly five entities: &, <, >, " and '. Everything else needs a numeric character reference or a DTD declaration, which is why an   pasted in from an HTML pipeline produces an undefined-entity error instead of a space.

Only two are unconditionally required in element content: & and <. A right angle bracket has to be escaped only where it would complete the sequence ]]>, and the quote characters matter only inside attribute values, where you must escape whichever quote delimits the value. Escaping all five every time is the safe default and what this converter does, so a cell reading Ben & Jerry's <2> comes out as Ben & Jerry's <2>.

One caveat on ': it is defined by XML and XHTML but was never part of HTML 4, so an apostrophe entity handed to a lenient HTML parser downstream can survive as visible text. Use ' when the eventual consumer might be an HTML toolchain. CDATA is not a general escape hatch either — a <![CDATA[ ]]> section cannot nest and cannot contain the three characters ]]>, so a field holding that sequence has to be split across two sections. And watch for double escaping: input that already contains & becomes &, which reaches the customer as a literal entity.

Output starts with <?xml version="1.0" encoding="UTF-8"?>. Parsers are required to support UTF-8 and UTF-16 and nothing else; with no declaration and no BOM, UTF-8 is assumed. The declaration converts nothing — it asserts what the bytes already are, and an incorrect assertion is worse than none.

This is where Excel exports break. The plain CSV export on Windows is Windows-1252, in which a curly apostrophe is the single byte 0x92, invalid as UTF-8 in any position. XML has no error recovery to fall back on: where an HTML parser would substitute a replacement character and carry on, a conformant XML processor must report a fatal error and stop. Re-export with Excel's "CSV UTF-8" option, or run iconv -f windows-1252 -t utf-8 first.

Control characters are a harder wall. The XML 1.0 Char production permits only tab (U+0009), line feed (U+000A) and carriage return (U+000D) below U+0020, and numeric character references are bound by that same production — so  is not a workaround, it is a second error. Legacy extracts that use U+001F as a unit separator, or carry a stray NUL from a fixed-width field, must be cleaned before conversion rather than escaped during it.

Line endings get rewritten too. Section 2.11 requires the processor to normalise CRLF and lone CR to a single line feed before the application sees any text, so a field containing \r\n reads back as \n. If those exact bytes matter, write them as 
, which survives because character references are resolved after line-end normalisation.

Attributes are tempting for short scalar columns: the document gets shorter and the values read like metadata. Three properties should make the decision instead. An attribute cannot repeat on the same element, cannot contain child structure, and is unordered in the Infoset — so any column that might later become a list or grow sub-fields has to be an element from the beginning. Promoting an attribute to an element afterwards breaks every XPath and every data binding written against it.

The rule that actually catches people is attribute-value normalisation. Under XML 1.0 §3.3.3, before the application sees a CDATA-typed attribute value the parser replaces every literal tab, carriage return, and line feed in it with a space. A multi-line address dropped into an attribute comes back as a single line, with no error raised anywhere along the way. A 
 character reference does survive, because references are appended to the normalised value literally — which means two spellings of the same newline behave differently in the same position. Element content has no equivalent rule.

A split that holds up: identifiers, units, currency codes, and enum-like flags become attributes; anything a human typed becomes an element. This converter emits child elements only, which is the choice that never has to be undone and the one that keeps free text intact.

CSV has no null. It has an empty field, and an empty field is all you get whether the value was deliberately blank, unknown, or not applicable. XML does not fix this on its own: <price></price> and <price/> are the same node to every parser, since the spec defines the empty-element tag as equivalent to a start tag immediately followed by an end tag. After conversion, an empty string and a missing value are indistinguishable.

Three honest options exist. Omit the element entirely when a value is absent, which makes absence detectable at the cost of records with differing children. Keep the empty element and document that empty means unknown. Or use xsi:nil="true", with the xsi prefix bound to http://www.w3.org/2001/XMLSchema-instance — the only one of the three that XML Schema itself understands.

Decide before writing the schema, because validation forces the issue: an empty <price></price> checked against xs:decimal is a datatype error, because the empty string is not a valid decimal, whereas a nilled element declared nillable="true" validates cleanly. Ragged input never reaches that stage here — the CSV parser reports a field-count mismatch and the conversion stops, which is the correct behaviour but does mean short rows have to be padded upstream. Duplicate headers, by contrast, pass silently: repeated sibling elements are perfectly well-formed XML, and only a schema with maxOccurs="1" will ever object.