jsoncsvexcelgoogle-sheetsdata-conversionspreadsheet

How to Convert JSON to CSV for Excel and Google Sheets

JSON Tools Team
7 min read

JSON is the dominant format for APIs, databases, and web applications, but when it comes time to analyze data, share it with non-technical stakeholders, or import it into a reporting tool, you almost always need a spreadsheet. That means you need CSV.

CSV (Comma-Separated Values) is the universal interchange format for tabular data. Every spreadsheet application — Microsoft Excel, Google Sheets, LibreOffice Calc, Apple Numbers — can open a CSV file instantly. The challenge is getting your JSON data into that flat, row-and-column structure without losing information or breaking the import.

In this guide, we will cover exactly how JSON to CSV conversion works, walk through the process step by step, show you code examples for common scenarios, and help you avoid the pitfalls that cause broken spreadsheets and missing data.

What Is JSON to CSV Conversion?

JSON to CSV conversion is the process of transforming hierarchical, nested JSON data into a flat, two-dimensional table. In a CSV file, the first row typically contains column headers (field names), and each subsequent row represents a single record. Every value is separated by a comma, and text values that contain commas or line breaks are enclosed in double quotes.

Here is a simple example. Given this JSON array:

[
  {
    "id": 1,
    "name": "Alice Johnson",
    "email": "alice@example.com",
    "department": "Engineering"
  },
  {
    "id": 2,
    "name": "Bob Smith",
    "email": "bob@example.com",
    "department": "Marketing"
  },
  {
    "id": 3,
    "name": "Carol Lee",
    "email": "carol@example.com",
    "department": "Engineering"
  }
]

The corresponding CSV output looks like this:

id,name,email,department
1,Alice Johnson,alice@example.com,Engineering
2,Bob Smith,bob@example.com,Marketing
3,Carol Lee,carol@example.com,Engineering

The conversion is straightforward when your JSON is a flat array of objects with consistent keys. It gets more interesting when you have nested objects, arrays within records, or inconsistent fields across records — scenarios we will address later in this guide.

Why Convert JSON to CSV for Spreadsheets?

Data Analysis and Reporting

Spreadsheets are still the most widely used tool for data analysis. Pivot tables, charts, conditional formatting, and formulas are all built around the row-and-column model. Converting your JSON API responses or database exports to CSV lets you leverage these powerful features without writing code.

Sharing with Non-Technical Teams

Product managers, sales teams, finance departments, and executives are far more comfortable with Excel than with raw JSON. Converting data to CSV before sharing it removes friction and ensures everyone can work with the data immediately.

Bulk Data Import

Many SaaS platforms, CRM systems, and databases support CSV import for bulk data loading. If your source data is in JSON format — from an API export, a MongoDB dump, or a webhook payload — converting it to CSV is often the fastest path to getting it into your target system.

Archival and Compliance

CSV files are plain text, human-readable, and will remain openable for decades. For regulatory compliance and long-term archival, CSV is often preferred over proprietary formats because it has no vendor lock-in.

How to Convert JSON to CSV Online: Step by Step

The quickest way to convert JSON to CSV is with an online tool. Here is how to do it with our JSON to CSV Converter:

  1. Validate your JSON first. Before converting, make sure your JSON is valid. Paste it into the JSON Validator to catch any syntax errors like missing commas or unquoted keys.
  2. Open the converter. Navigate to the JSON to CSV tool in your browser.
  3. Paste your JSON. Copy your JSON array into the input panel. The tool accepts arrays of objects, which is the most common structure for tabular data.
  4. Click "Convert". The tool parses your JSON, extracts all unique keys as column headers, and generates the CSV output instantly.
  5. Download or copy. Copy the CSV to your clipboard or download it as a .csv file. Open it in Excel or Google Sheets, and your data will appear neatly in rows and columns.

The entire process takes seconds, and your data never leaves your browser — all processing happens client-side.

Handling Nested JSON Structures

Real-world JSON is rarely flat. APIs commonly return nested objects and arrays. The key question is: how do you flatten nested data into CSV columns?

Dot Notation for Nested Objects

The most common approach is to use dot notation to create column names from the nesting path. Consider this JSON:

