XML is a widely used format for storing structured data, but it’s not ideal for analysis or tabular tools like Excel. Converting XML to CSV allows you to work with the data in a simpler, flat format that’s compatible with spreadsheets and data analysis libraries. By converting XML to CSV, you can easily import data into Excel, perform calculations, or feed it into Python data analysis tools like Pandas.

This approach also helps standardize complex hierarchical data into a format that is easier to read, manipulate, and share across different applications. In this tutorial, we’ll explore how to efficiently transform XML files into CSV using Spire.XLS for Python.

Table of Contents:

• Setting Up Spire.XLS for Python
• Understanding XML Data Structures
• How XML Data Is Extracted and Converted
• Basic Example: Convert XML to CSV with Python
• Advanced Techniques
  • Exporting Only Specific Elements
  • Including XML Attributes in CSV
  • Handling Nested XML Structures
• Troubleshooting and Common Issues
• Conclusion
• FAQs

Setting Up Spire.XLS for Python

Before we can start, we need to install the Spire.XLS library. The package is available on PyPI , so installation is straightforward. Run the following command:

pip install spire.xls

Once installed, you can import the required classes:

from spire.xls import *
from spire.xls.common import *

Spire.XLS provides the Workbook and Worksheet objects for managing Excel-like files. You’ll use them to create new CSV files and populate them with your XML data.

Understanding XML Data Structures

XML files are organized in a tree-like hierarchy, where elements (or nodes) are enclosed in tags. Each element can contain text, attributes, or even other child elements.

For example, consider this Books.xml:

<catalog>
  <book isbn="9780451524935">
    <title>The Catcher in the Rye</title>
    <author>J.D. Salinger</author>
    <genre>Fiction</genre>
    <reviews>
      <review>Excellent coming-of-age story</review>
      <review>A true classic</review>
    </reviews>
  </book>
  <book isbn="9780439023528">
    <title>The Hunger Games</title>
    <author>Suzanne Collins</author>
    <genre>Adventure</genre>
  </book>
</catalog>

• Root node : <catalog> is the top-level container.
• Child nodes : Each <book> is a child of <catalog>.
• Elements : <title>, <author>, and <genre> are elements inside each <book>.
• Attributes : The isbn in <book isbn="..."> is an attribute attached to the book element.
• Nested elements : The <reviews> node contains multiple <review> sub-nodes.

Challenges when converting XML to CSV:

• Hierarchical data – XML allows nesting (e.g., <reviews> inside <book>), while CSV is flat.
• Attributes vs. elements – Data may be stored as an attribute (isbn) or as a tag (title).
• Optional fields – Not all <book> elements may contain the same tags, which can lead to missing values in the CSV.

A robust converter must be able to handle these differences and map hierarchical XML into a flat, tabular CSV format.

How XML Data Is Extracted and Converted

To load and parse an XML file in Python, you can use the built-in xml.etree.ElementTree library. This library lets you navigate the XML tree, retrieve elements, and access attributes. For example:

import xml.etree.ElementTree as ET

# Load the XML file
tree = ET.parse("Books.xml")
root = tree.getroot()

# Iterate through elements
for book in root.findall("book"):
    title = book.findtext("title", "")
    author = book.findtext("author", "")
    isbn = book.attrib.get("isbn", "")

After extracting the XML data, the next step is to map it to a tabular structure . Using Spire.XLS for Python , you can create a workbook, write the extracted values into worksheet cells, and finally export the worksheet as a CSV file for easy analysis.

General Steps to Convert XML to CSV in Python

1. Use xml.etree.ElementTree to load and retrieve data from the XML file.
2. Create a Workbook object using Spire.XLS.
3. Add a worksheet with Workbook.Worksheets.Add() .
4. Write extracted XML data into the worksheet using Worksheet.SetValue() .
5. Save the worksheet to a CSV file using Worksheet.SaveToFile() .

Basic Example: Convert XML to CSV with Python

Let’s start with a basic XML-to-CSV conversion. This example automatically generates headers by inspecting the first <book> element and then exports all child nodes into CSV.

from spire.xls import *
from spire.xls.common import *
import xml.etree.ElementTree as ET

# Create a Workbook object
workbook = Workbook()

# Remove default worksheets
workbook.Worksheets.Clear()

# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("Books")

# Load an XML file
xml_tree = ET.parse("C:\\Users\\Administrator\\Desktop\\Books.xml")

# Get the root element of the XML tree
xml_root = xml_tree.getroot()

# Get the first the "book" element
first_book = xml_root.find("book")

# Extract header information and convert it into a list
header = list(first_book.iter())[1:]  

# Write header to Excel
for col_index, header_node in enumerate(header, start=1):
    header_text = header_node.tag
    worksheet.SetValue(1, col_index, header_text)

# Write other data to Excel by iterating over each book element and each data node within it
row_index = 2
for book in xml_root.iter("book"):
    for col_index, data_node in enumerate(list(book.iter())[1:], start=1):  
        value = data_node.text
        header_text = list(header[col_index - 1].iter())[0].tag
        worksheet.SetValue(row_index, col_index, value)
    row_index += 1

# Save the document to an Excel file
worksheet.SaveToFile("output/XmlToCsv.csv", ",", Encoding.get_UTF8())

# Dispose resources
workbook.Dispose()

