You have a JSON file from an API, a database export, or a no-code tool, and you need it in a spreadsheet without breaking dates, IDs, nested fields, or arrays. Or you have a CSV from Excel and need clean JSON for an app, automation, or developer handoff. This guide shows the practical choices that matter so you can convert JSON to CSV and back without creating a file that looks right but fails later.
Understand the shape of your data before converting
JSON and CSV store data differently, so the first step is not clicking “convert.” It is checking the structure.
CSV is a table. It works best when every row has the same columns:
```csv id,name,email,plan 101,Ava Lee,[email protected],Pro 102,Noah Kim,[email protected],Free ```
JSON can be a simple list of objects, which maps cleanly to CSV:
```json [ { "id": 101, "name": "Ava Lee", "email": "[email protected]", "plan": "Pro" }, { "id": 102, "name": "Noah Kim", "email": "[email protected]", "plan": "Free" } ] ```
That kind of JSON is easy. Each object becomes one CSV row, and each key becomes a column.
The problems start with nested objects and arrays:
```json [ { "id": 101, "name": "Ava Lee", "billing": { "city": "Austin", "country": "US" }, "tags": ["trial", "newsletter"] } ] ```
A CSV cell cannot naturally contain a nested object or a list unless you flatten it. In practice, you have three choices:
For spreadsheet work, option 1 is usually best. For importing into another system, option 3 is often cleaner because it preserves relationships better.
Before converting, open the JSON in a text editor and check the first few records. Look for nested `{}` objects, arrays `[]`, missing fields, long ID numbers, dates, and values that contain commas or line breaks.
How to convert JSON to CSV cleanly
If your JSON is an array of objects, the conversion process is straightforward.
A typical JSON-to-CSV workflow looks like this:
For example, this JSON:
```json [ { "order_id": "00081234", "customer": "Mia Chen", "total": 49.95, "created_at": "2026-01-14T09:30:00Z", "shipping": { "city": "Denver", "state": "CO" } } ] ```
Should become something like this:
```csv order_id,customer,total,created_at,shipping.city,shipping.state 00081234,Mia Chen,49.95,2026-01-14T09:30:00Z,Denver,CO ```
Notice that `order_id` is stored as `"00081234"` in JSON. The quotes matter because the leading zero is part of the value. If Excel opens that CSV and turns it into `81234`, your order ID has changed. To avoid this, import the CSV into Excel rather than double-clicking it.
In Excel, use:
In Google Sheets, use:
If your JSON includes long numeric IDs, such as social IDs, SKU codes, tracking numbers, or transaction references, treat them as text. Spreadsheet software may round long numbers or display them in scientific notation. Once that happens, the original value may be lost.
Recommended CSV settings
Use these settings unless the target system says otherwise:
A pipe separator is safer than a comma inside array cells. For example:
```csv id,tags 101,trial|newsletter|vip ```
If you use commas inside that cell, you must quote the whole value:
```csv id,tags 101,"trial, newsletter, vip" ```
That is valid CSV, but it is easier to mishandle later.
How to convert CSV back to JSON
CSV-to-JSON conversion is easier if the CSV has clear headers and consistent rows. The first row should contain field names, not labels written for humans.
Good headers:
```csv id,email,first_name,last_name,created_at ```
Problematic headers:
```csv User ID,Email Address,First Name,Last Name,Date Created ```
The second version can still work, but spaces and capitalization often become annoying in code. If the JSON will be used in an API, automation, or JavaScript app, use lowercase keys with underscores or camelCase:
```json { "first_name": "Ava" } ```
or:
```json { "firstName": "Ava" } ```
Pick one style and keep it consistent.
To convert a spreadsheet-style file into JSON, first save or export it as CSV. In Excel:
Then convert the CSV. A quick way is to use CSV to JSON when you need a browser-based conversion without setting up scripts or installing a package.
A clean CSV like this:
```csv id,name,active,signup_date 101,Ava Lee,true,2026-01-14 102,Noah Kim,false,2026-01-15 ```
Can become JSON like this:
```json [ { "id": "101", "name": "Ava Lee", "active": "true", "signup_date": "2026-01-14" }, { "id": "102", "name": "Noah Kim", "active": "false", "signup_date": "2026-01-15" } ] ```
Many converters keep all CSV values as strings because CSV does not define data types. That is not wrong. It is often safer.
If you need real JSON booleans and numbers, you may want:
```json [ { "id": 101, "name": "Ava Lee", "active": true, "signup_date": "2026-01-14" } ] ```
Be careful here. Convert `active` to boolean if the receiving app expects `true` or `false` without quotes. Convert `total`, `quantity`, or `price` to numbers if they will be used for calculations. Keep IDs, ZIP codes, phone numbers, invoice numbers, and tracking numbers as strings.
A simple rule works well: if you will do math with it, make it a number. If it identifies something, keep it as text.
Handling nested JSON, arrays, and messy files
Nested JSON is the main reason conversions go wrong. Here are practical ways to deal with common structures.
Nested objects
Original JSON:
```json { "id": 101, "customer": { "name": "Ava Lee", "email": "[email protected]" } } ```
Flattened CSV:
```csv id,customer.name,customer.email 101,Ava Lee,[email protected] ```
This is usually the best format for editing in a spreadsheet. When converting back to JSON, decide whether you need to rebuild the nested structure. Some systems accept flat keys like `customer.email`; others require:
```json { "id": 101, "customer": { "name": "Ava Lee", "email": "[email protected]" } } ```
If the target system expects nested JSON, a basic CSV-to-JSON converter may not rebuild it automatically. You may need a tool or script that treats dots in headers as nesting instructions.
Arrays of simple values
Original JSON:
```json { "id": 101, "tags": ["trial", "newsletter"] } ```
CSV option:
```csv id,tags 101,trial|newsletter ```
This is easy for humans to edit, but you must split the field back into an array later. Use a delimiter that will not appear in the values. I usually choose `|` for tags, categories, and labels.
Avoid semicolons if your data comes from regions or tools that use semicolons as CSV delimiters.
Arrays of objects
Original JSON:
```json { "order_id": "A1001", "items": [ { "sku": "PEN-BLUE", "qty": 2 }, { "sku": "NOTE-A5", "qty": 1 } ] } ```
Do not squeeze this into one row unless the target is very simple. A better approach is two CSV files.
Orders:
```csv order_id,customer,total A1001,Ava Lee,12.50 ```
Order items:
```csv order_id,sku,qty A1001,PEN-BLUE,2 A1001,NOTE-A5,1 ```
The shared `order_id` connects the files. This is easier to validate, import, and troubleshoot.
Missing fields
JSON objects do not always have the same keys:
```json [ { "id": 101, "email": "[email protected]" }, { "id": 102, "phone": "555-0100" } ] ```
The CSV should include all discovered columns:
```csv id,email,phone 101,[email protected], 102,,555-0100 ```
Blank cells are normal. Do not shift values left to “fill gaps.” That destroys the row structure.
Common mistakes and how to fix them
Mistake 1: Opening CSV by double-clicking it
Double-clicking lets Excel guess data types. That can remove leading zeros, change date formats, or round long numbers.
Fix: import the CSV using Excel’s Data > From Text/CSV option and set sensitive columns to Text before loading.
Mistake 2: Not escaping commas, quotes, and line breaks
This CSV is broken:
```csv id,note 101,Customer said "send to office, not home" ```
The comma inside the note may be treated as a separator. The quotes are also not escaped.
Correct version:
```csv id,note 101,"Customer said ""send to office, not home""" ```
If a field contains a comma, quote, or line break, wrap it in double quotes. Inside quoted fields, double any internal quote marks.
Mistake 3: Treating every value as a number
Values like `00123`, `08001`, and `123456789012345678` should usually be strings. JSON allows both numbers and strings, but spreadsheets are not careful with long or zero-padded values.
Fix: keep identifiers in quotes in JSON:
```json { "zip": "08001", "account_id": "123456789012345678" } ```
Mistake 4: Mixing date formats
A CSV with these values in the same column will cause trouble:
```csv created_at 01/02/2026 2026-01-02 2 Jan 2026 ```
Use one format. For data exchange, `YYYY-MM-DD` is safer for dates:
```csv 2026-01-02 ```
For timestamps, use ISO-style UTC if your system supports it:
```csv 2026-01-02T14:30:00Z ```
Do not let a spreadsheet silently change `2026-01-02T14:30:00Z` into a local date display unless that is what you intend.
Mistake 5: Forgetting character encoding
If names like `José`, `Zoë`, or `Müller` turn into strange characters, the file was probably read with the wrong encoding.
Fix: export as CSV UTF-8. If you are using a code editor, save the file as UTF-8 without changing delimiters or quotes.
Mistake 6: Converting a JSON object instead of an array
This JSON is a single object:
```json { "id": 101, "name": "Ava Lee" } ```
Many converters expect an array:
```json [ { "id": 101, "name": "Ava Lee" } ] ```
If your converter says “invalid format” or creates strange headers, wrap the object in square brackets if you want a one-row CSV.
A practical checklist before sending or importing the file
Before you hand off a converted file, spend two minutes checking it. This prevents most import failures.
For JSON to CSV:
For CSV to JSON:
If an import fails, read the exact error line if the system gives one. A failure at line 248 often means the problem is on that row or the row just before it. Look for an unclosed quote, an unexpected comma, a line break inside a field, or a different number of columns than the header row.
Wrap-up
JSON is flexible and CSV is flat, so the safest conversion depends on the shape of your data. Simple arrays of objects can become CSV directly; nested objects should be flattened; arrays of objects are usually better as separate related CSV files. Going back from CSV to JSON is straightforward if your headers are clean and you decide carefully which values should remain strings.
If you have a prepared CSV and need JSON output quickly, try the BestAIFinds CSV to JSON tool and review the first few records before using the file in an app or import workflow.