How jsoncsv Handles Empty Dicts and Lists During Excel Export

When exporting JSON to Excel-compatible formats, jsoncsv's DumpCSV class converts empty dictionaries and lists to blank cells, while DumpXLS renders empty dicts as the literal string "{}" and empty lists as their Python string representation "[]".

The alingse/jsoncsv library provides Python utilities for converting hierarchical JSON data into flat spreadsheet formats. Understanding how the toolkit handles empty collections during Excel export is essential for predicting cell output and ensuring your data appears correctly in the final workbook.

CSV Export: Normalizing Empty Structures to Blank Cells

In jsoncsv/dumptool.py, the DumpCSV class handles empty collections through its patch_value method (lines 97–115). This method acts as a normalization filter for every field before writing to the CSV file.

def patch_value(self, value):
    if value in (None, {}, []):
        return ""
    return str(value)

When patch_value encounters a None value, an empty dictionary {}, or an empty list [], it returns an empty string "". Consequently, the generated CSV file—and any Excel workbook opened from that CSV—contains blank cells for these empty structures. This behavior ensures consistent, clean output when working with text-based spreadsheet formats.

XLS Export: Divergent Handling of Empty Collections

The binary Excel export path behaves differently. In the same file (jsoncsv/dumptool.py), the DumpXLS class implements the write_obj method (lines 133–141) to handle row writing via the xlwt library.

def write_obj(self, obj):
    for head in self._headers:
        value = obj.get(head)
        if value == {}:               # Special-case for empty dict

            value = "{}"
        self.ws.write(self.row, self.cloumn, value)
        self.cloumn += 1

Unlike the CSV exporter, DumpXLS only special-cases empty dictionaries, converting them to the visible string "{}". Empty lists receive no transformation and are passed directly to xlwt.write(). Because xlwt expects scalar values, it stores the Python list object itself, which renders in the cell as the string representation "[]".

Why the Handling Differs Between Formats

The distinction stems from the fundamental nature of the target formats:

• CSV (text-based): Since CSV files contain plain text, the exporter normalizes all "empty" JSON values to empty strings. This produces uniform blank cells across the spreadsheet.
• XLS (binary via xlwt): The binary Excel format requires explicit type handling. The author chose to make empty dictionaries visible as "{}" to avoid ambiguity, while empty lists were left untouched, causing them to display as their literal Python representation.

Practical Usage Example

The following example demonstrates the divergent behavior using the dump_excel function from jsoncsv.main.py:

import jsoncsv
from jsoncsv import dumptool

# Prepare sample data with empty structures

data = [
    '{"id": 1, "meta": {}}',
    '{"id": 2, "meta": []}',
    '{"id": 3, "meta": "active"}'
]

# Write to temp file

with open('input.jsonl', 'w') as f:
    f.write('\n'.join(data))

# Export to CSV (blank cells for empty dict/list)

jsoncsv.dump_excel(
    open('input.jsonl', 'r'),
    open('output.csv', 'w'),
    dumptool.DumpCSV
)

# Export to XLS ({} visible, [] as list object)

jsoncsv.dump_excel(
    open('input.jsonl', 'r'),
    open('output.xls', 'wb'),
    dumptool.DumpXLS
)

In the resulting CSV file, both the first and second rows display blank cells in the meta column. In the XLS file, the first row shows "{}" while the second row displays "[]".

Summary

• DumpCSV.patch_value in jsoncsv/dumptool.py converts None, {}, and [] to empty strings, producing blank cells in CSV output.
• DumpXLS.write_obj only transforms empty dictionaries to the string "{}", leaving empty lists as Python objects that render as "[]".
• CSV exports prioritize visual cleanliness with blank cells, while XLS exports preserve type information for empty dictionaries.
• The core export logic is implemented in jsoncsv/dumptool.py (lines 97–141), with the CLI entry point located in jsoncsv/main.py.

Frequently Asked Questions

How does jsoncsv handle None values during Excel export?

Both exporters treat None as an empty value. The DumpCSV class converts it to an empty string via patch_value, resulting in a blank cell. The DumpXLS class writes None directly to the worksheet, which xlwt typically renders as a blank cell since it cannot serialize Python's NoneType as a spreadsheet value.

Why does the XLS exporter show {} as a string instead of a blank cell?

According to the source code in jsoncsv/dumptool.py, the DumpXLS.write_obj method explicitly checks if value == {} and replaces it with the string "{}" before calling xlwt.write(). This design choice makes empty dictionaries visually distinguishable from truly null values in the binary Excel format, whereas the CSV exporter normalizes them to empty strings for consistency with text-based formats.

Can I customize how empty lists appear in XLS files?

The current implementation in jsoncsv/dumptool.py provides no special handling for empty lists in the DumpXLS class. To customize this behavior, you would need to subclass DumpXLS and override the write_obj method to check for empty lists (similar to the existing empty dict check) and replace them with your preferred string representation before writing to the worksheet.

Where is the Excel export logic implemented in the jsoncsv repository?

The core export functionality resides in jsoncsv/dumptool.py, specifically within the DumpCSV class (lines 97–115) and DumpXLS class (lines 133–141). The command-line interface and dump_excel function that orchestrate these exporters are located in jsoncsv/main.py. Unit tests validating this behavior can be found in tests/test_dumptool.py and tests/test_mkexcel.py.

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