# How jsoncsv Handles Empty Dicts and Lists During Excel Export

> Learn how jsoncsv manages empty dicts and lists in Excel exports. Discover the differences between DumpCSV and DumpXLS for blank cell representation.

- Repository: [alingse/jsoncsv](https://github.com/alingse/jsoncsv)
- Tags: how-to-guide
- Published: 2026-02-24

---

**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`](https://github.com/alingse/jsoncsv/blob/main/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.

```python
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`](https://github.com/alingse/jsoncsv/blob/main/jsoncsv/dumptool.py)), the `DumpXLS` class implements the `write_obj` method (lines 133–141) to handle row writing via the `xlwt` library.

```python
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`](https://github.com/alingse/jsoncsv/blob/main/jsoncsv.main.py):

```python
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`](https://github.com/alingse/jsoncsv/blob/main/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`](https://github.com/alingse/jsoncsv/blob/main/jsoncsv/dumptool.py) (lines 97–141), with the CLI entry point located in [`jsoncsv/main.py`](https://github.com/alingse/jsoncsv/blob/main/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`](https://github.com/alingse/jsoncsv/blob/main/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`](https://github.com/alingse/jsoncsv/blob/main/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`](https://github.com/alingse/jsoncsv/blob/main/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`](https://github.com/alingse/jsoncsv/blob/main/jsoncsv/main.py). Unit tests validating this behavior can be found in [`tests/test_dumptool.py`](https://github.com/alingse/jsoncsv/blob/main/tests/test_dumptool.py) and [`tests/test_mkexcel.py`](https://github.com/alingse/jsoncsv/blob/main/tests/test_mkexcel.py).