This script works well for simple, flat XML structures. It automatically generates headers (title, author, genre, price) and fills each row with corresponding values.

Output:

You might also be interested in: Convert XML to Excel and PDF in Python

Advanced Techniques

The basic script works in many cases, but XML often isn’t so simple. Let’s go through some advanced techniques to handle real-world scenarios.

Exporting Only Specific Elements

Sometimes your XML has more data than you need. Maybe you only want to export title and author, ignoring everything else.

from spire.xls import *
from spire.xls.common import *
import xml.etree.ElementTree as ET

# Create a Workbook object
workbook = Workbook()

# Remove default worksheets
workbook.Worksheets.Clear()

# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("Books")

# Load the XML file
xml_tree = ET.parse(r"C:\Users\Administrator\Desktop\Books.xml")
xml_root = xml_tree.getroot()

# Define the elements you want to export
selected_elements = ["title", "author"]

# Write header
for col_index, tag in enumerate(selected_elements, start=1):
    worksheet.SetValue(1, col_index, tag)

# Write data
row_index = 2
for book in xml_root.iter("book"):
    for col_index, tag in enumerate(selected_elements, start=1):
        # Use findtext to handle missing values safely
        worksheet.SetValue(row_index, col_index, book.findtext(tag, ""))
    row_index += 1

# Save the document to a CSV file
worksheet.SaveToFile("output/XmlToCsv_Selected.csv", ",", Encoding.get_UTF8())

# Dispose resources
workbook.Dispose()

This approach ensures your CSV only contains the columns you care about.

Output:

Including XML Attributes in CSV

What if your XML contains important data stored as attributes, such as isbn? You can easily include them:

from spire.xls import *
from spire.xls.common import *
import xml.etree.ElementTree as ET

# Create a Workbook object
workbook = Workbook()

# Remove default worksheets
workbook.Worksheets.Clear()

# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("Books")

# Load an XML file
xml_tree = ET.parse(r"C:\Users\Administrator\Desktop\Books.xml")

# Get the root element of the XML tree
xml_root = xml_tree.getroot()

# Get the first the "book" element
first_book = xml_root.find("book")

# Extract header information (child nodes)
header = list(first_book.iter())[1:]

# Write header to Excel
worksheet.SetValue(1, 1, "isbn")   # <-- Add ISBN column first
for col_index, header_node in enumerate(header, start=2):  # start at 2 now
    header_text = header_node.tag
    worksheet.SetValue(1, col_index, header_text)

# Write data
row_index = 2
for book in xml_root.iter("book"):
    # Write isbn as text
    isbn_value = book.attrib.get("isbn", "")
    worksheet.Range[row_index, 1].Text = isbn_value

    # Then write other fields
    for col_index, data_node in enumerate(list(book.iter())[1:], start=2):
        value = data_node.text
        worksheet.SetValue(row_index, col_index, value)

    row_index += 1

# Format the whole ISBN column as text to prevent scientific notation
last_row = row_index - 1   
isbn_range = f"A2:A{last_row}"
worksheet.Range[isbn_range].NumberFormat = "@"

# Save the document to an Excel file (CSV format)
worksheet.SaveToFile("output/XmlToCsv_WithAttributes.csv", ",", Encoding.get_UTF8())

# Dispose resources
workbook.Dispose()

Here we explicitly create an ISBN column, extract it from each <book>’s attributes, and format it as text to prevent Excel from displaying it in scientific notation.

Output:

Handling Nested XML Structures

Nested nodes are common. Suppose your XML looks like this:

<catalog>
  <book>
    <title>1984</title>
    <author>George Orwell</author>
    <reviews>
      <review>Excellent read!</review>
      <review>Still relevant today.</review>
    </reviews>
  </book>
</catalog>

Here’s how to collapse multiple <review> entries into a single CSV column:

from spire.xls import *
from spire.xls.common import *
import xml.etree.ElementTree as ET

# Create a Workbook object
workbook = Workbook()

# Remove default worksheets
workbook.Worksheets.Clear()

# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("Books")

# Load an XML file
xml_tree = ET.parse(r"C:\Users\Administrator\Desktop\Nested.xml")
xml_root = xml_tree.getroot()

# Get the first <book> element
first_book = xml_root.find("book")

# Collect headers (auto-detected)
header = []
for child in first_book:
    if child.tag == "reviews":  
        header.append("reviews")   # collapse nested <review> into one column
    else:
        header.append(child.tag)

# Write header row
for col_index, header_text in enumerate(header, start=1):
    worksheet.SetValue(1, col_index, header_text)

# Write data rows
row_index = 2
for book in xml_root.iter("book"):
    col_index = 1
    for child in book:
        if child.tag == "reviews":
            # Join all <review> texts into a single cell
            reviews = [r.text for r in child.findall("review") if r.text]
            worksheet.SetValue(row_index, col_index, "; ".join(reviews))
        else:
            worksheet.SetValue(row_index, col_index, child.text if child.text else "")
        col_index += 1
    row_index += 1

# Save to CSV
worksheet.SaveToFile("output/XmlToCsv_WithReviews.csv", ",", Encoding.get_UTF8())

# Dispose resources
workbook.Dispose()

Output:

