Convert XML to CSV in Python

Converting XML data to CSV is a common task for data analysis and processing. Python offers multiple ways to perform this conversion. This guide explains two approaches: using the ElementTree module and the Pandas library, with detailed examples and comparisons.

1. Sample XML File

Here’s a sample XML file data.xml:

<data>
    <record>
        <id>1</id>
        <name>John Doe</name>
        <age>30</age>
        <city>New York</city>
    </record>
    <record>
        <id>2</id>
        <name>Jane Smith</name>
        <age>25</age>
        <city>Los Angeles</city>
    </record>
    <record>
        <id>3</id>
        <name>Emily Davis</name>
        <age>35</age>
        <city>Chicago</city>
    </record>
</data>

2. Using Pandas `read_xml()` to Convert XML to CSV

The Pandas library provides the read_xml() method to easily parse XML data into a DataFrame, which can then be exported as a CSV file.

Code Example

import pandas as pd

# Input and output file paths
input_file = 'data.xml'
output_file = 'data.csv'

# Read the XML file into a Pandas DataFrame
df = pd.read_xml(input_file)

# Export the DataFrame to a CSV file
df.to_csv(output_file, index=False)

# Output confirmation
print(f"Data exported to {output_file} successfully.")

Explanation:

• pd.read_xml(): Parses the XML file and loads it into a DataFrame.
• df.to_csv(): Exports the DataFrame to a CSV file.
• index=False: Prevents the DataFrame's index from being included in the CSV file.

3. Using ElementTree to Convert XML to CSV

The ElementTree module, part of Python’s standard library, allows you to parse XML files and write the data to a CSV file.

Code Example

import xml.etree.ElementTree as ET
import csv

# Input and output file paths
input_file = 'E:\\testing\\data\\student.xml'
output_file = 'E:\\testing\\data\\data.csv'

# Parse the XML file
tree = ET.parse(input_file)
root = tree.getroot()

# Extract data from XML
rows = []
headers = []

# Iterate through records
for record in root.findall('record'):
    row = {}
    for element in record:
        if element.tag not in headers:
            headers.append(element.tag)  # Collect headers dynamically
        row[element.tag] = element.text
    rows.append(row)

# Write data to CSV
with open(output_file, 'w', newline='', encoding='utf-8') as csvfile:  
    writer = csv.DictWriter(csvfile, fieldnames=headers)
    writer.writeheader()  # Write headers
    writer.writerows(rows)  # Write rows

print(f"Data exported to {output_file} successfully.")

Explanation:

• ET.parse(): Parses the XML file and creates a tree structure.
• root.findall('record'): Finds all the <record> elements in the XML file.
• csv.DictWriter: Writes the data into a CSV file with headers and rows.

4. Comparison of Methods

5. Which Method Should You Use?

Use Pandas `read_xml()` if:

• You need a quick and straightforward way to convert XML to CSV.
• The XML structure is flat and doesn't require advanced parsing.

Use ElementTree if:

• You need fine-grained control over XML parsing.
• The XML file has nested structures or requires custom handling.

Conclusion

Both methods are effective for converting XML to CSV in Python. Pandas is best for quick and simple tasks, while ElementTree offers flexibility for more complex requirements. Choose the approach that fits your specific use case.