JSON to CSV (and Back): Flattening Nested Data Correctly

Someone sends you an API export and asks for it "in Excel". Someone else sends you a spreadsheet and asks you to load it into a service that only accepts JSON. Both requests sound trivial, and both go wrong in quiet ways: addresses vanish into a single cell, a postal code loses its leading zero, a column appears in one row and not the next, and a comment field containing a comma shifts every value to its right. This guide is for developers, analysts and anyone who moves data between APIs and spreadsheets. You will learn why tabular and hierarchical data are different shapes, what the CSV rules really say, four ways to flatten nested JSON, how to get back to JSON without corrupting types, and which security and encoding traps to avoid. Every code example here was run before it was published, and the output is shown.

Two shapes of data

A spreadsheet is a grid. Every row has the same columns, every cell holds one value, and position carries meaning: the third cell in a row belongs to the third column. JSON is a tree. An object can contain objects, which can contain arrays of objects, to any depth, and two sibling objects in the same array may have completely different keys.

Converting between them is therefore not a format change like converting a PNG to a JPEG. It is a change of data model. Going from a tree to a grid means deciding how to represent depth, repetition and absence. Going from a grid to a tree means guessing the structure and the types that the grid threw away.

Three questions come up in every conversion, and the rest of this article is largely about answering them well:

1. How do I represent nesting? A grid has no place to put an object inside a cell, so you either encode the path in the column name, repeat parent values on extra rows, or store a JSON string in the cell.
2. What happens when records disagree? The grid needs one fixed list of columns, but the records may have different keys.
3. What happens to types? JSON distinguishes numbers, strings, booleans and null. CSV distinguishes nothing. Every cell is text until some program decides otherwise.

If you only need a quick conversion, the DevUtilX JSON to CSV converter handles the common cases in your browser. Understanding the questions above will help you choose its options and interpret its output.

What CSV is, and a short history

Comma-separated values is one of the oldest data interchange formats still in daily use. Lists of fields separated by commas were already being read by mainframe-era tools long before personal computers, because the format is trivial to produce: print the values, put a comma between them, end the line. Spreadsheet programs and database tools adopted it as a lowest-common-denominator import and export format, and each added its own small variations.

Those variations were the problem. For decades there was no authoritative description, so "CSV" meant whatever the program you were using happened to read and write. In 2005 the IETF published RFC 4180, "Common Format and MIME Type for Comma-Separated Values (CSV) Files". It is an informational document, not a standards-track specification, and it describes the common practice rather than imposing a new rule. It also registered the text/csv media type. Treat it as the best shared reference, and treat real-world files as being only mostly compliant with it.

JSON, by contrast, grew out of the JavaScript object literal syntax in the early 2000s and was designed for exchanging structured data between a browser and a server. It is now formally described in RFC 8259 and ECMA-404. The two formats meet constantly, because APIs speak JSON and business people speak spreadsheet.

The CSV rules, according to RFC 4180

The RFC fits on a few pages. The rules that matter for conversion are these.

• One record per line. Records are separated by a line break, which the RFC defines as CRLF (carriage return plus line feed). In practice, many files use a bare LF, and most readers accept both.
• Fields are separated by commas. Spaces are part of the field. A space before or after a comma is data, not formatting.
• An optional header row may appear as the first line, with the same number of fields as the records. The RFC notes that the presence of a header can be signalled with the header parameter of the media type, but the file itself does not say so. A reader has to be told or has to guess.
• Every line should have the same number of fields. Real readers differ in how they handle short or long rows, as the troubleshooting section shows.
• Quoting. A field may be enclosed in double quotes. A field that contains a line break, a double quote or a comma must be enclosed in double quotes.
• Escaping a quote. A double quote inside a quoted field is written as two double quotes.
• The last record may or may not end with a line break.

That is the entire grammar. Notice what is missing: there is no way to say that a column is a number, no way to say that a value is null as distinct from an empty string, no date format, and no statement about character encoding. The RFC's media type has a charset parameter, and says the default is US-ASCII, which is rarely what a file actually contains today. Every one of the problems later in this article traces back to something this grammar leaves out.

A worked example of quoting

Consider a record with three fields: the number 1, the text Says "hi", then leaves with a real line break before leaves, and the text 1,5. Python's standard csv writer produces this, shown here as a Python string so the control characters are visible:

'id,note,amount\r\n1,"Says ""hi"", then\nleaves","1,5"\r\n2,=1+1,007\r\n'

Three things happened to the second field. It contains a comma, a quote and a newline, so it was wrapped in quotes. The two inner quotes were doubled. The newline stayed inside the quoted field, so the record spans two physical lines. This is why you must never parse CSV by splitting on newlines and then on commas. A quoted newline or comma will break such a parser, and the damage is silent: you get the wrong number of fields and shifted columns rather than an error.

Also look at the second record. =1+1 and 007 were written without quotes, because they contain nothing that requires quoting. Whether 007 survives as 007 depends entirely on the program that opens the file, which is the subject of a later section.

From tree to grid: four flattening strategies

Take this small, realistic input. It has a nested object two levels deep, an array of scalars, a free-text field with a quote and a newline, a postal code with a leading zero, and a null.