Spire.XLS not only supports importing data from standard XML files into Excel or CSV, but also allows converting OpenXML (Microsoft's XML-based file format) to Excel. If you're interested, check out this tutorial: How to Convert Excel to OpenXML and OpenXML to Excel in Python.

Troubleshooting and Common Issues

Even with a well-structured script, you may encounter some common issues when converting XML to CSV:

1. Scientific notation in Excel
   • Problem: Long numeric strings like ISBNs may appear as 9.78045E+12 instead of the full number.
   • Solution: Format the column as text before saving, for example:

   worksheet.Range["A2:A{last_row}"].NumberFormat = "@"
   

2. Missing values causing errors

   • Problem: Some <book> elements may lack optional fields (e.g., <genre>). Attempting to access .text directly can cause errors.
   • Solution: Use findtext(tag, "") to safely provide a default empty string.

3. Incomplete or unexpected headers

   • Problem: If you generate headers from only the first <book>, you might miss fields that appear later in the XML.
   • Solution: Scan multiple elements (or the entire dataset) to build a complete header list before writing data.

4. Encoding issues

   • Problem: Special characters (such as accents or symbols) may not display correctly in the CSV.
   • Solution: Always save with UTF-8 encoding:

   worksheet.SaveToFile("output.csv",",", Encoding.get_UTF8())
   

Conclusion

Converting XML to CSV in Python doesn’t have to be painful. With Spire.XLS for Python, you can automate much of the process, including header generation, handling attributes, and flattening nested nodes. Whether you’re exporting only a few fields, working with complex hierarchies, or cleaning up messy XML, Spire.XLS gives you the flexibility to handle it.

By integrating these approaches into your workflow, you can turn structured XML datasets into clean, analysis-ready CSV files in just a few lines of code.

FAQs

Q1: Can I export directly to Excel (.xlsx) instead of CSV?

Yes. Simply use workbook.SaveToFile("output.xlsx", ExcelVersion.Version2016).

Q2: How do I handle very large XML files?

Use Python’s iterparse() from xml.etree.ElementTree to stream large files instead of loading them fully into memory.

Q3: What if some <book> elements contain additional tags?

You can enhance the header-building step to scan all <book> nodes and collect unique tags dynamically.

Q4: Can I customize the CSV delimiter (e.g., use ; instead of ,)?

Yes. When calling SaveToFile(), replace the delimiter argument:

worksheet.SaveToFile("output.csv", ";", Encoding.get_UTF8())

Q5: How do I export nested XML structures (e.g., multiple <review> nodes)?

Flatten them into a single cell by joining values. For example:

reviews = [r.text for r in book.find("reviews").findall("review")]
worksheet.SetValue(row_index, col_index, "; ".join(reviews))

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.

JSON (JavaScript Object Notation) is the most widely used format for structured data in web applications, APIs, and configuration files. CSV (Comma-Separated Values) is a popular format for data analysis, reporting, and seamless integration with Excel. Converting JSON to CSV is a common requirement for developers, data engineers, and analysts who need to transform hierarchical JSON data into a tabular format for easier processing, visualization, and automation.

This step-by-step guide demonstrates how to convert JSON to CSV using Python, including techniques for handling flat and nested JSON data, and optimizing performance for large JSON datasets. By following this tutorial, you can streamline your data workflows and efficiently integrate JSON data into Excel or other tabular systems.

What You Will Learn

• Why Convert JSON to CSV
• Understanding JSON Structures
• Python JSON to CSV Converter – Installation
• Step-by-Step JSON to CSV Conversion in Python
  • Handling Flat JSON Data
  • Handling Nested JSON with Dictionaries and Arrays
• Performance Tips for Exporting Large or Complex JSON Files to CSV
• Conclusion
• FAQs

Why Convert JSON to CSV?

Although JSON is ideal for hierarchical and complex data, CSV offers several practical advantages:

• Spreadsheet Compatibility: CSV files can be opened in Excel, Google Sheets, and other BI tools.
• Ease of Analysis: Tabular data is easier to filter, sort, summarize, and visualize.
• Data Pipeline Integration: Many ETL workflows and reporting systems rely on CSV for seamless data integration.

Real-World Use Cases:

• API Data Extraction: Transform JSON responses from web APIs into CSV for analysis or reporting.
• Reporting Automation: Convert application or system logs into CSV for automated dashboards or scheduled reports.
• Data Analytics: Prepare hierarchical JSON data for Excel, Google Sheets, or BI tools like Tableau to perform pivot tables and visualizations.
• ETL Pipelines: Flatten and export JSON from databases or log files into CSV for batch processing or integration into data warehouses.

Converting JSON files to CSV format bridges the gap between structured storage and table-based analytics, making it a common requirement in reporting, data migration, and analytics pipelines.

Understanding JSON Structures

Before implementing JSON to CSV conversion in Python, it is important to understand the two common structures of JSON data:

• Flat JSON: Each object contains simple key-value pairs.

[
    {"name": "Alice", "age": 28, "city": "New York"},
    {"name": "Bob", "age": 34, "city": "Los Angeles"}
]

• Nested JSON: Objects contain nested dictionaries or arrays.

[
    {
        "name": "Alice",
        "age": 28,
        "contacts": {"email": "alice@example.com", "phone": "123-456"}
    }
]

Flat JSON can be directly mapped to CSV columns, while nested JSON requires flattening to ensure proper tabular representation.

Python JSON to CSV Converter - Installation

To export JSON to CSV in Python, you can use Spire.XLS for Python, a spreadsheet library that enables Python developers to create, read, manipulate, and export spreadsheet files directly from Python. It supports common formats like .xls, .xlsx, .csv, and .ods.

Installation

You can install the library from PyPI via pip:

pip install spire.xls

If you need assistance with the installation, refer to this tutorial: How to Install Spire.XLS for Python on Windows.

Once installed, you can import it into your Python scripts:

from spire.xls import *

This setup allows seamless JSON-to-CSV conversion with complete control over the workbook structure and output format.

Step-by-Step JSON to CSV Conversion in Python

Converting JSON to CSV involves four main steps: loading JSON data, creating a workbook, writing headers and rows, and exporting the final file as CSV. Below, we’ll go through the process for both flat JSON and nested JSON.

Handling Flat JSON Data

Step 1: Load JSON and Create Workbook

First, load your JSON file into Python and create a new workbook where the data will be written.

import json
from spire.xls import *

# Load JSON data
with open('data.json') as f:
    data = json.load(f)

# Create workbook and worksheet
workbook = Workbook()
sheet = workbook.Worksheets[0]

Explanation:

• json.load parses your JSON file into Python objects (lists and dictionaries).
• Workbook() creates a new Excel workbook in memory.
• workbook.Worksheets[0] accesses the first worksheet where data will be written.

Step 2: Write Headers Dynamically

Next, generate column headers from the JSON keys. This ensures that the CSV reflects the structure of your data.

# Extract headers from JSON keys
headers = list(data[0].keys())

# Write headers to the first row
for col, header in enumerate(headers, start=1):
    sheet.Range[1, col].Value = header

Explanation:

• list(data[0].keys()) retrieves all top-level JSON keys.
• sheet.Range[1, col].Value writes headers in the first row, starting from column 1.

Step 3: Write Data Rows

After headers are set, populate the worksheet row by row with values from each JSON object.

# Populate values from each JSON object to the subsequent rows
for row_index, item in enumerate(data, start=2):
    for col_index, key in enumerate(headers, start=1):
        value = item.get(key, "")
        sheet.Range[row_index, col_index].Value = str(value) if value is not None else ""

Explanation:

• Loop starts from row 2 because row 1 is reserved for headers.
• Each JSON object is mapped to a row, and each key is mapped to a column.

Step 4: Save the Worksheet as CSV

Finally, save the worksheet as a CSV file and clean up resources.

# Save the worksheet as a CSV file
sheet.SaveToFile("output.csv", ",", Encoding.get_UTF8())
workbook.Dispose()

Resulting CSV (output.csv):

This example uses Python’s built-in json module to parse JSON data. For more details on its functions and usage, refer to the Python json module documentation.

If you also want to implement JSON to Excel conversion, see our guide on converting JSON to/from Excel in Python.

Handling Nested JSON with Dictionaries and Arrays

When JSON objects contain nested dictionaries or arrays, direct CSV export is not possible because CSV is a flat format. To ensure compatibility, nested fields must be flattened to preserve all data in a readable CSV format.

Suppose you have the following JSON file (nested.json) with nested data:

[
    {
        "Name": "Alice",
        "Age": 28,
        "City": "New York",
        "Contacts": {"Email": "alice@example.com", "Phone": "123-456"},
        "Skills": ["Python", "Excel", "SQL"]
    },
    {
        "Name": "Bob",
        "Age": 34,
        "City": "Los Angeles",
        "Contacts": {"Email": "bob@example.com", "Phone": "987-654"},
        "Skills": ["Java", "CSV", "JSON"]
    }
]

This JSON contains:

• Flat fields: Name, Age, City
• Nested dictionary: Contacts
• Array: Skills

To export all fields to CSV, nested dictionaries need to be flattened, and arrays need to be joined into semicolon-separated strings. The following steps show you how to achieve this.

Step 1: Flatten Nested JSON Data

Flattening nested JSON with dictionaries and arrays involves converting nested dictionaries into dot-notation keys and joining array values into a single string. The following helper function performs this flattening:

import json
from spire.xls import *

# ---------------------------
# Step 1: Flatten nested JSON
# ---------------------------
def flatten_json(item):
    flat = {}
    for key, value in item.items():
        if isinstance(value, dict):
            for sub_key, sub_value in value.items():
                flat[f"{key}.{sub_key}"] = sub_value
        elif isinstance(value, list):
            flat[key] = "; ".join(map(str, value))
        else:
            flat[key] = value
    return flat

with open('nested.json') as f:
    data = json.load(f)

flat_data = [flatten_json(item) for item in data]

Explanation:

• flatten_json() checks each field in the JSON object.
• If the value is a dictionary, its keys are prefixed with the parent key (dot notation).
• If the value is a list, all elements are joined into one string separated by semicolons.
• Other values are kept as they are.
• Finally, the original nested JSON is transformed into flat_data, which can now be exported using the same workflow as flat JSON.

Step 2: Export Flattened Data to CSV

After flattening, the export process is the same as flat JSON: create a workbook, generate headers, write rows, and save as CSV.

# ------------------------------------------------
# Step 2: Export flattened data to CSV
# (create workbook, write headers, populate rows, save)
# ------------------------------------------------
workbook = Workbook()
sheet = workbook.Worksheets[0]

# Generate headers from flattened JSON keys
headers = list(flat_data[0].keys())
for col, header in enumerate(headers, start=1):
    sheet.Range[1, col].Value = header

# Populate rows from flat_data
for row_index, item in enumerate(flat_data, start=2):
    for col_index, key in enumerate(headers, start=1):
        value = item.get(key, "")
        sheet.Range[row_index, col_index].Value = str(value) if value is not None else ""

# Save the worksheet as CSV (comma delimiter, UTF-8)
sheet.SaveToFile("output.csv", ",", Encoding.get_UTF8())

# Clean up resources
workbook.Dispose()

Explanation:

• Headers and rows are generated from flat_data (instead of the original data).
• This ensures nested fields (Contacts.Email, Contacts.Phone) and arrays (Skills) are properly included in the CSV.

Resulting CSV (output.csv):

Performance Tips for Exporting Large or Complex JSON Files to CSV

When working with large or complex JSON files, applying a few practical strategies can help optimize memory usage, improve performance, and ensure your CSV output remains clean and accurate.

• For very large datasets, process JSON in chunks to avoid memory issues.
• When arrays are very long, consider splitting into multiple rows instead of joining with semicolons.
• Maintain consistent JSON keys for cleaner CSV output.
• Test the CSV output in Excel to verify formatting, especially for dates and numeric values.

Conclusion

Converting JSON to CSV in Python is essential for developers and data engineers. With Spire.XLS for Python, you can:

• Convert flat and nested JSON into structured CSV.
• Maintain consistent headers and data formatting.
• Handle large datasets efficiently.
• Export directly to CSV or Excel without additional dependencies.

By following this guide, you can seamlessly transform JSON into CSV for reporting, analytics, and integration.

FAQs

Q1: Can Spire.XLS for Python convert nested JSON objects to CSV?

A1: Yes. You can flatten nested JSON objects, including dictionaries and arrays, and export them as readable CSV columns with dynamic headers. This ensures all hierarchical data is preserved in a tabular format.

Q2: How do I install Spire.XLS for Python?

A2: You can install it via pip with the command:

pip install spire.xls

Then import it into your Python script using:

from spire.xls import

This setup enables seamless JSON-to-CSV conversion in Python.

Q3: Can I join array elements from JSON into a single CSV cell?

A3: Yes. You can join array elements from JSON into a single CSV cell using a delimiter like ;. This keeps multiple values readable and consistent in your exported CSV file.

Q4: How can I optimize performance when converting large JSON files to CSV in Python?

A4: To handle large JSON files efficiently: flatten nested JSON before writing, process data row-by-row, and export in batches. This minimizes memory usage and ensures smooth CSV generation.

Q5: Can I customize the CSV delimiter or column order when exporting JSON to CSV?

A5: Yes. Spire.XLS allows you to set custom delimiters (e.g., comma, semicolon, or tab) and manually define the column order, giving you full control over professional CSV output.

In many Python projects, especially those that involve APIs, data analysis, or business reporting, developers often need to convert Excel to JSON or JSON to Excel using Python code. These formats serve different but complementary roles: JSON is ideal for structured data exchange and storage, while Excel is widely used for sharing, editing, and presenting data in business environments.

This tutorial provides a complete, developer-focused guide to converting between JSON and Excel in Python. You'll learn how to handle nested data, apply Excel formatting, and resolve common conversion or encoding issues. We’ll use Python’s built-in json module to handle JSON data, and Spire.XLS for Python to read and write Excel files in .xlsx, .xls, and .csv formats — all without requiring Microsoft Excel or other third-party software.

Topics covered include:

• Install Spire.XLS for Python
• Why Choose Spire.XLS over General-Purpose Libraries?
• Convert JSON to Excel in Python
• Convert Excel to JSON in Python
• Real-World Example: Handling Nested JSON and Complex Excel Formats
• Common Errors and Fixes
• FAQ
• Conclusion

Install Spire.XLS for Python

This library is used in this tutorial to generate and parse Excel files (.xlsx, .xls, .csv) as part of the JSON–Excel conversion workflow.

To get started, install the Spire.XLS for Python package from PyPI:

pip install spire.xls

You can also choose Free Spire.XLS for Python in smaller projects:

pip install spire.xls.free

Spire.XLS for Python runs on Windows, Linux, and macOS. It does not require Microsoft Excel or any COM components to be installed.

Why Choose Spire.XLS over Open-Source Libraries?

Many open-source Python libraries are great for general Excel tasks like simple data export or basic formatting. If your use case only needs straightforward table output, these tools often get the job done quickly.

However, when your project requires rich Excel formatting, multi-sheet reports, or an independent solution without Microsoft Office, Spire.XLS for Python stands out by offering a complete Excel feature set.

For developer teams that need polished Excel files — with complex layouts, visuals, or business-ready styling — Spire.XLS is an efficient, all-in-one alternative.

Convert JSON to Excel in Python

In this section, we’ll walk through how to convert structured JSON data into an Excel file using Python. This is especially useful when exporting API responses or internal data into .xlsx reports for business users or analysts.

Step 1: Prepare JSON Data

We’ll start with a JSON list of employee records:

[
  {"employee_id": "E001", "name": "Jane Doe", "department": "HR"},
  {"employee_id": "E002", "name": "Michael Smith", "department": "IT"},
  {"employee_id": "E003", "name": "Sara Lin", "department": "Finance"}
]

This is a typical structure returned by APIs or stored in log files. For more complex nested structures, see the real-world example section.

Step 2: Convert JSON to Excel in Python with Spire.XLS

from spire.xls import Workbook, FileFormat
import json

# Load JSON data from file
with open("employees.json", "r", encoding="utf-8") as f:
    data = json.load(f)

# Create a new Excel workbook and access the first worksheet
workbook = Workbook()
sheet = workbook.Worksheets[0]

# Write headers to the first row
headers = list(data[0].keys())
for col, header in enumerate(headers):
    sheet.Range[1, col + 1].Text = header

# Write data rows starting from the second row
for row_idx, row in enumerate(data, start=2):
    for col_idx, key in enumerate(headers):
        sheet.Range[row_idx, col_idx + 1].Text = str(row.get(key, ""))

# Auto-fit the width of all used columns
for i in range(1, sheet.Range.ColumnCount + 1):
    sheet.AutoFitColumn(i)

# Save the Excel file and release resources
workbook.SaveToFile("output/employees.xlsx", FileFormat.Version2016)
workbook.Dispose()

Code Explanation:

• Workbook() initializes the Excel file with three default worksheets.
• workbook.Worksheets[] accesses the specified worksheet.
• sheet.Range(row, col).Text writes string data to a specific cell (1-indexed).
• The first row contains column headers based on JSON keys, and each JSON object is written to a new row beneath it.
• workbook.SaveToFile() saves the Excel workbook to disk. You can specify the format using the FileFormat enum — for example, Version97to2003 saves as .xls, Version2007 and newer save as .xlsx, and CSV saves as .csv.

The generated Excel file (employees.xlsx) with columns employee_id, name, and department.

You can also convert the Excel worksheet to a CSV file using Spire.XLS for Python if you need a plain text output format.

Convert Excel to JSON in Python

This part explains how to convert Excel data back into structured JSON using Python. This is a common need when importing .xlsx files into web apps, APIs, or data pipelines that expect JSON input.

Step 1: Load the Excel File

First, we use Workbook.LoadFromFile() to load the Excel file, and then select the worksheet using workbook.Worksheets[0]. This gives us access to the data we want to convert into JSON format.

from spire.xls import Workbook

# Load the Excel file
workbook = Workbook()
workbook.LoadFromFile("products.xlsx")
sheet = workbook.Worksheets[0]

Step 2: Extract Excel Data and Write to JSON

import json

# Get the index of the last row and column
rows = sheet.LastRow
cols = sheet.LastColumn

# Extract headers from the first row
headers = [sheet.Range[1, i + 1].Text for i in range(cols)]
data = []

# Map each row to a dictionary using headers
for r in range(2, rows + 1):
    row_data = {}
    for c in range(cols):
        value = sheet.Range[r, c + 1].Text
        row_data[headers[c]] = value
    data.append(row_data)

# Write JSON output
with open("products_out.json", "w", encoding="utf-8") as f:
    json.dump(data, f, indent=2, ensure_ascii=False)

Code Explanation:

• sheet.LastRow and sheet.LastColumn detect actual used cell range.
• The first row is used to extract field names (headers).
• Each row is mapped to a dictionary, forming a list of JSON objects.
• sheet.Range[row, col].Text returns the cell’s displayed text, including any formatting (like date formats or currency symbols). If you need the raw numeric value or a real date object, you can use .Value, .NumberValue, or .DateTimeValue instead.

The JSON file generated from the Excel data using Python:

If you’re not yet familiar with how to read Excel files in Python, see our full guide: How to Read Excel Files in Python Using Spire.XLS.

Real-World Example: Handling Nested JSON and Formatting Excel

In real-world Python applications, JSON data often contains nested dictionaries or lists, such as contact details, configuration groups, or progress logs. At the same time, the Excel output is expected to follow a clean, readable layout suitable for business or reporting purposes.

In this section, we'll demonstrate how to flatten nested JSON data and format the resulting Excel sheet using Python and Spire.XLS. This includes merging cells, applying styles, and auto-fitting columns — all features that help present complex data in a clear tabular form.

Let’s walk through the process using a sample file: projects_nested.json.

Step 1: Flatten Nested JSON

Sample JSON file (projects_nested.json):

[
  {
    "project_id": "PRJ001",
    "title": "AI Research",
    "manager": {
      "name": "Dr. Emily Wang",
      "email": "emily@lab.org"
    },
    "phases": [
      {"phase": "Design", "status": "Completed"},
      {"phase": "Development", "status": "In Progress"}
    ]
  },
  {
    "project_id": "PRJ002",
    "title": "Cloud Migration",
    "manager": {
      "name": "Mr. John Lee",
      "email": "john@infra.net"
    },
    "phases": [
      {"phase": "Assessment", "status": "Completed"}
    ]
  }
]

We'll flatten all nested structures, including objects like manager, and summarize lists like phases into string fields. Each JSON record becomes a single flat row, with even complex nested data compactly represented in columns using readable summaries.

import json

# Helper: Flatten nested data and summarize list of dicts into strings
# e.g., [{"a":1},{"a":2}] → "a: 1; a: 2"
def flatten(data, parent_key='', sep='.'):
    items = {}
    for k, v in data.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            items.update(flatten(v, new_key, sep=sep))
        elif isinstance(v, list):
            if all(isinstance(i, dict) for i in v):
                items[new_key] = "; ".join(
                    ", ".join(f"{ik}: {iv}" for ik, iv in i.items()) for i in v
                )
            else:
                items[new_key] = ", ".join(map(str, v))
        else:
            items[new_key] = v
    return items

# Load and flatten JSON
with open("projects_nested.json", "r", encoding="utf-8") as f:
    raw_data = json.load(f)

flat_data = [flatten(record) for record in raw_data]

# Collect all unique keys from flattened data as headers
all_keys = set()
for item in flat_data:
    all_keys.update(item.keys())
headers = list(sorted(all_keys))  # Consistent, sorted column order

This version of flatten() converts lists of dictionaries into concise summary strings (e.g., "phase: Design, status: Completed; phase: Development, status: In Progress"), making complex structures more compact for Excel output.

Step 2: Format and Export Excel with Spire.XLS

Now we’ll export the flattened project data to Excel, and use formatting features in Spire.XLS for Python to improve the layout and readability. This includes setting fonts, colors, merging cells, and automatically adjusting column widths for a professional report look.

from spire.xls import Workbook, Color, FileFormat

# Create workbook and worksheet
workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Projects"

# Title row: merge and style
col_count = len(headers)
sheet.Range[1, 1, 1, col_count].Merge()
sheet.Range[1, 1].Text = "Project Report (Flattened JSON)"
title_style = sheet.Range["A1"].Style
title_style.Font.IsBold = True
title_style.Font.Size = 14
title_style.Font.Color = Color.get_White()
title_style.Color = Color.get_DarkBlue()

# Header row from flattened keys
for col, header in enumerate(headers):
    cell = sheet.Range[2, col + 1]
    cell.BorderAround() # Add outside borders to a cell or cell range
    #cell.BorderInside() # Add inside borders to a cell range
    cell.Text = header
    style = cell.Style
    style.Font.IsBold = True
    style.Color = Color.get_LightGray()

# Data rows
for row_idx, row in enumerate(flat_data, start=3):
    for col_idx, key in enumerate(headers):
        sheet.Range[row_idx, col_idx + 1].Text = str(row.get(key, ""))

# Auto-fit columns and rows
for col in range(len(headers)):
    sheet.AutoFitColumn(col + 1)
for row in range(len(flat_data)):
    sheet.AutoFitRow(row + 1)

# Save Excel file
workbook.SaveToFile("output/projects_formatted.xlsx", FileFormat.Version2016)
workbook.Dispose()

This produces a clean, styled Excel sheet from a nested JSON file, making your output suitable for reports, stakeholders, or dashboards.

Code Explanation

• sheet.Range[].Merge(): merges a range of cells into one. Here we use it for the report title row (A1:F1).
• .Style.Font / .Style.Color: allow customizing font properties (bold, size, color) and background fill of a cell.
• .BorderAround() / .BorderInside(): add outside/inside borders to a cell range.
• AutoFitColumn(n): automatically adjusts the width of column n to fit its content.

The Excel file generated after flattening the JSON data using Python:

Common Errors and Fixes in JSON ↔ Excel Conversion

Converting between JSON and Excel may sometimes raise formatting, encoding, or data structure issues. Here are some common problems and how to fix them:

Conclusion

With Spire.XLS for Python, converting between JSON and Excel becomes a streamlined and reliable process. You can easily transform JSON data into well-formatted Excel files, complete with headers and styles, and just as smoothly convert Excel sheets back into structured JSON. The library helps you avoid common issues such as encoding errors, nested data complications, and Excel file format pitfalls.

Whether you're handling data exports, generating reports, or processing API responses, Spire.XLS provides a consistent and efficient way to work with .json and .xlsx formats in both directions.

Want to unlock all features without limitations? Request a free temporary license for full access to Spire.XLS for Python.

FAQ

Q1: How to convert JSON into Excel using Python?

You can use the json module in Python to load structured JSON data, and then use a library like Spire.XLS to export it to .xlsx. Spire.XLS allows writing headers, formatting Excel cells, and handling nested JSON via flattening. See the JSON to Excel section above for step-by-step examples.

Q2: How do I parse JSON data in Python?

Parsing JSON in Python is straightforward with the built-in json module. Use json.load() to parse JSON from a file, or json.loads() to parse a JSON string. After parsing, the result is usually a list of dictionaries, which can be iterated and exported to Excel or other formats.

Q3: Can I export Excel to JSON with Spire.XLS in Python?

Yes. Spire.XLS for Python lets you read Excel files and convert worksheet data into a list of dictionaries, which can be written to JSON using json.dump(). The process includes extracting headers, detecting used rows and columns, and optionally handling formatting. See Excel to JSON for detailed implementation.

While working with CSV files is common in data processing, Excel (XLSX) often provides more advantages when it comes to data sharing, visualization, and large-scale analysis. In this guide, you’ll learn how to convert CSV to Excel in Python, including both single file and batch conversion methods. Whether you're automating reports or preparing data for further analysis, this guide will help you handle the conversion efficiently.

• Why Convert CSV to Excel
• Install Required Python Libraries
• Convert Single CSV to Excel
• Batch Convert CSV to XLSX
• FAQs

Why Convert CSV to Excel?

While CSV files are widely used for data storage and exchange due to their simplicity, they come with several limitations—especially when it comes to formatting, presentation, and usability. Converting CSV to Excel can bring several advantages:

Benefits of Converting CSV to Excel

• Better formatting support: Excel allows rich formatting options like fonts, colors, borders, and cell merging, making your data easier to read and present.
• Multiple worksheets: Unlike CSV files that support only a single sheet, Excel files can store multiple worksheets in one file, which is better for large datasets.
• Built-in formulas and charts: You can apply Excel formulas, pivot tables, and charts to analyze and visualize your data.
• Improved compatibility for business users: Excel is the preferred tool for many non-technical users, making it easier to share and collaborate on data.

Limitations of CSV Files

• No styling or formatting (plain text only)
• Single-sheet structure only
• Encoding issues (e.g., with non-English characters)
• Not ideal for large datasets or advanced reporting If your workflow involves reporting, data analysis, or sharing data with others, converting CSV to Excel is often a more practical and flexible choice.

Install Required Python Libraries

This guide demonstrates how to effortlessly convert CSV to Excel using Spire.XLS for Python. Spire.XLS is a powerful and professional Python Excel library that allows you to read, edit, and convert Excel files (both .xlsx and .xls) without relying on Microsoft Excel. Installing this CSV to Excel converter on your device is simple — just run the following command:

pip install Spire.XLS

Alternatively, you can download the Spire.XLS package manually for custom installation.

How to Convert CSV to Excel in Python: Single File

Now let’s get to the main part — how to convert a single CSV file to Excel using Python. With the help of Spire.XLS, this task becomes incredibly simple. All it takes is three easy steps: create a new workbook, load the CSV file, and save it as an Excel (.xlsx) file. Below is a detailed walkthrough along with a complete code example — let’s take a look!

Steps to convert a single CSV to Excel in Python:

• Create a Workbook instance.
• Load a sample CSV file using Workbook.LoadFromFile() method.
• Save the CSV file as Excel through Workbook.SaveToFile() method.

Below is the Python code to convert a CSV file to Excel. It also ignores parsing errors and automatically adjusts the column widths for better readability.

from spire.xls import *
from spire.xls.common import *

# Create a workbook
workbook = Workbook()

# Load a csv file
workbook.LoadFromFile("/sample csv.csv", ",", 1, 1)
  
# Set ignore error options
sheet = workbook.Worksheets[0]
sheet.Range["D2:E19"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText
sheet.AllocatedRange.AutoFitColumns()  

# Save the document and launch it
workbook.SaveToFile("/CSVToExcel1.xlsx", ExcelVersion.Version2013)

Warm Note: If you're only working with small files or doing some light testing, you can also use the free Spire.XLS. It's a great option for getting started quickly.

How to Batch Convert CSV to XLSX in Python

Another common scenario is when you need to convert multiple CSV files to Excel. Instead of manually replacing the file path and name for each one, there's a much more efficient approach. Simply place all the CSV files in the same folder, then use Python to loop through each file and convert them to Excel using the Workbook.SaveToFile() method. Let’s walk through the detailed steps below!

Steps to batch convert CSVs to Excel files in Python:

• Specify the file paths of input and output folders.
• Loop through all CSV files in the input folder.
• Create an object of Workbook class.
• Load each CSV file from the input folder with Workbook.LoadFromFile() method.
• Save the current CSV as an Excel file through Workbook.SaveToFile() method.

Here's the Python code to batch convert CSV to Excel (.XLSX):

import os
from spire.xls import *

input_folder = r"E:input\New folder"
output_folder = r"output\New folder"

# Loop through each CSV file
for csv_file in os.listdir(input_folder):
    if csv_file.endswith(".csv"):
        input_path = os.path.join(input_folder, csv_file)
        output_name = os.path.splitext(csv_file)[0] + ".xlsx"
        output_path = os.path.join(output_folder, output_name)

        # Create a Workbook instance and load CSV files
        workbook = Workbook()
        workbook.LoadFromFile(input_path, ",", 1, 1)

        # Save each CSV file as an Excel file
        workbook.SaveToFile(output_path, ExcelVersion.Version2013)

The Conclusion

This guide showed you how to convert CSV to Excel in Python with step-by-step instructions and complete code examples. Whether you're working with a single CSV file or multiple files, Spire.XLS makes the process simple, fast, and hassle-free. Need help with more advanced scenarios or other Excel-related tasks? Feel free to contact us anytime!

FAQs about Converting CSV to Excel

Q1: How to convert CSV to Excel in Python without pandas?
A: You can use libraries like Spire.XLS, openpyxl, or xlsxwriter to convert CSV files without relying on pandas. These tools provide simple APIs to load .csv files and export them as xlsx—no Microsoft Excel installation required.

Q2: What is the easiest way to convert multiple CSV files to Excel in Python?
A: Just place all CSV files in one folder, then loop through them in Python and convert each using Workbook.SaveToFile(). This approach is ideal for batch processing. Alternatively, online converters can be a quick fix for occasional use.

Q3: How to auto-adjust column width when converting CSV to Excel in Python?
A: After loading the CSV, call worksheet.autoFitColumns() in Spire.XLS to automatically resize columns based on content before saving the Excel file.