[
  {
    "id": 1,
    "name": "Alice Johnson",
    "address": {
      "street": "123 Main St",
      "city": "Springfield",
      "state": "IL"
    },
    "skills": ["Python", "SQL", "Tableau"]
  },
  {
    "id": 2,
    "name": "Bob Smith",
    "address": {
      "street": "456 Oak Ave",
      "city": "Portland",
      "state": "OR"
    },
    "skills": ["JavaScript", "React"]
  }
]

A flattened CSV using dot notation would look like this:

id,name,address.street,address.city,address.state,skills
1,Alice Johnson,123 Main St,Springfield,IL,"Python, SQL, Tableau"
2,Bob Smith,456 Oak Ave,Portland,OR,"JavaScript, React"

Notice two things: the nested address object has been expanded into three columns (address.street, address.city, address.state), and the skills array has been joined into a single comma-separated string enclosed in double quotes. This is a pragmatic compromise — you preserve the data while keeping it importable into spreadsheets.

When to Flatten vs. When to Separate

Not all nested structures should be flattened into a single CSV. If your JSON contains a one-to-many relationship (for example, an order with multiple line items), you have two options:

  • Flatten into one row per parent record. Join child arrays into delimited strings within a single cell. This works for simple cases like tags or skills.
  • Expand into one row per child record. Create a separate row for each child item, repeating the parent data. This is better when you need to analyze child-level data individually — for example, calculating revenue per line item.

Converting JSON to CSV in Code

If you need to automate JSON to CSV conversion in your application, here are practical examples in JavaScript and Python.

JavaScript / Node.js

