CSV to JSON: The Parts Nobody Mentions Until Your Script Breaks in Production

If you've ever written line.split(',') to parse a CSV file, you already know how this story ends. It works great on your test file, ships to production, and then someone opens the export in Excel, types a comma into a "Notes" field, saves it, and your parser quietly shifts every column after that one.

CSV to JSON conversion looks like a five-minute task right up until it isn't. This post walks through what actually goes wrong, how to handle it properly, and where the effort is or isn't worth it — whether you're writing a one-off script or building something that has to survive contact with real-world data.

Why "just split on commas" doesn't work

CSV (Comma-Separated Values) is a deceptively simple-looking format with a genuinely messy specification (RFC 4180, if you want to read it, though half the CSVs in the wild don't fully follow it anyway). Here's a file that will break a naive splitter:

name,age,notes
"Smith, John",34,"Likes coffee, hates Mondays"
"Doe, Jane",29,"Says ""hello"" a lot"

A quoted field can contain the delimiter. A quoted field can contain quote characters, escaped by doubling them (""). A quoted field can even contain a literal newline, which means a single CSV "row" might span multiple physical lines in the file. None of this is visible if you just count commas.

Rule one: don't write your own CSV parser. It's a fun weekend project, but every mature CSV library has already solved quoting, escaping, multi-line fields, and the dozen other edge cases you haven't thought of yet. Use one.

A solid baseline: Python

Python's standard library handles the parsing correctly out of the box:

import csv
import json

with open('data.csv', newline='', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    rows = list(reader)

with open('data.json', 'w', encoding='utf-8') as f:
    json.dump(rows, f, indent=2)

That newline='' on the file open isn't decorative — it's required so the csv module can correctly detect line endings inside quoted fields. Skip it and you'll get subtly wrong results on files with embedded newlines.

The output is a JSON array of objects, keyed by the header row:

[
  { "name": "Smith, John", "age": "34", "notes": "Likes coffee, hates Mondays" },
  { "name": "Doe, Jane", "age": "29", "notes": "Says \"hello\" a lot" }
]

Notice age is a string. That's the next problem.

Everything in CSV is a string, and that's a trap

CSV has no type system. 34 is text. true is text. An empty cell is text (or absent, depending on the row). If you want a real JSON number or boolean, you have to convert it yourself — and that's where scripts get sloppy in ways that don't show up until later.

A blind "try to parse everything as a number" approach seems tempting:

def guess_type(value):
    if value == "":
        return None
    if value.lower() in ("true", "false"):
        return value.lower() == "true"
    try:
        return float(value) if "." in value else int(value)
    except ValueError:
        return value

But run it on {"id": "00423", "active": "true"} and id silently becomes 423. If that's a zip code, an account number, or a SKU, you just corrupted your data — leading zeros are the classic CSV-to-JSON landmine. The safer approach is an explicit column-to-type map instead of guessing per value:

SCHEMA = {
    "id": "string",     # keep as-is, don't coerce
    "score": "number",
    "active": "boolean",
}

def coerce(key, value):
    if value == "":
        return None
    kind = SCHEMA.get(key, "string")
    if kind == "boolean":
        return value.lower() in ("true", "yes", "1")
    if kind == "number":
        return float(value) if "." in value else int(value)
    return value

More typing, but it never quietly reinterprets an ID as an integer just because it happens to look like one.

The encoding problem you'll hit eventually

Open a CSV exported from Excel and check the first few bytes — there's a decent chance it starts with a BOM (byte order mark, \ufeff). If you read it as plain UTF-8 without stripping the BOM, it silently attaches itself to your first header name:

# Header looks like "name" but is actually "\ufeffname"
row["name"]  # KeyError, even though the column is right there

The fix is using utf-8-sig instead of utf-8 as the encoding when you open the file:

with open('data.csv', newline='', encoding='utf-8-sig') as f:
    reader = csv.DictReader(f)

Also worth checking: not every CSV is UTF-8 to begin with. Files from older Windows systems or non-English locales sometimes show up as latin-1 or cp1252. If you get garbled characters instead of an outright crash, that's usually the culprit. If you're not sure what you're dealing with, chardet or charset-normalizer can take a decent guess at the encoding before you commit to one.

Delimiters aren't always commas

Plenty of "CSV" files use semicolons instead of commas — common in locales where the comma is the decimal separator (so 3,14 means 3.14, and a comma can't also be the field separator without ambiguity). Excel in those locales exports semicolon-delimited files by default, still with a .csv extension, which trips people up constantly. Tab-separated files also frequently masquerade as .csv.

Both Python's csv module and JS libraries let you set the delimiter explicitly:

reader = csv.DictReader(f, delimiter=';')

If you're building something that accepts arbitrary uploaded CSVs, it's worth sniffing the delimiter rather than assuming:

sample = f.read(2048)
f.seek(0)
dialect = csv.Sniffer().sniff(sample)
reader = csv.DictReader(f, dialect=dialect)

It's not bulletproof, but it catches the semicolon case reliably.

Node.js version, with a real streaming parser

For Node, I'd reach for csv-parse rather than hand-rolling anything:

const fs = require('fs');
const { parse } = require('csv-parse');

const records = [];

fs.createReadStream('data.csv')
  .pipe(parse({ columns: true, skip_empty_lines: true, bom: true }))
  .on('data', (row) => records.push(row))
  .on('end', () => {
    fs.writeFileSync('data.json', JSON.stringify(records, null, 2));
  })
  .on('error', (err) => console.error(err));

The bom: true option handles that BOM issue automatically, and columns: true gives you the same header-keyed objects as Python's DictReader. skip_empty_lines matters more than it sounds — trailing blank lines at the end of exported CSVs are extremely common and will otherwise show up as a row of empty strings in your output.

When the file is too big to hold in memory

Loading everything into an array and calling JSON.stringify works fine until someone hands you a 2GB export. At that point you want to stream rows out as you read them, usually as NDJSON (newline-delimited JSON) rather than one giant JSON array, since NDJSON lets you process the output line by line too:

import csv
import json

with open('huge.csv', newline='', encoding='utf-8-sig') as infile, \
     open('huge.ndjson', 'w', encoding='utf-8') as outfile:
    reader = csv.DictReader(infile)
    for row in reader:
        outfile.write(json.dumps(row) + '\n')

Constant memory usage regardless of file size, because you never hold more than one row at a time. If you genuinely need a single JSON array as output (some downstream consumer requires it), you can still stream the reads and just write the array brackets and commas manually — a bit more bookkeeping, but it avoids loading the whole CSV into memory at once.

Handling malformed rows without crashing the whole job

Real files have bad rows in them — a stray extra comma from a bad export, a row that's missing half its fields. Letting one bad row kill a job processing 500,000 others is usually the wrong tradeoff. Collect the failures instead of raising immediately:

good_rows = []
errors = []

with open('data.csv', newline='', encoding='utf-8-sig') as f:
    reader = csv.DictReader(f)
    expected = set(reader.fieldnames)
    for line_num, row in enumerate(reader, start=2):  # line 1 is the header
        if None in row or None in row.values():
            errors.append({"line": line_num, "reason": "field count mismatch", "raw": row})
            continue
        good_rows.append(row)

print(f"Converted {len(good_rows)} rows, skipped {len(errors)}")

DictReader actually tells you when a row doesn't match the header: extra fields get bundled under the None key, and missing fields come back as None values. Checking for that instead of ignoring it means you find out about bad rows from a log line, not from a confused support ticket three weeks later.

Turning flat columns into nested JSON

CSV is inherently flat — one row, one level of columns. But it's common to want structured output, especially with dot-notation or bracket-notation headers:

name,address.city,address.zip,tags[0],tags[1]
Alex,Portland,97201,dev,remote

into:

{
  "name": "Alex",
  "address": { "city": "Portland", "zip": "97201" },
  "tags": ["dev", "remote"]
}

There's no standard for this — it's a convention some tools support and others don't — but it's straightforward to build:

def unflatten(row):
    result = {}
    for key, value in row.items():
        parts = key.replace(']', '').replace('[', '.').split('.')
        d = result
        for i, part in enumerate(parts):
            is_last = i == len(parts) - 1
            if part.isdigit():
                part = int(part)
            if is_last:
                if isinstance(d, list):
                    while len(d) <= part:
                        d.append(None)
                    d[part] = value
                else:
                    d[part] = value
            else:
                next_is_index = parts[i + 1].isdigit()
                container = [] if next_is_index else {}
                if isinstance(d, list):
                    while len(d) <= part:
                        d.append(None)
                    if d[part] is None:
                        d[part] = container
                    d = d[part]
                else:
                    if part not in d:
                        d[part] = container
                    d = d[part]
    return result

It's more code than you'd think for something that sounds simple — nested-structure reconstruction always is. If you only need this occasionally, it's usually not worth maintaining custom unflattening logic yourself; a lot of teams just add a small transform step after a plain conversion instead of building this into the parser.

Ragged rows and duplicate headers

Beyond the field-count mismatch already mentioned, watch for headers that repeat. If a CSV has two columns both named email (more common than you'd hope, usually from a spreadsheet merge), a plain dict-based approach will silently keep only the last one. Check for it before you trust the output:

with open('data.csv', encoding='utf-8-sig') as f:
    headers = next(csv.reader(f))

duplicates = {h for h in headers if headers.count(h) > 1}
if duplicates:
    print(f"Warning: duplicate columns will overwrite each other: {duplicates}")

Cheap check, saves you from silently dropping data with no error at all.

Bonus: inferring a schema from the CSV itself

If you're handing the resulting JSON off to another team, or writing a TypeScript interface for it, it helps to know the actual shape of the data rather than guessing. A quick pass over all rows per column gives you that (this builds on the reader and good_rows from the malformed-row check earlier):

def infer_column_type(values):
    kinds = set()
    for v in values:
        if v == "":
            kinds.add("null")
        elif v.lower() in ("true", "false"):
            kinds.add("boolean")
        else:
            try:
                int(v)
                kinds.add("integer")
            except ValueError:
                try:
                    float(v)
                    kinds.add("number")
                except ValueError:
                    kinds.add("string")
    non_null = kinds - {"null"}
    if non_null <= {"integer"}:
        return "integer"
    if non_null <= {"integer", "number"}:
        return "number"  # a mix of "10" and "10.5" in one column is still just a number
    if len(non_null) == 1:
        return non_null.pop()
    return "string"  # genuinely mixed types: safest to leave as string

columns = reader.fieldnames
schema = {col: infer_column_type([row[col] for row in good_rows]) for col in columns}
print(schema)
# {'name': 'string', 'age': 'integer', 'active': 'boolean', 'notes': 'string'}

It's rough — real schema inference tools do a lot more validation — but for catching an unexpectedly mixed column (a "phone" field that's mostly numbers but has a few entries like "N/A") before it becomes a type error downstream, this is usually enough.

Quick, one-off conversions without writing code

Not every CSV-to-JSON job needs a script. If it's a one-time thing — a test fixture, a quick check of what an export actually contains — there are lighter options depending on where you're working.

On the command line, csvkit ships a csvjson command that covers the common cases:

pip install csvkit
csvjson data.csv > data.json

Miller (mlr) is another solid option if you're already piping data through Unix tools:

mlr --icsv --ojson cat data.csv > data.json

And if you're on a machine where installing anything isn't an option — someone else's laptop, a locked-down environment — a browser-based converter works too. 99tools' CSV to JSON converter is one of these: paste the CSV in, get JSON back, nothing to install. Like the CLI tools above, you're trading control for convenience — you don't get to decide how empty strings, leading zeros, or non-UTF-8 encoding get handled — so it's better suited to quick fixtures and debugging than anything that runs unattended as part of a pipeline.

A checklist for production conversions

• Use a real CSV parser, never manual string splitting
• Handle the BOM (utf-8-sig in Python, bom: true in csv-parse)
• Decide your delimiter explicitly, or sniff it, don't assume a comma
• Convert types deliberately with a column map, not blind inference — leading zeros will bite you
• Treat empty strings, null, and "absent key" as three different things and pick one on purpose
• Stream large files instead of loading everything into memory
• Collect and log malformed rows instead of letting one bad row kill the whole job
• Check for duplicate or ragged headers before you trust the output

None of this is complicated once you know it exists — it's just easy to skip when the sample file on your desk happens to be clean. The real CSVs, the ones exported from some legacy system three departments over, are never as clean as the one you tested with.