How to Convert JSON to XLSX Using Python with the jsoncsv Library

Use the jsoncsv library to flatten nested JSON structures and write them directly to Excel format via the DumpXLS class or the mkexcel CLI tool.

The jsoncsv library by alingse provides a streamlined pipeline for converting complex JSON data into Excel workbooks using Python. While the library specifically generates .xls files (Excel 97-2003 binary format) rather than modern .xlsx files, the flattening and writing architecture demonstrates the core pattern for JSON-to-Excel conversion. This guide explains how to convert JSON to Excel using both command-line utilities and the native Python API.

Understanding the Conversion Pipeline

According to the jsoncsv source code, the conversion process follows a three-stage architecture implemented across separate modules:

1. Flattening – The expand function in jsoncsv/jsontool.py (lines 93-106) recursively traverses nested JSON objects and produces a flat dictionary where keys represent dot-delimited paths to leaf values.
2. Excel Writing – The DumpXLS class in jsoncsv/dumptool.py (lines 17-44) creates an xlwt.Workbook, writes headers, and populates rows using the flattened key-value pairs.
3. Orchestration – The mkexcel command in jsoncsv/main.py (lines 70-84) wires these components together, selecting DumpXLS when the --type xls flag is specified.

Command-Line Conversion Method

For quick conversions without writing Python scripts, use the two-stage CLI pipeline that ships with the library.

Piping jsoncsv to mkexcel

The jsoncsv command expands nested JSON, while mkexcel handles the Excel output generation:

# Expand nested JSON and convert to Excel format

jsoncsv input.json -e | mkexcel -t xls output.xls

How this works:

• jsoncsv input.json -e reads the input file and applies the expansion algorithm (-e flag), outputting flattened JSON lines to stdout.
• mkexcel -t xls selects the DumpXLS dumper and writes the binary Excel workbook to output.xls.

Both commands are defined in jsoncsv/main.py, with jsoncsv handling the expansion logic and mkexcel managing the output formatting.

Programmatic Python API Method

For integration into larger applications, import the core functions directly from jsoncsv.jsontool and jsoncsv.dumptool.

Step 1: Flatten the JSON Structure

Use the expand function to transform nested dictionaries into a flat map suitable for tabular output:

import json
from jsoncsv.jsontool import expand

# Load your JSON data

with open("data.json", "r", encoding="utf-8") as f:
    raw_data = json.load(f)

# Flatten with dot notation separator (default: '.')

flattened = expand(raw_data, separator=".", safe=False)

The expand function (located at jsoncsv/jsontool.py lines 93-106) uses a recursive generator gen_leaf to traverse the JSON tree and build the flat dictionary.

Step 2: Write to Excel Format

Pass the flattened data to dump_excel with the DumpXLS class to generate the workbook:

import io
from pathlib import Path
from jsoncsv.dumptool import dump_excel, DumpXLS

# Convert flat dict to line-delimited JSON format expected by dump_excel

json_lines = "\n".join(json.dumps({k: v}) for k, v in flattened.items())
input_stream = io.StringIO(json_lines)

# Write binary Excel output

with Path("output.xls").open("wb") as out_f:
    dump_excel(input_stream, out_f, DumpXLS, read_row=None, sort_type=False)

The DumpXLS class creates an xlwt.Workbook object, writes the header row via write_headers, and populates data rows through write_obj (implementation details in jsoncsv/dumptool.py lines 27-44).

Key Implementation Files

Understanding these source files helps when debugging or extending the conversion logic:

• jsoncsv/jsontool.py – Contains the expand and restore functions that handle JSON tree flattening and reconstruction.
• jsoncsv/dumptool.py – Defines the abstract Dump base class and concrete implementations DumpCSV and DumpXLS for file output.
• jsoncsv/main.py – Implements the Click-based CLI interface, defining the jsoncsv and mkexcel entry points.

XLS vs. XLSX Format Considerations

The jsoncsv library uses xlwt (Excel Write Library) to generate .xls files compatible with Excel 97-2003. If you require the modern .xlsx format (Office Open XML), you must either:

• Convert the output using a separate library like pyexcel or pandas after generation.
• Modify jsoncsv/dumptool.py to use openpyxl or xlsxwriter instead of xlwt in a custom DumpXLSX class.

Excel 2010 and later versions can open .xls files without conversion, making this approach compatible with most modern workflows despite the older format.

Summary

• The jsoncsv library converts JSON to Excel through a two-step flatten-and-dump process.
• Use jsoncsv input.json -e | mkexcel -t xls output.xls for command-line conversions.
• For Python scripts, call expand() from jsoncsv/jsontool.py to flatten data, then dump_excel() with DumpXLS from jsoncsv/dumptool.py to write the workbook.
• The library generates .xls binary format using xlwt, not modern .xlsx XML format.
• Core logic resides in jsontool.py (flattening), dumptool.py (Excel writing), and main.py (CLI orchestration).

Frequently Asked Questions

Does jsoncsv support XLSX format or only XLS?

The jsoncsv library specifically generates .xls files (Excel 97-2003 binary format) using the xlwt library, not the modern .xlsx Office Open XML format. However, Excel 2007 and later versions can open .xls files natively. To generate true .xlsx files, you would need to extend the Dump class in dumptool.py to use openpyxl or xlsxwriter instead of xlwt.

How does jsoncsv handle deeply nested JSON objects?

The expand function in jsoncsv/jsontool.py recursively traverses nested objects and arrays, creating flattened keys using dot notation (e.g., user.address.city becomes the column header). The safe=False parameter controls whether the separator character within keys is escaped. This approach preserves all leaf values while creating a tabular structure suitable for Excel's row-column format.

Can I convert multiple JSON files to a single Excel workbook?

The library processes one logical input stream at a time. To combine multiple JSON files into a single Excel workbook, concatenate the flattened output from each file into one input stream before calling dump_excel, or concatenate the files first using the CLI: cat file1.json file2.json | jsoncsv -e | mkexcel -t xls combined.xls.

What dependencies are required for Excel output?

The jsoncsv library requires xlwt for Excel output generation. When you install jsoncsv via pip install jsoncsv, xlwt is typically installed as a dependency. The library does not require Microsoft Excel to be installed on the machine, as xlwt generates the binary workbook format independently.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}