How to Convert JSON to CSV Using Python CLI with jsoncsv

The jsoncsv package provides a pure-Python, stream-based CLI toolset that converts JSON to CSV through a two-step pipeline: jsoncsv flattens nested objects, and mkexcel writes the output to CSV or XLS format.

The alingse/jsoncsv repository offers a lightweight solution for converting JSON to CSV using Python CLI commands without loading entire files into memory. This tool handles complex nested structures by flattening keys into dot-notation (or custom separators) before export, making it ideal for data processing workflows.

The Two-Command Pipeline Architecture

The tool separates concerns into two distinct commands defined in jsoncsv/main.py. This design allows streaming processing of arbitrarily large files.

Step 1: Flattening with jsoncsv

The first command, jsoncsv (lines 24-50 in jsoncsv/main.py), handles the transformation of nested JSON into flat key-value pairs. It uses the expand() function from jsoncsv/jsontool.py to recursively walk JSON objects via gen_leaf and build flattened dictionaries where nested paths join with a separator (default .).

The convert_json() utility (lines 93-119 in jsoncsv/jsontool.py) streams input line-by-line by default, or processes entire JSON arrays when the -A flag is specified. This stream-based approach ensures minimal memory footprint even with gigabyte-sized inputs.

Step 2: Exporting with mkexcel

The second command, mkexcel (lines 55-92 in jsoncsv/main.py), consumes the flattened JSON and writes to the target format. It selects between DumpCSV and DumpXLS classes from jsoncsv/dumptool.py based on the output type.

DumpCSV extends the base DumpExcel class and utilizes Python's built-in csv.DictWriter to automatically generate headers and write rows. The dump_excel() helper validates the dumper class and manages the lifecycle: prepare → dump_file → on_finish.

Handling Complex JSON Structures

For nested objects, the default separator creates column names like user.name or address.city. The restore() function in jsoncsv/jsontool.py can reverse this process using from_leaf to rebuild original hierarchies from flattened keys.

The utility functions in jsoncsv/utils.py provide type aliases and safe-key encoding to prevent collisions when custom separators are used.

Practical CLI Examples

Basic JSON to CSV Conversion

Convert newline-delimited JSON to CSV using the standard pipeline:

cat data.json | jsoncsv | mkexcel > output.csv

This pipes the raw JSON through the expand function and directly into the CSV dumper.

Custom Key Separators

If your JSON keys contain dots, specify an alternative separator with the -s flag:

cat data.json | jsoncsv -s '_' | mkexcel > output.csv

The underscore separator prevents conflicts with existing dots in key names.

Processing JSON Arrays

For single JSON array files rather than line-delimited JSON, enable array mode:

cat array.json | jsoncsv -A | mkexcel > output.csv

The -A (or --array) option instructs convert_json to parse the entire file as a list before streaming elements.

Generating Excel Files

Export directly to XLS format instead of CSV:

cat data.json | jsoncsv | mkexcel -t xls > output.xls

The -t xls parameter selects the DumpXLS class, which uses the xlwt library to create Excel workbooks.

Restoring Expanded JSON

To reverse the flattening process and restore original nested structures:

cat expanded.json | jsoncsv -r > original.json

The -r (or --restore) flag switches the operation to use the restore() function instead of expand().

Summary

• jsoncsv provides a two-stage pipeline: jsoncsv for flattening and mkexcel for format conversion.
• The architecture in jsoncsv/main.py supports streaming processing via convert_json() in jsoncsv/jsontool.py, enabling handling of large files without memory issues.
• Nested JSON is flattened using dot-notation by default, with customizable separators via the -s flag.
• Output supports both CSV (via DumpCSV) and Excel XLS (via DumpXLS) formats through jsoncsv/dumptool.py.
• The tool can process both line-delimited JSON and JSON arrays using the -A flag, and operations are reversible using the -r restore option.

Frequently Asked Questions

How does jsoncsv handle large JSON files without memory issues?

The tool implements stream-based processing in jsoncsv/jsontool.py where convert_json() reads and transforms input line-by-line rather than loading entire files into memory. This design allows the CLI to process arbitrarily large JSON files efficiently, forwarding flattened objects immediately to mkexcel for output generation.

What is the difference between the jsoncsv and mkexcel commands?

jsoncsv (defined in jsoncsv/main.py lines 24-50) is responsible for structural transformation—either expanding nested JSON into flat dictionaries or restoring flattened data to nested form. mkexcel (lines 55-92) handles format serialization, utilizing DumpCSV or DumpXLS from jsoncsv/dumptool.py to write the flattened data to CSV or Excel files. These commands are designed to pipe together but operate independently.

Can I convert JSON arrays directly to CSV using jsoncsv?

Yes, use the -A or --array flag when processing files containing a single JSON array instead of newline-delimited objects. This option modifies convert_json() to parse the entire input as a list and stream each element individually, maintaining the same memory-efficient processing as line-delimited inputs.

How do I customize the separator for nested keys in the CSV output?

Pass the -s flag followed by your desired separator string to the jsoncsv command. For example, jsoncsv -s '_' uses underscores instead of dots. This configuration is handled in the expansion logic within jsoncsv/jsontool.py, ensuring safe key encoding via utilities in jsoncsv/utils.py to prevent naming collisions.

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