[
  {"id": 1, "name": "Ada Lovelace", "zip": "02134", "active": true, "score": 9.5,
   "address": {"city": "London", "geo": {"lat": 51.5, "lng": -0.12}},
   "tags": ["math", "poetry"], "note": "Says \"hello\", then\nleaves", "joined": "2026-03-14T09:30:00Z"},
  {"id": 2, "name": "Alan Turing", "zip": "00501", "active": false, "score": null,
   "address": {"city": "Wilmslow"}, "tags": [], "note": "=HYPERLINK(\"http://evil.example\",\"click\")", "extra": "only here"}
]

There are four common ways to turn this into rows and columns. None is universally right. The right one depends on who will read the file and whether you need to get the original back.

Strategy 1: dot-notation keys

Walk the tree and join the path of keys with a separator. address.geo.lat becomes a column name, and its value goes in the cell. Arrays use the index as a path segment, so tags.0 and tags.1 become two columns.

This is the most widely used approach, because it is reversible: the column name carries the full path, so a program can rebuild the tree from it. It also keeps one record per row, so a spreadsheet user can filter and sort without surprises.

Its weaknesses are width and ragged arrays. A record with 50 tags produces 50 columns, and most other rows leave 49 of them empty. Dots in real key names also cause ambiguity: a key literally named "a.b" is indistinguishable from a nested a containing b. If your keys can contain dots, pick a different separator such as / or __, or escape the dots.

Strategy 2: exploding arrays into rows

Instead of widening the table, lengthen it. Each element of an array becomes its own row, and the parent's values are repeated on every row. This suits arrays of objects, such as order line items, where each element is itself a record.

Here is a Python example, using only the standard library, that explodes order items:

import csv, sys
orders = [
  {"order": "A-1", "customer": {"name": "Ada"}, "items": [{"sku": "pen", "qty": 2}, {"sku": "ink", "qty": 1}]},
  {"order": "A-2", "customer": {"name": "Alan"}, "items": []},
]
w = csv.writer(sys.stdout, lineterminator="\n")
w.writerow(["order", "customer.name", "item.sku", "item.qty"])
for o in orders:
    items = o["items"] or [{}]          # keep orders that have no items
    for it in items:
        w.writerow([o["order"], o["customer"]["name"], it.get("sku", ""), it.get("qty", "")])

The output:

order,customer.name,item.sku,item.qty
A-1,Ada,pen,2
A-1,Ada,ink,1
A-2,Alan,,

Exploding is ideal for analysis, because a pivot table or a SQL import can sum item.qty per order directly. The cost is that the row no longer corresponds to one entity. If you count rows you count line items, not orders, and any numeric column belonging to the parent is duplicated, so summing it double-counts. Notice also the line items = o["items"] or [{}]. Without it, an order with an empty array would produce zero rows and vanish from the output. That is the single most common bug in explode logic.

If a record has two independent arrays, exploding both produces a cross product: three tags and four phone numbers give twelve rows. Explode only one array per output file, or write one file per array.

Strategy 3: JSON in a cell

Sometimes the nested part is not something the spreadsheet user needs to read. Keep it as a compact JSON string in one cell:

order,items_json
A-1,"[{""sku"":""pen"",""qty"":2},{""sku"":""ink"",""qty"":1}]"
A-2,[]

This is lossless, simple, and trivially reversible: parse the cell with JSON.parse or json.loads. It is also unfriendly. Every inner quote is doubled by the CSV quoting rule, so the cell is hard to read, and a spreadsheet user cannot filter on a field inside it. It works well for round trips between systems, and poorly for human analysis. Use compact JSON for the cell. Our guide to JSON minification and payload size explains why whitespace inside a cell is wasted bytes.

Strategy 4: pick the columns you want

The fourth strategy is not to flatten everything. Most exports serve a purpose, and the reader wants five columns, not fifty. Decide the schema first: choose which paths to include, rename them to friendly headers, and drop the rest. This is a mapping rather than a flattening, and it is what you want for reports. It is not reversible, since the dropped data is gone, so keep the original JSON if you may need it later.

Choosing between them

Strategy	Rows per record	Reversible	Best for	Main risk
Dot-notation keys	One	Yes, with care	Round trips, mostly fixed shapes	Many empty columns, dots in keys
Explode arrays	One per array element	Hard	Analysis, pivot tables, SQL import	Double counting, lost empty arrays
JSON in a cell	One	Yes	System-to-system transfer	Unreadable cells, no filtering
Selected columns	One	No	Reports for people	Dropped data

You can combine them. A common pattern is dot-notation for nested objects, JSON in a cell for a deeply irregular array, and selected columns for the final report.

A complete flatten and write routine in JavaScript

The following Node.js code implements dot-notation flattening, builds the union of columns across all records, and writes valid CSV with CRLF line endings and proper quoting. It also neutralises dangerous cell prefixes, which the security section explains.

const fs = require("fs");

function flatten(v, prefix = "", out = {}) {
  if (Array.isArray(v)) {
    if (v.length === 0) out[prefix] = "[]";
    v.forEach((x, i) => flatten(x, prefix ? `${prefix}.${i}` : String(i), out));
  } else if (v && typeof v === "object") {
    const keys = Object.keys(v);
    if (keys.length === 0 && prefix) out[prefix] = "{}";
    for (const k of keys) flatten(v[k], prefix ? `${prefix}.${k}` : k, out);
  } else out[prefix] = v;
  return out;
}

