JSON to CSV Converter

JSON to CSV — Transform JSON Arrays into Spreadsheet-Ready Data

JSON is the dominant data format for web APIs, databases, and application communication. But when you need to analyze that data in Excel, Google Sheets, or any spreadsheet application, import it into a database, or share it with non-technical stakeholders who work with CSV files, you need to convert JSON to CSV. Manually copying data from JSON responses into spreadsheets is tedious and error-prone — especially with large datasets or nested structures.

Our free JSON to CSV converter handles arrays of JSON objects and produces clean, standards-compliant CSV output. It automatically detects all unique keys across your JSON records, handles missing fields gracefully, and optionally flattens nested objects using dot notation. The output is ready to open directly in Excel, Google Sheets, or any CSV-compatible application.

How the Converter Handles Real-World JSON

JSON data from APIs and databases rarely comes in a perfectly flat structure. Objects may have nested properties, arrays of values, or inconsistent fields across records. Our converter addresses these common scenarios: it flattens nested objects using dot notation (e.g., address.city), collects all unique keys from across all records to build the header row, and fills in empty cells when a record is missing a field that other records have.

The delimiter is always comma for CSV output, and values containing commas, newlines, or double quotes are automatically quoted following RFC 4180 standards. This ensures the output is valid CSV that opens correctly in any spreadsheet application. The converter also handles Unicode strings, numbers, booleans, and null values — each is serialized in the way spreadsheet applications expect.

Flattening Nested JSON Structures

Real-world API responses often nest data several levels deep. A user record might look like {"name":"John","address":{"city":"New York","zip":"10001"},"settings":{"theme":"dark"}}. Without flattening, this data simply can't fit into a flat CSV row. With the "Flatten nested objects" option enabled, the converter produces columns like address.city, address.zip, and settings.theme — preserving the hierarchical relationship in the column name while keeping the data in a spreadsheet-compatible format.

This dot-notation approach is the industry standard for representing nested data in flat formats. It's the same convention used by Elasticsearch, JSON flattening libraries in Python and JavaScript, and most ETL tools. The column names are immediately meaningful: address.city tells you exactly where that value came from.

When nesting goes deeper than two levels, the column names get longer but remain readable. A structure like {"company":{"department":{"team":{"lead":{"name":"Alice"}}}}} produces company.department.team.lead.name. In a spreadsheet with 30 or 40 columns, these long names can make column headers unwieldy. A practical workaround is to rename the columns after conversion — give them short, meaningful names like "Team Lead" instead of the full dot path. Most spreadsheet applications let you edit header rows with a few clicks.

There are trade-offs with flattening strategies. The dot-notation approach works well for objects, but what about deeply nested arrays? A structure like {"tags": [{"id":1,"label":"urgent"},{"id":2,"label":"billing"}]} presents a real challenge. The converter serializes the array as a JSON string in the cell. If you need each tag as a separate column, you'd need to preprocess the data — either with a script or by restructuring the JSON before pasting it in. This is an inherent limitation of CSV's flat, two-dimensional nature meeting JSON's recursive tree structure.

Handling Arrays and Missing Fields

What happens when a JSON field contains an array like ["red","blue","green"]? Arrays don't fit neatly into a single CSV cell, so the converter serializes them as a string representation. You'll see ["red","blue","green"] in the cell. If you need those values spread across multiple columns, you'll need to preprocess the data or flatten the structure before converting.

There are several approaches to handling arrays depending on your use case. The simplest is the serialized string — you get the array as-is in a single cell, which works fine if you don't need to sort or filter by individual array elements. A more advanced approach is to join array elements with a delimiter: red | blue | green using a pipe or semicolon. This keeps the data in one cell but makes it easier to split later in a spreadsheet. The most complex approach is to explode each array element into its own column (tags.0, tags.1, tags.2), but this only works when all arrays have a known maximum length.

Missing fields are another common scenario. Not every record in a JSON array has the same keys — one user might have a phone field while another doesn't. The converter builds its header row from every unique key across all records, then leaves cells empty where a record doesn't have that key. This keeps the column alignment correct throughout the spreadsheet without losing or inventing data.

Column Naming Conventions for Flattened Data

