CSV to JSON

Convert CSV to a JSON array of objects, with the header row as keys. Delimiters are detected, quoted fields with embedded commas and newlines are handled, and duplicate column names are renamed rather than dropped.

Type inference is off by default, and that is deliberate.

Why every value is a string until you say otherwise

Type inference is where CSV converters destroy data. A CSV has no types, so a converter has to guess, and the guesses are wrong in ways that are hard to notice.

007 becomes 7 and a zip code is gone. A phone number written as 1234567890123456789 exceeds what a JavaScript number can hold and comes back different. A product code like 1E5 becomes 100000. A value of NA becomes a string in one row and the header of a column somewhere else.

So strings are the default. Turn inference on when you know your data, and note that even then leading zeros are preserved and any value that would not survive a float64 round trip stays a string.

The CSV rules that trip people up

Quoting

RFC 4180: a field containing a comma, a quote or a newline is wrapped in double quotes, and a quote inside becomes two quotes. This parser reads that correctly, including multi-line fields.

Delimiters

Comma, semicolon, tab and pipe are detected automatically by counting occurrences outside quotes across the first few lines. European Excel writes semicolons.

Duplicate headers

JSON objects cannot hold duplicate keys, so a repeated column name is suffixed and the change is reported. Dropping one would be silent data loss.

Ragged rows

A row with fewer fields than the header gets nulls; a row with more keeps the extras under an _extra key. Both are reported with a line number rather than silently realigned.

The byte order mark

Stripped, and reported. Left in place it becomes part of the first column name, which produces a mysteriously missing field.

Rebuilding nested structure

If your headers are dotted paths, as they are in a CSV our own converter produced, turn on expand dotted headers and the nesting is rebuilt: address.city becomes a nested object rather than a key containing a dot.

How to do this in code

Converting in code.

import csv, json

# The standard library gives you strings, which is the honest default
with open('in.csv', newline='', encoding='utf-8-sig') as f:
    rows = list(csv.DictReader(f))
print(json.dumps(rows, indent=2))

# pandas infers types, which is convenient and lossy
import pandas as pd
df = pd.read_csv('in.csv', dtype=str)   # dtype=str turns inference OFF
records = df.to_dict(orient='records')

import Papa from 'papaparse';

const { data, errors } = Papa.parse(csv, {
  header: true,
  skipEmptyLines: true,
  dynamicTyping: false,   // leave it off; see the note above
});

# csvkit
in2csv data.csv | csvjson --indent 2 > data.json

# Or with Miller, which is a single binary
mlr --icsv --ojson cat data.csv

Questions

Should I turn on type inference?

Only if you know the data. It is right most of the time and the exceptions are exactly the values you cannot afford to lose: zip codes, account numbers, part numbers, phone numbers, anything with a leading zero. Even with inference on, this tool leaves those alone.

Why is my first column name odd?

A byte order mark. This tool strips it and says so. A parser that does not leaves an invisible character at the start of the name, so lookups by that name fail for no visible reason.

Can I output NDJSON instead of an array?

Convert here and then use the NDJSON page, which turns an array into one record per line. That is the better format for anything you plan to stream or append to.