Can CSV Express Nested Data Reliably?
From the earlier reference collection. Read the current software blog.
CSV is useful for rows and columns, but relationships and nested values require explicit conventions or a companion structured representation.
A table has a shape
CSV represents records as rows with fields arranged in columns. That shape works well when each record has a predictable set of values, such as a list of stations with an ID, name, and latitude. Nested objects and variable-length lists do not naturally fit the same rectangular structure.
Suppose an order contains an address and several line items. Putting the address into columns is straightforward. The items are harder: one order may contain two and another twenty. Adding item_1, item_2, and so on creates an arbitrary limit and leaves many cells empty.
Flatten with care
One approach is to flatten nested properties into named columns, such as customer_city or sensor_unit. This works when the structure is fixed and shallow. Names should make the path and meaning clear, and the schema should explain how missing components are represented.
For repeating children, use a separate CSV file with a stable parent key. An orders file can contain one row per order, while an order_items file contains one row per item and an order_id column. This is a relational representation, not a single self-contained row, so document how to join the files and whether item order matters.
When a cell contains structure
Some producers place JSON text inside a CSV cell. This can preserve nested data, but it creates a second parsing layer: first parse the CSV quoting rules, then parse the cell as JSON. Commas, quotes, and line breaks inside the value must be escaped correctly at both levels. A spreadsheet user may see an opaque string rather than usable columns.
If this method is chosen, specify the embedded format, encoding, null behavior, and expected shape. Provide an example that includes quotes and a list, then verify that common import tools preserve the cell exactly. Avoid ad hoc delimiters such as semicolons unless escaping and nesting rules are fully defined.
Choose for the reader
Use CSV when users benefit from a simple table and the relationships can be represented clearly as columns or linked files. Use a structured companion format when nested objects are central or variable in shape. Publishing both can serve spreadsheet workflows and software clients, provided they describe the same dataset and share stable identifiers.
Before release, round-trip a representative record through export and import. Check that lists, empty values, relationships, and ordering survive. The right format is the one whose rules readers can apply without guessing what a cell was meant to contain.