How to convert CSV to JSON

Five ways to do it, what each one costs you, and the four things about the CSV format that turn a two-line conversion into an afternoon.

The short answer

Use a real CSV parser, and decide what you want to happen to types. Those are the only two decisions. Everything else is a matter of which language you are already in.

A one-off conversion belongs in a browser tool. A conversion you will run again belongs in a script, because next month's file will have a new column in it. A file too big to hold in memory belongs in a streaming tool writing NDJSON. What none of them should be is a split on commas, for the reason in the next section.

Four things about CSV that break a conversion

1. Quoting

A field may contain the delimiter, as long as it is quoted. So this file has two columns, not three:

name,note
Ada,"Keyboard, 88 key"

A quoted field may also contain a doubled quote standing for one character, and a line break, which means one record can span several lines of the file. Any conversion that assumes one line is one record will read that file wrongly and not tell you. This is the whole reason to use a parser rather than split.

2. The delimiter is not always a comma

Excel writes a semicolon on any machine whose locale uses a comma as the decimal separator, which is most of continental Europe. Databases and log tools often write tabs or pipes. A good converter detects it; a good script takes it as an argument rather than hard-coding a comma and failing silently on the first file from the Berlin office.

3. Encoding

CSV carries no encoding declaration. A file is bytes, and the reader guesses. UTF-8 read as Windows-1252 turns é into é; the fix is a byte order mark at the front, which Excel treats as proof of UTF-8. In Python, read those files with encoding='utf-8-sig' so the mark is stripped rather than becoming part of your first column name.

4. Excel has already changed your data

This is the one people find last. If the CSV was opened and saved in Excel on its way to you, leading zeros are gone, anything date-shaped has been converted, and long numbers are in scientific notation. No converter can undo that, because the information is not in the file any more. Get the original export, and open it in a text editor to check before you convert.

Five ways to do it

In the browser

Paste or drop the file, check the delimiter it reports, take the JSON. Nothing is uploaded, which matters when the file is a customer export and the alternative is a site that wants you to hand it over. The ceiling is memory: past about 10 MB you want one of the others.

Python, standard library

import csv, json

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

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

Every value comes out as a string, which is the correct default: the file did not say otherwise. newline='' is not optional, because the csv module handles line endings itself and will split records containing line breaks without it. ensure_ascii=False keeps accented characters as themselves rather than as escape sequences.

To convert only some columns to numbers, do it explicitly and one column at a time:

for row in rows:
    row['qty'] = int(row['qty'])
    row['price'] = float(row['price'])

pandas

import pandas as pd

pd.read_csv('data.csv', dtype=str).to_json('data.json', orient='records', indent=2)

dtype=str is the important argument. Without it pandas infers a type per column, and a column of order numbers becomes floats, so 00412 comes out as 412.0 and a missing value becomes NaN, which is not valid JSON. Drop dtype=str only when every column really is a measurement.

Miller, or jq

Miller reads CSV natively, which jq does not:

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

Going the other way, jq is the usual tool, and this is the incantation:

jq -r '(.[0] | keys_unsorted), (.[] | [.[]]) | @csv' data.json

It takes the keys of the first record as the header, then every record's values in order. Which means it is wrong the moment two records have different keys, and it will not say so. Miller's --ojson and --ocsv handle that case properly and are worth the install if you do this more than once.

Node

import { readFileSync, writeFileSync } from 'node:fs';
import { parse } from 'csv-parse/sync';

const rows = parse(readFileSync('data.csv'), { columns: true, bom: true });
writeFileSync('data.json', JSON.stringify(rows, null, 2));

Node ships no CSV parser, so this needs csv-parse. Use the streaming API rather than /sync for anything large. bom: true handles the Excel case from the encoding section above.

Which shape of JSON do you want

There are three, and they are not interchangeable.

• An array of objects. The default, and what nearly everything expects. Each record carries its column names, so it costs more bytes and reads without a schema.
• An array of arrays. The header stays as row one and the rest are positional. Compact, and the right shape when the column names are noise or the consumer is a chart library.
• NDJSON. One object per line, no wrapping array. The only one of the three that can be read without holding the whole file, and the only one you can append to.

Check the result before you ship it

Convert it back. Turn the JSON into CSV again and compare it with the file you started with: anything that changed is something the conversion did to your data rather than to its formatting. Row counts that disagree usually mean a quoted line break, and a column that lost its leading zeros means a type guess you did not want.

Then read the JSON itself. Long IDs turning into rounded numbers and duplicate keys from two columns with the same header are both invisible in a diff and both obvious in a validator.

Related