function jsonToCsv(jsonArray) {
  if (!jsonArray.length) return '';

  // Extract headers from all objects to handle inconsistent keys
  const headers = [...new Set(
    jsonArray.flatMap(obj => Object.keys(obj))
  )];

  // Build header row
  const csvRows = [headers.join(',')];

  // Build data rows
  for (const obj of jsonArray) {
    const values = headers.map(header => {
      const val = obj[header];
      if (val === null || val === undefined) return '';
      if (typeof val === 'object') return '"' + JSON.stringify(val).replace(/"/g, '""') + '"';
      const str = String(val);
      // Wrap in quotes if value contains comma, newline, or quote
      if (str.includes(',') || str.includes('\n') || str.includes('"')) {
        return '"' + str.replace(/"/g, '""') + '"';
      }
      return str;
    });
    csvRows.push(values.join(','));
  }

  return csvRows.join('\n');
}

// Usage
const data = [
  { id: 1, name: "Alice", role: "Engineer" },
  { id: 2, name: "Bob", role: "Designer" },
  { id: 3, name: "Carol", role: "Manager" }
];

console.log(jsonToCsv(data));

This function handles the three most common edge cases: missing keys across records, nested objects (serialized as JSON strings), and values that contain commas or quotes. For production use, consider a library like papaparse or csv-stringify which handle additional edge cases like Unicode BOM markers and custom delimiters.

Python

import json
import csv
import io

def json_to_csv(json_array):
    if not json_array:
        return ''

    # Collect all unique keys preserving order
    headers = []
    seen = set()
    for obj in json_array:
        for key in obj.keys():
            if key not in seen:
                headers.append(key)
                seen.add(key)

    output = io.StringIO()
    writer = csv.DictWriter(output, fieldnames=headers, extrasaction='ignore')
    writer.writeheader()

    for obj in json_array:
        # Convert nested structures to JSON strings
        row = {}
        for key in headers:
            val = obj.get(key, '')
            if isinstance(val, (dict, list)):
                row[key] = json.dumps(val)
            else:
                row[key] = val
        writer.writerow(row)

    return output.getvalue()

# Usage
with open('data.json', 'r') as f:
    data = json.load(f)

csv_output = json_to_csv(data)
with open('output.csv', 'w', newline='') as f:
    f.write(csv_output)

print("CSV file created successfully")

Python's built-in csv module handles quoting and escaping automatically via the DictWriter class. The extrasaction='ignore' parameter prevents errors when individual records have extra keys not in the header list.

Importing CSV into Excel and Google Sheets

Microsoft Excel

  1. Open Excel and go to File > Open, then select your .csv file.
  2. Excel may launch the Text Import Wizard. Choose "Delimited" and click Next.
  3. Check "Comma" as the delimiter. If your data uses semicolons (common in European locales), check "Semicolon" instead.
  4. Set column data formats. Choose "Text" for columns that contain leading zeros (like ZIP codes or product IDs) to prevent Excel from converting them to numbers.
  5. Click Finish. Your JSON data is now in a spreadsheet.

Tip: If your CSV contains UTF-8 characters (accented letters, CJK characters, emojis), save it with a UTF-8 BOM (byte order mark) to ensure Excel reads the encoding correctly. In Node.js, prepend "\uFEFF" to your CSV string before writing the file.

Google Sheets

  1. Open Google Sheets and go to File > Import.
  2. Upload your .csv file or select it from Google Drive.
  3. In the import dialog, set the separator type to "Comma" (or "Detect automatically").
  4. Choose whether to create a new spreadsheet, insert new sheets, or replace existing data.
  5. Click "Import data." Google Sheets handles UTF-8 natively, so encoding is rarely an issue.

Common Mistakes When Converting JSON to CSV

1. Forgetting to Handle Null and Missing Values

Not every object in a JSON array will have the same keys. If your converter only looks at the first object to determine headers, you will miss columns that only appear in later records. Always scan all records to build a complete header list, and output empty strings for missing values.

2. Not Escaping Commas and Quotes in Values

A value like "Acme, Inc." will break your CSV if it is not wrapped in double quotes. And a value containing double quotes must have those quotes escaped by doubling them: "She said ""hello""". Most CSV libraries handle this automatically, but if you are building a converter by hand, this is the most common source of corrupted output.

3. Losing Nested Data

If your JSON contains nested objects or arrays and you simply call toString() on them, you will get [object Object] in your CSV — useless garbage. Serialize nested structures with JSON.stringify() or flatten them with dot notation as described earlier.

4. Ignoring Data Types in Excel

Excel aggressively interprets cell values. A string like "001234" becomes the number 1234. A date-like string such as "3/4" becomes March 4th. And long numeric strings like phone numbers lose precision. To prevent this, either format your CSV to force text mode (prefix with an equals sign and quotes: ="001234") or instruct Excel to import columns as text during the Import Wizard.

Best Practices for JSON to CSV Conversion

  • Validate your JSON before converting. Use a JSON Validator to catch syntax errors. A converter cannot produce correct CSV from broken JSON.
  • Format your JSON first for inspection. Before converting a large dataset, format your JSON to visually inspect its structure. Understanding the nesting depth and field names helps you anticipate how the CSV will look.
  • Use UTF-8 encoding with BOM for Excel. This prevents encoding issues with special characters and non-Latin scripts.
  • Test with a small sample first. Before converting a 50,000-record JSON file, test with the first 5-10 records to verify the column structure and data formatting are correct.
  • Choose the right delimiter for your locale. Many European countries use semicolons instead of commas as CSV delimiters, because commas are used as decimal separators. Match the delimiter to your audience's locale settings.
  • Document your flattening strategy. If you flatten nested JSON, document how nested keys map to CSV columns (e.g., address.city maps to the city column). This prevents confusion when others consume the CSV.

Convert Your JSON to CSV Now

Ready to turn your JSON data into a spreadsheet? Paste your JSON into our free JSON to CSV Converter and download a clean CSV file in seconds. The tool runs entirely in your browser — your data stays private and never hits a server.

For best results, start by running your data through the JSON Formatter to inspect its structure, then convert it to CSV with confidence.

Conclusion

Converting JSON to CSV bridges the gap between modern APIs and the spreadsheet tools that billions of people rely on every day. Whether you are exporting analytics data for a quarterly report, preparing a customer list for a mail merge, or migrating records between systems, understanding how to reliably transform JSON into clean CSV output is an essential skill.

The key takeaways: validate your JSON first, handle nested structures deliberately, escape special characters properly, and be mindful of how Excel interprets data types. Follow these practices, and your spreadsheet imports will be clean every time.