Convert JSON to CSV without losing data meaning

JSON and CSV solve different problems. JSON keeps objects, lists, and types; CSV arranges values into rows and columns. Converting between them is useful when a person needs to inspect an extract in a spreadsheet, but it is never a neutral operation. Before converting, decide what one row represents, which fields become columns, which types are written as text, and which information cannot be reconstructed.

Treating CSV as “JSON without braces” causes many bad exports. RFC 8259 permits nested structures, null, booleans, numbers, and strings. RFC 4180 describes comma-delimited records, where fields containing a comma, quote, or line break are quoted and quotes in data are doubled. Neither standard says how a customer with several orders should fit into one row. That choice belongs to the export contract.

The problem

The problem starts when an export combines data with different cardinality. A record can have one customer and address but many orders; each order can have many line items. If each customer occupies one row, where do two orders go? If each item occupies one row, should customer and order values repeat? Both answers are reasonable for different uses, but they produce files with different meanings.

Types create another risk. An identifier such as "00073" is not the number 73: the zeroes may be part of the code. A cell containing true might represent a boolean or literal text, depending on the importer. null means no value, while an empty string can mean a known value that happens to be empty. If all of those cases become an empty cell, the spreadsheet looks tidy but loses information needed by a later import.

Delimiters do not remove the need for escaping. A note such as Deliver to Madrid, door 4 contains a comma and must be quoted. A two-line note must retain its line break inside a quoted field. Seeing the file open correctly in one application does not prove that another importer applies the same delimiter, encoding, or line-ending rules.

Worked example

Use a small synthetic example rather than customer data. This JSON contains a customer, two orders, a tag array, a null, a boolean, leading-zero codes, a comma, and a line break.

{
  "customer": {
    "id": "00073",
    "name": "Lucía, S.A.",
    "marketingAllowed": false,
    "phone": null,
    "tags": ["wholesale", "priority"],
    "address": { "city": "Madrid", "postalCode": "08001" }
  },
  "orders": [
    { "id": "ORD-001", "paid": true, "note": "Call before delivery\nin the afternoon", "items": [{ "sku": "A-01", "qty": 2 }, { "sku": "B-02", "qty": 1 }] },
    { "id": "ORD-002", "paid": false, "note": "Deliver to Valencia, reception", "items": [{ "sku": "C-03", "qty": 4 }] }
  ]
}

An export with one row per order item can have customer.id, customer.name, customer.marketingAllowed, customer.phone, customer.address.city, customer.address.postalCode, order.id, order.paid, order.note, item.sku, and item.qty columns. Its first row contains "00073", "Lucía, S.A.", false, an empty cell, Madrid, "08001", ORD-001, true, a quoted multi-line note, A-01, and 2. Customer information repeats because a row represents an item, not a customer.

Procedure

First state the row grain in a sentence another person can test: “one row is one order item” or “one row is one order.” Then list the JSON paths that become columns, using stable names such as customer.address.postalCode. Avoid generic headers such as id; they become ambiguous as soon as customer and order identifiers coexist.

Next decide how to treat every array. For orders and items, a normalized strategy emits one row per order-item combination and repeats higher-level fields. That is useful for filtering quantities and summing sales. If the target needs one row per order, items can be serialized as JSON in a cell, but CSV no longer exposes every property separately. For tags, document whether you will join values with ;, create numbered columns, or make a separate table. Do not invent a separator when a tag may contain it.

Then set type representations. Keep identifiers and postal codes as text; when importing into a spreadsheet, set the column to text before it turns 00073 into 73. Represent booleans as true and false, or another agreed pair, without mixing Yes, 1, and true. Pick an explicit null rule, such as an empty cell plus a specification distinguishing null from empty, or a reserved token that cannot occur as data. Test non-ASCII characters, commas, quotes, and line breaks.

Finally validate with a CSV reader that follows the agreed format. Check the expected row count: the example has three order items. Verify that a data quote is doubled inside a quoted field and that a line break does not create an extra record. A conversion tool can prepare the file; the integration contract remains the owner’s responsibility.

Technical explanation

Flattening turns paths in a tree into column names. customer.address.city preserves a path, but it does not make a hierarchy reversible by itself. Object paths are fairly direct because an object has one value per key. Arrays change the relationship: a list has zero, one, or many values, while a table must choose between repeated rows, joined values, or another table.

The repeated-row approach resembles related database tables. For each orders[0].items entry, the exporter copies customer and orders[0] values. This permits a quantity total without parsing JSON from a cell. The cost is duplication. If someone changes the customer name in only one row, the rows disagree. CSV is therefore often an exchange or analysis format, not the sole source of truth.

RFC 4180 requires quotes around fields with commas, quotes, or line breaks; a quote in data is represented by two quotes. Do not simply replace commas with semicolons: that changes content and fails where a consumer expects commas. Specify encoding and line endings as well, because CSV does not reliably carry metadata for every import decision.

Common failures

A common failure is selecting the first list entry and discarding the rest. Exporting the example as one row per order while keeping only the first item looks successful but silently loses data. Another is joining values with commas without escaping them: Lucía, S.A. becomes two apparent columns. A third is using an empty column for both null and ""; no later JSON reconstruction can tell which value existed.

It also fails to assume that a spreadsheet preserves types. It may convert 00073 to 73, interpret ORD-001 as a date under local settings, or display large numbers in scientific notation. If the file will be reimported, deliver a column specification and test sample. Keep the original JSON or a batch identifier so the source can be found.

Finally, well-formed CSV is not automatically safe data. A user-controlled cell beginning with =, +, -, or @ can be treated as a formula by some programs. For spreadsheet exports, apply and test a destination-appropriate mitigation policy. Do not silently alter data for a critical integration without documenting that contract.

Considerations

Ask who will read the file before designing columns. For a quick analysis, one wide repeated table can be appropriate. For a migration, several related CSV files—customers, orders, and items—joined by textual identifiers may be better. For backup, JSON preserves the original structure far better. The same data can need three different exports without one being universally correct.

Keep a mapping table beside the exporter: JSON path, CSV column, expected type, transformation, null treatment, and example. Include edge cases such as an empty array, a note with quotes, and a customer without a phone number. Version this mapping when a column changes. A consumer depending on customer.id should not discover accidentally that it is now called client_code.

Privacy affects conversion too. Reducing fields before export is usually safer than hiding them later in a shared workbook. The example is fabricated so it can be reused safely. If a file contains personal data, restrict access, avoid services that have not been assessed, and follow the duties that apply to the responsible organization.

Limitations

No JSON-to-CSV conversion automatically preserves every meaning in every document. CSV does not itself distinguish number from text, null from empty, a missing object from an empty object, or a one-item list from text that resembles a list. A round trip can reconstruct selected rows when it stores extra rules, but not the original tree in all cases.

This guide does not certify compatibility with a particular spreadsheet, replace an API contract, integration testing, or professional advice for regulated data. Import behavior depends on the program, regional settings, and encoding. Always test synthetic copies and verify the real destination before processing an important batch.

Checklist

Define what a row represents and which JSON paths become columns. Choose and document an array strategy and its duplication. Keep leading-zero identifiers as text. Define booleans, null, and empty string unambiguously. Escape commas, quotes, and line breaks according to CSV. Test the synthetic JSON, count rows, and read the file back with the intended consumer. Keep original JSON when structural fidelity matters, and publish a versioned mapping so another person can reproduce the export.