CSV is one of the most common formats for exchanging tabular data because of its simplicity and wide support across platforms. However, when working with structured applications, configurations, or hierarchical data, XML often becomes the preferred choice due to its ability to represent nested relationships and enforce stricter data validation.
In this guide, we’ll explore how to convert CSV files into XML using Spire.XLS for Python. You’ll learn how to convert CSV into both Excel XML format (SpreadsheetML) and standard XML for general use. We’ll also cover how to clean and preprocess real-world CSV files—handling issues like invalid headers, missing values, special characters, and nested fields—so that your XML output is always valid and structured correctly.
Table of Contents:
- Why Convert CSV to XML
- Prerequisites
- Convert CSV to Excel XML Format
- Convert CSV to Standard XML
- Handle Real-World CSV Challenges
- Wrap Up
- FAQs
Why Convert CSV to XML
So why should developers care about converting CSV to XML? Here are some real-world use cases:
- Enterprise Data Migration: Many enterprise applications like ERP or CRM systems require XML input for bulk data import.
- Configuration & Metadata: XML is often used to store structured metadata, while raw data may come in CSV form.
- Interoperability: Some APIs, especially in finance, healthcare, and government sectors, still rely heavily on XML payloads.
- Readable Reports: XML can represent hierarchical data, making it more descriptive compared to flat CSV files.
- Validation: XML can be validated against schemas (XSD), ensuring data integrity—something CSV cannot provide out-of-the-box.
CSV is great for simplicity . XML is great for structure . By converting between them, you get the best of both worlds.
Prerequisites
Before diving into the code, let’s ensure we have the right tools:
- Python 3.7+
- Spire.XLS for Python → A professional library that provides powerful Excel manipulation capabilities.
- Standard Python Libraries → xml.etree.ElementTree, csv, and re.
You can install Spire.XLS via pip (assuming you have Python and pip installed on your system):
pip install spire.xls
Additionally, make sure you have a CSV file ready to test. A simple one might look like this:
ID,FirstName,LastName,Age,Department,Salary
1,John,Doe,30,Sales,5000
2,Jane,Smith,28,Marketing,4500
3,Mike,Johnson,35,IT,6000
4,Emily,Brown,32,HR,4800
Convert CSV to Excel XML Format in Python
The first approach is to convert CSV into Excel-compatible XML . This format is sometimes called SpreadsheetML , introduced in Excel 2003. It allows CSV data to be transformed into a fully structured XML file that Excel can open directly.
With Spire.XLS, this process is straightforward:
from spire.xls import *
# Create a Workbook
workbook = Workbook()
# Load CSV into the first worksheet
workbook.LoadFromFile("input.csv", ",", 1, 1)
# Save as XML Spreadsheet format
workbook.SaveAsXml("output.xml")
# Dispose resources
workbook.Dispose()
How it Works
- LoadFromFile() – Reads the CSV file into the workbook.
- SaveAsXml() – Saves it as SpreadsheetML XML format.
This approach is best if your end goal is Excel compatibility rather than pure XML processing.
Output:
You may also like: Convert Excel to Excel-Compatible XML in Python
Convert CSV to Standard XML in Python
Sometimes, you need a custom XML format rather than Excel-compatible XML. For instance, you may need an XML structure like:
<Employees>
<Employee>
<ID>1</ID>
<FirstName>John</FirstName>
<LastName>Doe</LastName>
<Department>Sales</Department>
<Salary>5000</Salary>
</Employee>
</Employees>
Here’s how you can achieve that:
from spire.xls import *
import xml.etree.ElementTree as ET
# Step 1: Load CSV into workbook
workbook = Workbook()
workbook.LoadFromFile("input.csv", ",", 1, 1)
sheet = workbook.Worksheets[0]
# Step 2: Create root element
root = ET.Element("Root")
# Step 3: Loop through rows and build XML
# Assume first row is header
headers = []
for col in range(1, sheet.Columns.Count + 1):
cell_value = sheet.Range[1, col].Value
if not cell_value:
break
headers.append(cell_value)
# Add data rows
for row in range(2, sheet.Rows.Count + 1):
# Stop if row is empty
if not sheet.Range[row, 1].Value:
break
record = ET.SubElement(root, "Record")
for col, header in enumerate(headers, start=1):
cell_value = sheet.Range[row, col].Value
field = ET.SubElement(record, header)
field.text = cell_value if cell_value else ""
# Step 4: Save to XML file
tree = ET.ElementTree(root)
tree.write("output.xml", encoding="utf-8", xml_declaration=True)
# Cleanup
workbook.Dispose()
Explanation
- Step 1: Load the CSV into a worksheet.
- Step 2: Create a root <Root> element for the XML tree.
- Step 3: Extract headers from the first row and use them as XML tags.
- Step 4: Loop through rows and add <Record> elements with child fields.
- Step 5: Save the XML to a file.
This approach is flexible because you can customize the XML schema however you like (e.g., change <Record> to <Employee>).
Output:
You may also like: How to Convert XML to CSV in Python
Handle Real-World CSV Challenges
Converting a “perfect” CSV to XML is easy. But real-world CSVs are rarely perfect. Let’s discuss common issues developers face and how to fix them.
-
Headers with Invalid XML Names
- Problem: CSV headers like Employee ID or 123Name are invalid XML tags since XML element names cannot have spaces, start with numbers, or include special characters.
- Fix: Clean headers by replacing spaces with underscores (_) or prefixing numeric headers with a safe string.
-
Empty or Missing Values
- Problem: Real-world CSVs often have missing cells. When converted to XML, missing cells may create malformed structures.
- Fix: Replace blanks with placeholders (NULL, Unknown, or 0).
-
Special Characters in Data
- Problem: Characters like <, >, & inside CSV values can break XML if not escaped.
- Fix: Always escape XML entities properly (<, >, &).
-
Nested / Hierarchical Data in Flat CSV
- Problem: Some CSV cells contain multiple values or hierarchical information (e.g., lists of products) within a single field. Converting directly to XML will flatten the data, losing the intended hierarchy.
Example:
OrderID,Customer,Products
1001,John,"Laptop;Mouse;Keyboard"
Flattened XML (bad):
<Products>Laptop;Mouse;Keyboard</Products>
- Fix: Detect list-like or nested values and expand them into child XML elements or multiple rows, preserving the structure in XML.
Hierarchical XML (fixed):
<Products>
<Product>Laptop</Product>
<Product>Mouse</Product>
<Product>Keyboard</Product>
</Products>
Automating Fixes with clean_csv
Instead of cleaning data manually, you can automate preprocessing with the following helper function:
import csv
import re
def clean_csv(input_file, output_file, nested_columns=None, nested_delimiter=";"):
if nested_columns is None:
nested_columns = []
cleaned_rows = []
# Escape special characters for XML
def escape_xml(text):
return (text.replace("&", "&")
.replace("<", "<")
.replace(">", ">")
.replace('"', """)
.replace("'", "'"))
with open(input_file, "r", encoding="utf-8") as infile:
reader = csv.reader(infile)
headers = next(reader)
# Clean headers
cleaned_headers = []
for h in headers:
h = h.strip()
h = re.sub(r"\s+", "_", h) # Replace spaces with _
h = re.sub(r"[^a-zA-Z0-9_]", "", h) # Remove invalid chars
if re.match(r"^\d", h): # Cannot start with digit
h = "Field_" + h
cleaned_headers.append(h)
cleaned_rows.append(cleaned_headers)
# Read all rows first
raw_rows = []
for row in reader:
# Handle empty cells
row = [cell if cell.strip() != "" else "NULL" for cell in row]
raw_rows.append(row)
# Expand nested columns
if nested_columns:
expanded_rows = [cleaned_headers] # include header
for row in raw_rows:
row_variants = [row]
for col_name in nested_columns:
if col_name not in cleaned_headers:
continue
col_index = cleaned_headers.index(col_name)
temp = []
for variant in row_variants:
cell_value = variant[col_index]
# Only split on the nested delimiter, not on XML special chars
if nested_delimiter in cell_value:
items = [item.strip() for item in cell_value.split(nested_delimiter)]
for item in items:
new_variant = variant.copy()
new_variant[col_index] = item
temp.append(new_variant)
else:
temp.append(variant)
row_variants = temp
expanded_rows.extend(row_variants)
cleaned_rows = expanded_rows
else:
cleaned_rows.extend(raw_rows)
# Escape special characters after expansion
final_rows = [cleaned_rows[0]] # header
for row in cleaned_rows[1:]:
final_row = [escape_xml(cell) for cell in row]
final_rows.append(final_row)
# Write cleaned CSV
with open(output_file, "w", newline="", encoding="utf-8") as outfile:
writer = csv.writer(outfile)
writer.writerows(final_rows)
print(f"Cleaned CSV saved to {output_file}")
You can invoke the clean_csv function by passing the input CSV file path, output CSV file path, and optionally the columns to expand for nested values.
# File paths
input_file = r"C:\Users\Administrator\Desktop\input.csv"
output_file = r"C:\Users\Administrator\Desktop\cleaned_output.csv"
# Specify columns that may contain nested values
nested_columns = ["Products"] # you can add more, e.g., ["Products", "Reviews"]
# Call the clean_csv function
clean_csv(input_file, output_file, nested_columns=nested_columns, nested_delimiter=";")
This function ensures your CSV is safe and clean before converting it to XML.
It helps:
- Clean headers (valid XML names)
- Handle empty cells
- Expand nested values in any specified column(s)
- Escape special characters for XML
- Write a clean, UTF-8 CSV ready for XML conversion
Wrap Up
Converting CSV to XML in Python with Spire.XLS is both powerful and flexible. If you only need Excel-readable XML, saving directly to SpreadsheetML is the quickest option. If you need custom XML structures for APIs, migrations, or reporting, you can build standard XML using xml.etree.ElementTree.
Real-world CSVs are messy. They may have missing values, invalid headers, special characters, or nested lists. By integrating a preprocessing step (like the clean_csv function), you ensure that your XML output is both valid and meaningful.
Whether you’re preparing data for enterprise integration, creating structured reports, or exchanging information with legacy systems, this workflow gives you the best of both worlds: the simplicity of CSV and the structure of XML.
FAQs
Q1: Can I convert very large CSV files to XML?
Yes, but for very large files, you should use a streaming approach (processing rows one by one) to avoid memory issues.
Q2: Does Spire.XLS support direct conversion to XML without custom code?
Yes. Saving as SpreadsheetML is built-in. But for custom XML, you’ll need to write Python code as shown.
Q3: How do I handle special characters automatically?
Use the escape_xml helper or Python’s built-in xml.sax.saxutils.escape().
Q4: What if my CSV has nested data in multiple columns?
You can pass multiple column names into nested_columns when calling clean_csv.
Q5: Can I validate the generated XML?
Yes. After generating XML, validate it against an XSD schema if your system requires strict formatting.
Get a Free License
To fully experience the capabilities of Spire.XLS for Python without any evaluation limitations, you can request a free 30-day trial license.
