How to Convert JSON Data to CSV Format

JSON and CSV are two of the most common data formats, but they serve very different purposes. JSON handles complex, nested, hierarchical data with ease. CSV is flat, tabular, and universally compatible with spreadsheet applications. When you need to move data from an API response, a database export, or a configuration file into Excel, Google Sheets, or a database import tool, converting JSON to CSV is often the required step.

The conversion sounds simple on the surface, but real-world JSON data comes with nested objects, arrays, inconsistent fields, and special characters that make the process tricky. Understanding these challenges before you start saves hours of debugging malformed spreadsheets.

Why Convert JSON to CSV

Several common scenarios require this conversion.

Spreadsheet analysis: Business users, analysts, and managers typically work in Excel or Google Sheets. They need data in rows and columns, not nested brackets and curly braces. Converting API data or database exports to CSV lets non-technical team members work with the data using familiar tools.

Database imports: Many databases accept CSV for bulk imports. PostgreSQL’s COPY command, MySQL’s LOAD DATA INFILE, and similar tools expect tabular data. If your source data is in JSON, conversion is a prerequisite for import.

Reporting and visualization: Reporting tools like Tableau, Power BI, and even simple chart generators often accept CSV as an input format. Converting JSON to CSV bridges the gap between your data source and your reporting pipeline.

Data sharing: CSV files are smaller than their JSON equivalents (no brackets, braces, or key names repeated on every row) and are readable by virtually any software. When sharing data with external partners who may use different systems, CSV is the safest common format.

Legacy system compatibility: Older systems that predate JSON adoption frequently require CSV or fixed-width text files. Converting JSON to CSV ensures compatibility with these systems.

The Structure Problem

JSON and CSV have fundamentally different data models, and this mismatch is the root of most conversion difficulties.

CSV is flat. Each row represents one record. Each column represents one field. Every row has the same number of columns. There’s no concept of nesting, hierarchy, or variable-length data within a single cell.

JSON is hierarchical. Objects can contain other objects. Arrays can hold multiple values. Fields can be present in one record and absent in another. A single JSON record can represent a complex tree of data with arbitrary depth.

Converting from hierarchical to flat requires decisions about how to handle every structural mismatch.

Flattening Nested Objects

When a JSON object contains nested objects, you need to flatten them into individual columns. The standard approach is to concatenate key names with a separator (usually a dot or underscore).

Consider this JSON record:

{
  "name": "Acme Corp",
  "address": {
    "street": "123 Main St",
    "city": "Springfield",
    "state": "IL"
  }
}

Flattened for CSV, this becomes columns: name, address.street, address.city, address.state. The dot notation preserves the relationship between the parent and child keys.

For deeply nested structures (three or more levels), the column names can become unwieldy: order.shipping.address.zipCode. At some point, you may need to decide which levels of nesting to include and which to exclude based on what the CSV consumer actually needs.

Handling Arrays

Arrays are the most challenging element to convert because CSV has no native way to represent a list of values within a single cell.

Array of Primitive Values

If a field contains an array of simple values like ["red", "blue", "green"], common approaches include:

Join into a single cell: Concatenate the values with a delimiter like a semicolon or pipe character. The cell would contain red;blue;green. This preserves all data in one row but makes the array harder to work with in a spreadsheet.

Create numbered columns: Generate colors.0, colors.1, colors.2 columns. This keeps values in separate cells but produces a variable number of columns depending on the longest array in your dataset.

Array of Objects

When a field contains an array of objects, the complexity increases significantly.

{
  "order_id": 1001,
  "items": [
    { "product": "Widget", "qty": 3 },
    { "product": "Gadget", "qty": 1 }
  ]
}

Row duplication: Create one row per array element, repeating the parent data. This produces two rows, both with order_id 1001, one for Widget and one for Gadget. This is the most spreadsheet-friendly approach but increases row count.

Numbered columns: Create items.0.product, items.0.qty, items.1.product, items.1.qty. This keeps one row per record but produces many columns and breaks when array lengths vary.

Serialized cell: Store the entire array as a JSON string within the cell: [{"product":"Widget","qty":3},{"product":"Gadget","qty":1}]. This preserves the data but isn’t usable without further processing.