function cell(v) {
  if (v === null || v === undefined) return "";
  let s = String(v);
  if (/^[=+\-@\t\r]/.test(s) && typeof v === "string") s = "'" + s;
  return /[",\r\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
}

function toCsv(rows) {
  const flat = rows.map((r) => flatten(r));
  const cols = [];
  for (const r of flat) for (const k of Object.keys(r)) if (!cols.includes(k)) cols.push(k);
  const lines = [cols.join(",")];
  for (const r of flat) lines.push(cols.map((c) => cell(r[c])).join(","));
  return lines.join("\r\n") + "\r\n";
}

const csv = toCsv(JSON.parse(fs.readFileSync("data.json", "utf8")));
fs.writeFileSync("out.csv", "" + csv);
console.log(csv);

Running it on the sample data printed this:

id,name,zip,active,score,address.city,address.geo.lat,address.geo.lng,tags.0,tags.1,note,joined,tags,extra
1,Ada Lovelace,02134,true,9.5,London,51.5,-0.12,math,poetry,"Says ""hello"", then
leaves",2026-03-14T09:30:00Z,,
2,Alan Turing,00501,false,,Wilmslow,,,,,"'=HYPERLINK(""http://evil.example"",""click"")",,[],only here

Study this output, because it shows most of the issues in this article in a few lines.

• The header is the union of keys. extra exists only in the second record but still gets a column, and the first record leaves it empty.
• tags.0 and tags.1 come from the first record's array. The second record has an empty array, which the code writes as a literal [] in a separate tags column. That column appears late, because columns are added in order of first appearance. This is a design choice. You could instead emit nothing for empty arrays, but then an empty array and a missing field become indistinguishable.
• score: null became an empty cell. So did every missing key. The grid cannot tell "null" from "absent" from "empty string".
• The note field contains a quote, a comma and a newline, so it is quoted, its quotes are doubled and the record occupies two lines.
• The second record's note began with =, so the code prefixed an apostrophe to defuse it.
• true and false became the words true and false. Spreadsheet programs may convert those to their own boolean display, depending on locale and settings.

Heterogeneous records and the column union

Real API data is rarely uniform. Optional fields are omitted, feature flags appear only on some records, and different event types share one array. A converter has three options.

Union of all keys is what the code above does. It never loses data, but it needs two passes over the data, or a buffer, because the header must be written before the first row and you cannot know the final columns until you have seen every record. For a stream of unknown length that is a real constraint, covered in the large-files section.

First record defines the schema is cheaper, since the header can be written immediately, but silently drops keys that appear only in later records. It is acceptable only when you control the producer and know the shape is stable.

Explicit column list is the safest for repeatable exports. You declare the columns, the converter writes exactly those, and anything unexpected is either ignored or logged. Column order stays stable between runs, which matters if people have built formulas or imports on top of the file.

Column order deserves attention. With the union approach, the order depends on the order records appear, so the same data sorted differently can produce a different column order. If the output is read by humans or by another program that maps columns by position, sort the columns or declare them.

Type loss: what the grid forgets

JSON has six value types: object, array, string, number, boolean and null. CSV has one: text. When you flatten, every value becomes a string, and when you read the file back, something has to guess.

JSON value	Written to CSV as	What a reader may guess
42	42	Number
"42"	42	Number, though it was a string
"02134"	02134	Number 2134, dropping the zero
true	true	Boolean, or text, or localised "TRUE"
null	empty	Empty string, null, or missing
""	empty	Same as null
"2026-03-14"	2026-03-14	Date in the viewer's locale
1e21	1e+21	Number, or text
[]	your choice	Text

The key observation is that 42 and "42" produce identical bytes. No amount of cleverness on the reading side can recover which one the original was. Information was destroyed at the moment of writing.

You have a few ways to cope.

• Document the types. Ship a short data dictionary next to the file, or encode types in a second header row if your consumers agree to skip it.
• Use type suffixes in headers, such as zip:string and score:number, when you control both ends.
• Prefer explicit schemas when going back. Instead of inferring types from the cell contents, apply a schema that says which columns are strings. The Python example later does this for the zip column.
• Choose a convention for null. Empty cell is the most common. Some pipelines use a literal such as NULL or \N. Whatever you pick, document it, because an empty string and null mean different things in a database import.

Numbers carry a second hazard. JavaScript parses every JSON number into a double-precision float, so integers above 2^53 lose precision. In the sandbox used for this article, the literal 12345678901234567890 printed back as 12345678901234567000. If your identifiers are large integers, treat them as strings in the CSV and in the JSON, or use a parser that supports big integers.

What spreadsheets do to your data

CSV does not specify types, so spreadsheet programs apply their own when you open the file. They are trying to help, and they frequently destroy data in the process.

Leading zeros disappear. 02134 is read as the number 2134. A postal code, a phone number, an account number or a product code that begins with zero is silently changed, and if the user saves the file, the loss is permanent. The zero is not in the CSV problem. The CSV still contains 02134. It is in the spreadsheet's interpretation.

Long numbers turn into scientific notation. A sixteen-digit card number or a long identifier may be displayed as 1.23457E+15, and digits beyond the program's numeric precision are replaced by zeros when saved.

Text that looks like a date gets converted. Gene names and codes such as MAR1 or SEPT2, or values like 1-2, may become dates. This has caused well-documented problems in scientific data files, which is why some research communities rename identifiers to avoid it.

Booleans and locale-dependent numbers change. true may become TRUE. In locales where the comma is the decimal separator, 1,5 is read as one and a half, while 1.5 may be read as text or as a date.

There are three honest ways to deal with this.

1. Import, don't open. Spreadsheet programs have a text import wizard or data-import feature where you can mark columns as text. Double-clicking the file skips that step. If recipients will double-click, this advice does not help them.
2. Quote and prefix. Some producers write values as ="02134" so the spreadsheet evaluates a formula that returns the text. This keeps the zero visible in the spreadsheet, but it pollutes the file for every other consumer, so do it only if the file is meant for spreadsheet users alone.
3. Offer a second format. If the data is for people, an .xlsx file with typed cells is a better deliverable. If it is for programs, send JSON or CSV with a schema.

None of these is perfect. The important point is that the damage happens in the viewer, not in your converter, and you should tell recipients which tool you tested with.

CSV injection: when data becomes a formula

A cell that begins with =, +, - or @ may be interpreted as a formula by a spreadsheet. An attacker who can put text into a field that later gets exported, such as a username, a comment or a company name, can plant a formula that runs when a colleague opens the export. The OWASP description of CSV injection covers the consequences, including formulas that exfiltrate data from other cells or trick the user into following a link. Modern spreadsheet programs show warnings for some of these actions, but you should not depend on a user to dismiss them correctly.

The sample data above contained a hostile note, =HYPERLINK("http://evil.example","click"), to show the mitigation. The usual defence is to prefix any text cell that starts with a dangerous character by a single apostrophe or space, so the spreadsheet treats it as text. The OWASP guidance also lists the tab and carriage return characters as risky leading characters, which is why the JavaScript code earlier checks for them too.

Be aware of the trade-offs.

• The prefix changes the data. The apostrophe is visible in some programs and part of the value in others. When you convert back, you must decide whether to strip it. The Python round trip below leaves it in, which shows the cost.
• Negative numbers start with a minus. Prefix only strings, not real numbers. In the JavaScript code above, the test is limited to values whose JSON type is string. A string like -5 that was meant as text will be prefixed too, which is the safe choice for untrusted input.
• The defence belongs at export time. Sanitising at input time is not enough, because data flows in from many sources, and the same value may be exported to different formats.
• Not every consumer is a spreadsheet. If a CSV only ever feeds a database import or a script, the apostrophe is damage without benefit. Make the behaviour an option, and turn it on by default for any file a person will open.

If you generate CSV from user-supplied content, treat this as seriously as you would treat HTML escaping.

Encoding, the BOM, and Excel

The RFC says nothing useful about encoding for modern files, so you must pick one. UTF-8 is the right choice for almost everything, since it represents every character and is the default of the web.

The complication is Microsoft Excel. When you open a CSV file directly, Excel has historically guessed the encoding from the file's first bytes and, absent a hint, may fall back to a legacy code page for your system. A UTF-8 file with accented letters or non-Latin scripts can then appear as garbled sequences such as é instead of é.

The widely used workaround is to start the file with a UTF-8 byte order mark, the three bytes EF BB BF. The BOM is a zero-width character that the Unicode specification allows at the start of a UTF-8 stream, although it is not required or recommended for UTF-8 in general. The Node code earlier wrote it with "" + csv, and a check of the file confirmed the first bytes were b'\xef\xbb\xbf'.

The BOM has costs too.

• It can corrupt the first header name. A reader that does not strip the BOM sees the first column as id, not id. Python's csv module will not remove it when you open the file with encoding="utf-8". Use encoding="utf-8-sig" when reading, which removes it if present, as the back-conversion script below does.
• Some Unix tools show it as junk at the start of the first line.
• Concatenating files puts BOMs in the middle of the data.

A reasonable policy is to write the BOM when the file is intended for Excel users, and to leave it out for machine-to-machine files, with an option to switch. Always declare UTF-8 in the Content-Type header when serving CSV over HTTP, as in text/csv; charset=utf-8.

Line endings are a smaller version of the same story. The RFC specifies CRLF. Most readers accept LF too. A file with mixed endings, or a bare CR inside an unquoted field, is a sign of a bad producer. Quoted newlines inside fields can be either, and a reader will preserve them as written.

Delimiters and locales

The "C" in CSV stands for comma, but in many European locales the comma is the decimal separator, so spreadsheet programs there write and expect a semicolon as the field delimiter. A file full of semicolons opened in a comma-expecting program ends up as a single column, and a comma file opened in a semicolon-expecting program does the same.

Tab-separated values avoid the clash with decimal commas, and pipe-separated files are common in some industries. Python's csv writer accepts a delimiter argument, and the output with a semicolon looks like this:

id;note;amount
1;"Says ""hi"", then
leaves";1,5
2;=1+1;007

Observe that the comma in 1,5 no longer needs quoting, since it is not special any more, while the quote and newline in the note still do. The quoting rule is "quote if the field contains the delimiter, a quote or a line break", with whichever delimiter is in use.

Practical advice:

• Make the delimiter an option in any tool or script that writes CSV, and read it back with the same option.
• Do not auto-detect silently in production pipelines. Sniffing the delimiter from the first lines works most of the time and fails on awkward data. Use detection for convenience and an explicit setting for repeatable jobs.
• Be careful with decimals. Write numbers with a dot in files meant for programs, and leave localisation to the display layer. If you must write localised numbers, say so in the documentation.

Converting back: from CSV to JSON

Reading the file is the same problem in reverse, with a twist: you have lost information, so you must reconstruct it from conventions. There are two parts, type inference and unflattening.

Step 1: parse CSV correctly

Use a real CSV parser, not split. It must handle quoted fields, doubled quotes, embedded newlines and an optional BOM. Every mainstream language has one: Python's csv module, and in JavaScript either a well-tested library or the browser tool described below.

A tiny check shows why newlines matter. Python's reader returns this for a quoted newline, and keeps the record intact:

list(csv.reader(io.StringIO('a,b\n1,"x\ny"\n')))
# [['a', 'b'], ['1', 'x\ny']]

Ragged rows are handled differently by different parsers. Python's csv.reader returned [['a', 'b'], ['1', '2', '3'], ['4']] for input whose rows had three fields and one field, with no error. csv.DictReader puts extra values under a key None and fills missing ones with None. Decide whether a ragged row is a hard error or a warning for your pipeline, and check explicitly.

Step 2: infer types, carefully

Inference turns text into values. The common rules are: empty means null, true and false are booleans, and anything that looks like a number is a number. Each of these rules has a failure case.

• A ZIP code 02134 looks like a number. Converting it to 2134 is data loss.
• A string such as 1e5, 0x10 or Infinity may be accepted as a number by a lenient parser, depending on the language.
• A phone number such as +15551234567 is a valid number to some parsers, but it is an identifier, not a quantity.
• An empty cell may have been null, "" or a missing key, and you cannot know which.

A conservative inference uses a strict pattern. The Python function below accepts only canonical integers and decimals, with no leading zeros, and leaves everything else as text:

def infer(s):
    if s == "": return None
    if s in ("true", "false"): return s == "true"
    if re.fullmatch(r"-?(0|[1-9]\d*)(\.\d+)?", s): return float(s) if "." in s else int(s)
    return s

The pattern 0|[1-9]\d* is what protects leading zeros: 02134 does not match, so it stays a string. This rule also protects values such as 007. The remaining risk is a legitimately numeric identifier like 12345, which will become a number. A per-column override is the answer, and the full script below passes keep_text=("zip",).

Step 3: unflatten the paths

Unflattening splits each header on the separator, creates nested objects as it walks, and finally converts objects whose keys are all digits into arrays. Here is the complete script, run against the file produced earlier:

import csv, json, re

def infer(s):
    if s == "": return None
    if s in ("true", "false"): return s == "true"
    if re.fullmatch(r"-?(0|[1-9]\d*)(\.\d+)?", s): return float(s) if "." in s else int(s)
    return s

def unflatten(row, keep_text=("zip",)):
    root = {}
    for key, val in row.items():
        v = val if key in keep_text else infer(val)
        if v is None: continue
        parts = key.split("."); node = root
        for i, p in enumerate(parts):
            if i == len(parts) - 1: node[p] = v
            else: node = node.setdefault(p, {})
    def fix(n):
        if isinstance(n, dict):
            n = {k: fix(v) for k, v in n.items()}
            if n and all(k.isdigit() for k in n):
                return [n[k] for k in sorted(n, key=int)]
        return n
    return fix(root)

with open("out.csv", newline="", encoding="utf-8-sig") as f:
    for row in csv.DictReader(f):
        print(json.dumps(unflatten(row), ensure_ascii=False))

The output:

{"id": 1, "name": "Ada Lovelace", "zip": "02134", "active": true, "score": 9.5, "address": {"city": "London", "geo": {"lat": 51.5, "lng": -0.12}}, "tags": ["math", "poetry"], "note": "Says \"hello\", then\nleaves", "joined": "2026-03-14T09:30:00Z"}
{"id": 2, "name": "Alan Turing", "zip": "00501", "active": false, "address": {"city": "Wilmslow"}, "note": "'=HYPERLINK(\"http://evil.example\",\"click\")", "tags": "[]", "extra": "only here"}

The first record came back identical to the original. The nested address, the geo coordinates, the tags array, the multi-line note with its quotes, and the zero-prefixed ZIP all survived. That is the best case for a round trip, and it worked because the script and the writer agreed on conventions.

The second record shows what cannot be recovered.

• "score": null is gone. The key was dropped, since an empty cell is treated as "no value".
• "tags": [] came back as the string "[]", because the writer used a literal marker and the reader did not interpret it. A fuller implementation would convert [] and {} markers back, or the writer would avoid emitting them.
• The note still has the apostrophe added for injection protection. The data now differs from the original by one character, and that is a deliberate trade for safety. Strip the prefix on import only if you trust the file and know the writer added it.

Round trips are never perfect without an agreed schema. State the conventions you used, and test the loop on realistic data before you rely on it.

Edge cases in unflattening

• Array holes. If a CSV has tags.0 and tags.2 but not tags.1, the digit-key rule produces a list with the middle element missing, shifting later items down. Decide whether to fill with null or to reject.
• Mixed keys. A header set containing both a and a.b means a is simultaneously a scalar and an object. The script above would overwrite or fail. Detect and report this.
• Digit-only real keys. An object with keys "1" and "2" is converted to an array. The ambiguity is inherent in dot-notation, so add an option or a type hint if your data has such objects.
• Duplicate headers. Two columns named id produce a dictionary with one winner. Reject duplicate headers outright.
• Empty header cells. A trailing comma on the header line creates a column with no name. Warn about it.

Real-world use cases

API export for business users. A product manager wants the last month of orders in a spreadsheet. Flatten the nested customer object with dot-notation, explode or drop the line items, set a BOM, and prefix dangerous cells.

Loading spreadsheet data into a service. A marketing team maintains a table of redirects or translations in a spreadsheet, and the application reads JSON. Export CSV, convert to JSON with the CSV to JSON tool, validate the result, and commit it. Keep ID and code columns as text.

Analytics and data science. Analysts load CSV into notebooks and databases. Explode arrays into rows, write numbers with dots, use UTF-8 without a BOM, and keep a schema file describing types.

Log and event analysis. Event logs are often NDJSON. Flattening a sample into CSV makes it easy to sort, count and chart in a spreadsheet. The records are usually heterogeneous, so use the union of keys, and expect many sparse columns.

Test fixtures and seed data. Developers keep fixtures as CSV because diffs per row are readable, then convert to JSON at build time. Our guide to comparing files and code diffs explains why row-based text diffs are friendlier than a single-line JSON document.

Migrating between systems. Many systems offer CSV import and export when they have no direct integration. CSV is the bridge, and the conversion rules above decide whether the data arrives intact.

Configuration tables. Feature-flag matrices, pricing tables and lookup lists are naturally tabular. Store them as CSV for easy editing and generate JSON for the application.

Common mistakes

• Splitting on commas. It breaks on quoted commas and quoted newlines. Always use a real parser.
• Forgetting to quote. Writing " characters without doubling them produces files that no parser reads correctly. Use a library writer.
• Assuming all records share the same keys. Take the union, or declare the columns.
• Writing the header from the first record only. Later keys disappear without a warning.
• Losing empty arrays and objects. An order with no items vanishes when exploding. Emit a row with empty item cells.
• Cross-joining two arrays. Exploding two independent arrays multiplies rows.
• Trusting type inference. 02134 becomes 2134, "true" becomes a boolean, a long ID loses digits. Use schemas for sensitive columns.
• Opening the file in a spreadsheet and saving it. The spreadsheet's interpretation then overwrites the original text.
• Ignoring CSV injection. Exports of user-controlled text need sanitising.
• Mixing encodings. UTF-8 in, Latin-1 out produces garbled names. Settle on UTF-8 everywhere and test with accented text.
• Hardcoding the comma. Users in semicolon locales will see a single column.
• Using dots in real key names without escaping, then unflattening into the wrong structure.
• Nesting JSON in a cell with single quotes. Cells that hold JSON must use valid double-quoted JSON, or the reader cannot parse them.
• Converting the whole file in memory when it is hundreds of megabytes.

Step by step: convert JSON to CSV with DevUtilX

For a quick job with no code, use the DevUtilX JSON to CSV converter. It runs in your browser, so your data is processed locally and never uploaded. That matters when the JSON contains customer records.

1. Open the JSON to CSV tool.
2. Paste your JSON array into the input, or load it from a file. If the document is a single object, wrap it in square brackets so the tool sees one record.
3. Check the options. Look for the delimiter, whether nested objects are flattened, and how arrays are handled. Choose a semicolon if your recipients use a semicolon locale.
4. Run the conversion and inspect the header row. Confirm that every key you care about has a column, and that nested paths look the way you expect.
5. Scan a few rows for empty cells where you expected values. Gaps usually mean a key is missing in that record, or the path is nested differently.
6. Copy the result or download the file, and open it with the import feature of your spreadsheet if zeros or long numbers matter.

If the tool reports an error, the input is probably invalid JSON, such as a trailing comma, single quotes or unquoted keys. Run it through the DevUtilX JSON validator, or review our JSON formatting guide, which covers the common syntax mistakes.

Step by step: convert CSV to JSON with DevUtilX

Going the other way, use the CSV to JSON converter. It also runs locally in your browser.

1. Open the CSV to JSON tool.
2. Paste the CSV text or load the file. Make sure the first line is the header row you want as keys.
3. Set the delimiter to match the file. A single-column result almost always means a wrong delimiter.
4. Choose whether values should be converted to numbers and booleans or kept as strings. For files with codes, ZIPs or phone numbers, keep strings or review the output carefully.
5. Run the conversion and read the first few objects. Check types: numbers should not be quoted, and identifiers with leading zeros should be.
6. Copy the JSON. Format or minify it as needed. If the result will be sent over a network, see our guide to JSON minification and payload size.

If you know the file used dot-notation headers, check how the tool treats them. Many converters return flat keys such as "address.city" and leave unflattening to your code. When you need nested output, the Python script above shows the logic you can adapt.

Large files and streaming

Everything so far assumed the data fits in memory. When it does not, the shape of the problem changes.

Writing CSV from a large JSON array. JSON.parse loads the whole document, and the parsed object graph is usually larger than the text. A streaming approach needs input that can be read incrementally. NDJSON, where each line is a separate compact JSON object, is ideal. A short Python illustration of line-by-line reading:

for line in src:
    r = json.loads(line)
    print(r["a"], r["b"]["c"])

On two sample lines, the output was 1 2 and 3 4. In a real job, each parsed record would be flattened and written immediately, and nothing accumulates in memory.

The header problem. A streaming writer must emit the header before the first row. If records are heterogeneous, you cannot know the full union in advance. The options are: do two passes over the input, the first only collecting keys; declare the columns up front; or write a fixed schema and route unexpected keys to an overflow column containing JSON.

Reading large CSV. Parse row by row and process or insert as you go, instead of loading all rows. Python's csv.reader is lazy, so iterating over it holds one row at a time. In the browser, read files in chunks with the File and Blob APIs, and avoid building one enormous string.

Spreadsheet limits. Spreadsheet programs cap rows and columns, and the cap differs between products and versions, so check the limits of the program your recipients use before sending a huge file. If a file will not fit, split it into parts, each with its own header.

Compression. CSV compresses well, because column values repeat. If you ship large exports, offer them gzipped, and our guide to payload size explains the general principles of transferring text efficiently.

Performance, security and accessibility considerations

Performance. Flattening is linear in the size of the data. The costly parts are usually the includes check when building the union of columns, which becomes quadratic on very wide data, and string concatenation of large outputs. Use a Set for column tracking in hot paths, and write in chunks. The cols.includes(k) call in the example is fine for dozens of columns but is not the right choice for thousands.

Security. Beyond formula injection, treat the CSV as untrusted input when reading. Limit file size and row count, reject absurdly wide rows, and never evaluate cell contents as code. When converting to JSON, be careful about keys such as __proto__ in JavaScript: an unflatten routine that assigns into plain objects by path can be abused to alter object prototypes. Use Object.create(null) or a Map for the intermediate structure, and block those key names. Do not paste sensitive data into any online converter that uploads it. DevUtilX runs locally, but check that claim for any tool you use, for example by watching the browser's network tab.

Privacy. An export is a copy of data outside the system that controls access. Include only the columns the recipient needs, and treat the file with the same care as the source.

Accessibility. CSV is plain text, so it works well with screen readers and assistive software only when it is well structured. A clear header row with unique, descriptive names helps people who navigate a table column by column. Avoid merged cells and decorative blank rows or columns, which have no CSV equivalent anyway. Use human-readable column names in files meant for people, and technical paths such as address.geo.lat only where programs are the audience. If you publish a CSV download on a web page, state its encoding, delimiter and meaning of empty cells in nearby text, rather than expecting readers to work them out.

Troubleshooting guide

Work from symptom to cause to fix.

Symptom: everything lands in one column

Cause. The delimiter in the file differs from the one the program expects, typically a semicolon file in a comma program or the reverse.
Fix. Set the delimiter explicitly in the import dialog or tool, or re-export with the expected delimiter.

Symptom: accented characters look like é

Cause. A UTF-8 file was read as a legacy single-byte encoding. Excel does this when there is no BOM.
Fix. Write a UTF-8 BOM for Excel users, or import with the encoding set to UTF-8.

Symptom: the first column name is id or the first key does not match

Cause. The reader did not strip the BOM.
Fix. In Python, open with encoding="utf-8-sig". In JavaScript, remove a leading  from the string before parsing.

Symptom: ZIP codes or IDs lost their leading zeros

Cause. A spreadsheet or an inference routine converted the text to a number.
Fix. Import the column as text, keep specified columns as strings when converting to JSON, and use strict number patterns that reject leading zeros.

Symptom: rows have the wrong number of fields

Cause. An unquoted comma, quote or newline inside a value, or a producer that did not follow the quoting rules.
Fix. Find the first bad line, correct the quoting at the source, and use a library writer. Do not repair by splitting lines. In Python, csv.reader returned a row of three fields and a row of one for ragged input without raising an error, so add your own length check.

Symptom: columns are missing for some records

Cause. The header was built from the first record only.
Fix. Use the union of keys or declare the columns.

Symptom: a record disappeared after exploding an array

Cause. The array was empty, so the loop produced no rows.
Fix. Emit one row with empty item cells for empty arrays, as in o["items"] or [{}].

Symptom: a spreadsheet warns about links or formulas when opening the file

Cause. A cell begins with =, +, - or @. This is either a formula the author wanted, or CSV injection.
Fix. Prefix untrusted text on export, and review the data source to understand where the content came from.

Symptom: after converting back, numbers have extra digits or lost digits

Cause. Large integers exceed the precision of a double, or a decimal is shown in a float's binary approximation.
Fix. Keep large identifiers as strings. For money, use integers in minor units or a decimal library.

Symptom: booleans arrive as text "true"

Cause. The converter had type inference turned off, or the values were capitalised, such as TRUE, and did not match a strict rule.
Fix. Normalise the values on export, or extend the inference to a documented set of spellings.

Symptom: nested data comes back flat, with keys like "address.city"

Cause. The reader parsed CSV but did not unflatten.
Fix. Run an unflatten step, as in the Python script above, and test it with sample paths.

Symptom: the output has hundreds of nearly empty columns

Cause. Dot-notation indexing of long arrays or highly variable keys.
Fix. Explode the array to rows, store it as JSON in one cell, or select the columns you need.

Best practices

• Decide the direction first. Know whether the CSV is for people, for programs or for round trips, then choose the flattening strategy.
• Use a real parser and writer. Never split or join strings by hand for anything beyond a toy.
• Declare columns for repeatable exports, and keep their order stable.
• Take the union of keys when you cannot declare columns, and sort for stable order.
• Write dot-notation with a documented separator, and escape or avoid dots in real keys.
• Handle empty arrays and objects deliberately, and say what an empty cell means.
• Keep identifiers as text. Mark the columns as text on import and in code.
• Use UTF-8. Add a BOM only for Excel audiences, and say so.
• Sanitise formula-leading cells in any export a person might open in a spreadsheet.
• Make delimiter and line endings options.
• Stream large files, and prefer NDJSON as the JSON side.
• Ship a schema or data dictionary when types matter.
• Test the round trip on realistic data, including nulls, empty arrays, quotes, newlines, zero-prefixed codes and non-ASCII text.
• Keep the original JSON when the conversion is lossy.
• Validate after converting, both the CSV structure and the JSON syntax.

A short checklist before you send a file:

1. Does the header contain every column recipients need, with unique names?
2. Are quotes, commas and newlines inside values handled by a library?
3. Is the encoding UTF-8, with or without a BOM as appropriate?
4. Are leading zeros and long numbers safe in the intended viewer?
5. Are formula-like cells neutralised?
6. Is the delimiter right for the recipient's locale?
7. Have you documented what empty means, and how nested data was flattened?
8. Did you convert the file back and compare it with the original?

Comparison: ways to move data between JSON and tables

Approach	Strengths	Weaknesses	Best for
CSV with dot-notation	Universal, opens anywhere, reversible by convention	No types, wide for arrays, injection risk	Spreadsheet exports and simple round trips
CSV with exploded rows	Easy aggregation	Duplicated parent values, row count changes	Reporting and analysis
CSV with JSON cells	Lossless nested data	Hard to read and filter	System-to-system transfers
NDJSON	Streams, keeps types and nesting	Not spreadsheet friendly	Logs, large exports
TSV	No clash with commas in text	Tabs in data still need handling, less standardised	Simple pipelines and copy-paste from spreadsheets
XLSX	Typed cells, formatting, multiple sheets	Heavier, needs a library, not plain text	Deliverables for business users
Parquet and similar columnar files	Types, compression, speed	Binary, needs tooling	Analytics at scale
SQL import	Strong schema and constraints	Requires a database	Loading data you will query

If the readers are programs that understand JSON, send JSON, as a compact payload if size matters. If the reader is a person with a spreadsheet, send CSV or XLSX. Convert only at the edge where the two worlds meet, and keep the richer format as the source of truth.

FAQ

What is the difference between JSON and CSV?
JSON stores hierarchical data with types such as numbers, booleans and null. CSV stores a flat table of text values, with one record per line and no types.

Is there an official standard for CSV?
RFC 4180 documents common practice and registers the text/csv media type, but it is informational, and many programs deviate from it in small ways.

How do I put a comma or quote inside a CSV value?
Wrap the field in double quotes. Inside a quoted field, write each double quote as two double quotes. A field containing a line break must also be quoted.

How are nested objects represented in CSV?
There is no standard. The common choices are dot-notation column names such as address.city, one row per array element, or a compact JSON string in a single cell.

Why do I lose leading zeros when I open a CSV in a spreadsheet?
The spreadsheet decides that 02134 is a number and drops the zero. The file itself still has the zero. Import the column as text to keep it.

What is CSV injection?
It is an attack in which text starting with =, +, - or @ in an exported cell is executed as a formula by a spreadsheet. Prefix such cells, or avoid exporting untrusted text without sanitising.

Do I need a BOM for UTF-8 CSV files?
It is not required by the UTF-8 standard, but Excel often needs it to recognise UTF-8 when opening a file directly. Other tools may treat it as part of the first header, so use it only when your readers need it.

Why does my CSV appear as one column?
The delimiter does not match. Semicolons are common in locales that use a decimal comma. Set the delimiter when importing or converting.

How do I tell null from an empty string in CSV?
You cannot, by default. Choose a convention such as an empty cell for null and a quoted empty string for empty text, or a marker like NULL, and make sure the reader implements it. Many readers treat both as empty.

Can I convert CSV back to the exact original JSON?
Only if you control the conventions. Types, nulls, empty arrays and key order can be lost. A schema and an unflatten routine recover much of the structure, but keep the original if it matters.

Should I use JSON or CSV for an API?
Use JSON for APIs, since it carries structure and types. Offer CSV as an export format for people who want to open the data in a spreadsheet.

How do I handle very large files?
Stream them. Read line by line from NDJSON or a lazy CSV reader, write rows as you go, declare the columns up front, and avoid loading everything into memory.

Is it safe to convert sensitive data with an online tool?
Only if the tool processes data locally. DevUtilX runs in your browser and does not upload your input, but you should still avoid pasting secrets, tokens or personal data into any tool you have not verified.

Conclusion

Converting between JSON and CSV is a translation between a tree and a grid, and the details matter. Pick a flattening strategy on purpose, take the union of keys, treat every value as text with a lost type, protect leading zeros and encodings, neutralise formula-leading cells, and test the round trip with data that contains the awkward cases. When you just need the result, open the JSON to CSV converter, or go the other way with CSV to JSON. Both run locally in your browser. For related reading, see our JSON formatting guide, and our look at JSON minification and payload size.

Further reading

• Comma-separated values – Wikipedia
• RFC 4180: Common Format and MIME Type for CSV Files
• JSON – Wikipedia
• RFC 8259: The JSON Data Interchange Format
• CSV Injection – OWASP
• Byte order mark – Wikipedia
• csv module – Python documentation