The column names in your CSV output directly affect how usable the spreadsheet is. Poorly named columns — data.items.0.name — are technically correct but painful to work with. Good naming conventions make the difference between a CSV you can share with your team and one that requires a decoder ring.

Stick with dot notation for the hierarchy: address.city is universally understood. Avoid brackets or special characters in column names — they can confuse some CSV parsers and spreadsheet formulas. Keep names concise but descriptive. user.email is better than u.e, which is better than user.contact_information.email_address.

If you're preparing the JSON before conversion, take a moment to simplify the structure. Rename verbose keys to shorter equivalents. Flatten one level of nesting if it makes the column names more readable. The work you put in before conversion pays dividends in the usability of the resulting spreadsheet.

Choosing Which Fields to Export

Sometimes you don't need every field. API responses often include metadata, internal IDs, timestamps, and nested objects that are irrelevant for a spreadsheet report. If you want to export only specific fields, consider using a JSON editor or code to filter the array before pasting it into the converter. Strip out the fields you don't need in advance, and you'll get a cleaner CSV with only the columns that matter. This also reduces file size and makes the spreadsheet easier to work with.

For large API responses with hundreds of records and dozens of fields, pre-filtering is especially valuable. A 500-record response with 45 fields produces a CSV with 22,500 cells. If you only need 5 of those fields, that's 2,500 cells — a much more manageable dataset. A quick script in your browser's console can filter the JSON: data.map(r => ({name: r.name, email: r.email, city: r.address.city})) gives you exactly the columns you need.

Character Encoding and Delimiter Selection

CSV files can have encoding issues when data contains non-ASCII characters — accented letters, Chinese characters, emoji, or special symbols. The converter outputs UTF-8 encoded CSV, which is the most widely supported encoding for multilingual data. When you open the output in Excel on Windows, you may occasionally see garbled characters if Excel defaults to a different encoding. In that case, use Excel's "From Text/CSV" import wizard and explicitly select UTF-8 encoding during the import process.

The default CSV delimiter is the comma, but this isn't always the best choice. In European locales where the comma serves as the decimal separator (1,5 instead of 1.5), comma-delimited CSV creates ambiguity between field separators and numeric values. Semicolon-delimited files solve this problem and are the default in many European spreadsheet applications. Tab-delimited values (TSV) work well when your data contains commas in text fields — tabs virtually never appear in natural text, so they don't conflict with the content.

This converter outputs comma-delimited CSV, which is the universal standard. If you need semicolons or tabs, a find-and-replace in a text editor after conversion is the fastest path. The converter's RFC 4180 quoting ensures values with embedded commas are properly wrapped in double quotes, so the output remains valid regardless of what's inside each field.

Practical Tips for Clean Conversions

Before pasting JSON into the converter, validate it. Malformed JSON — missing commas, trailing commas, unquoted keys — will cause a parse error. Browser developer tools have a built-in validator: paste the JSON into the console and see if it throws an error. Online JSON validators like jsonlint.com catch formatting issues instantly.

Pay attention to null values in your JSON. A field like "middle_name": null should become an empty cell in the CSV. Some datasets use "N/A", "null" (as a string), or an empty string "" instead of actual JSON null. These all produce different CSV output — understanding which representation your source uses helps you anticipate what the spreadsheet will look like.

Finally, check the header row after conversion. The converter builds headers from all unique keys across all records, which means the order might not match what you expect. Rearrange columns in the spreadsheet to put the most important fields first — ID, name, date — before less critical metadata like timestamps or internal references.

Common Pitfalls and How to Avoid Them

Single objects instead of arrays: If your JSON is a single object like {"name":"John","age":30}, the converter needs it wrapped in an array: [{"name":"John","age":30}]. Most APIs return arrays, but some return a single object for individual resource lookups.

Nested arrays at the top level: An array like ["apple","banana","cherry"] contains primitives, not objects. The converter expects objects with named keys. Wrap each value manually or use a script to transform the structure before converting.

Extremely deep nesting: JSON five or six levels deep produces column names like a.b.c.d.e.f that are unwieldy in spreadsheets. Flatten the JSON structurally before conversion, or accept that some columns will have long names that need manual renaming.

Frequently Asked Questions