The right choice depends on how the CSV will be consumed. Row duplication works best for database imports. Numbered columns suit fixed-length arrays. Serialized cells work when the consumer can parse JSON from within a cell.

Dealing with Missing and Null Fields

JSON records within the same collection often have different fields. One record might include a middleName field while another omits it entirely. A third might include it but set it to null.

In CSV, every row must have the same number of columns. The converter needs to scan all records to build a complete column list, then fill in empty values for any record missing a particular field.

Decide how to represent missing data:

• Empty string: The cell is blank. This is the most common approach and works well in spreadsheets.
• Literal “null”: The cell contains the text “null.” This distinguishes between “field was present but empty” and “field was absent,” but can confuse users who think “null” is actual data.
• Custom placeholder: Some systems use “N/A” or a specific sentinel value. Only use this if the downstream system expects it.

Special Character Handling

CSV files use commas as delimiters (or semicolons in some locales) and newlines as row separators. When your JSON data contains these characters within field values, they need to be escaped to prevent the CSV structure from breaking.

Commas in values: Any field value containing a comma must be wrapped in double quotes. "Springfield, IL" stays intact as one field because the quotes tell the parser to treat the comma as literal text.

Quotes in values: If a field value contains double quotes, each quote must be doubled. The value He said "hello" becomes "He said ""hello""" in CSV.

Newlines in values: Multi-line text fields (like descriptions or comments) contain newline characters. These must also be wrapped in quotes so the parser doesn’t interpret the newline as a row break.

Unicode characters: Most modern CSV parsers handle UTF-8 encoding, but some older tools expect ASCII. If your JSON contains international characters, emoji, or special symbols, verify that the target application handles them correctly.

Common Conversion Pitfalls

Inconsistent data types: A field might be a number in one record and a string in another. JSON allows this, but CSV columns typically expect consistent types. When importing into a database or spreadsheet, mixed types can cause errors.

Extremely large files: Converting a multi-gigabyte JSON file to CSV in memory will fail. For large files, use a streaming approach that processes one record at a time rather than loading the entire file.

Loss of data types: JSON distinguishes between numbers, strings, booleans, and null. CSV stores everything as text. The number 42, the string “42”, and the boolean true all become plain text in CSV. If type information matters, document the expected types separately.

Column order: JSON objects are technically unordered (though most implementations preserve insertion order). CSV columns have a fixed order. If you need a specific column arrangement, define it explicitly rather than relying on the order keys appear in the JSON.

Use the Tool

Convert your JSON data to CSV instantly with our JSON to CSV Converter. Paste your JSON, and the tool handles flattening, escaping, and formatting automatically.

Frequently Asked Questions

Can I convert nested JSON with more than two levels of depth?

Yes, but the result becomes increasingly wide. Each level of nesting adds a prefix to the column name (e.g., order.customer.address.city). For deeply nested data, consider extracting only the fields you need rather than converting the entire structure. Most practical CSV use cases only need two to three levels of depth.

What happens to JSON arrays with different lengths across records?

The converter creates enough columns to accommodate the longest array in the dataset. Records with shorter arrays have empty cells for the extra columns. If one record has 2 items and another has 5, the CSV will have columns for all 5, and the first record will have 3 blank item columns.

Should I use a comma or semicolon as the CSV delimiter?

This depends on your locale and target application. Most English-speaking countries and international standards use commas. Several European countries use semicolons because their number formatting uses commas as decimal separators (e.g., 1.234,56 instead of 1,234.56). Check what your spreadsheet or database expects and configure accordingly.

How do I handle JSON data that includes HTML or rich text?

HTML tags within JSON string values will appear as literal text in CSV cells. If you open the CSV in a spreadsheet, the cells will show the raw HTML (like <p>Hello</p>) rather than rendering it. Strip HTML tags before conversion if you want clean text in your spreadsheet. If you need to preserve the HTML for later processing, ensure the values are properly quoted so that angle brackets and special characters don’t interfere with the CSV structure.

Can I convert CSV back to JSON after editing?

Yes. The reverse conversion (CSV to JSON) is generally simpler because CSV is already flat. Each row becomes a JSON object, and each column header becomes a key. However, if the original JSON had nested structures that were flattened during conversion, you will need to unflatten the dot-notation column names back into nested objects, which requires additional processing logic.