CSV to JSON converter

What the output looks like

Two orders exported from a spreadsheet, and the JSON you get from them.

order_id,customer,zip,total,paid,notes
100045,Maya Chen,02108,1499.50,TRUE,"Gift wrap, no receipt"
100046,Alex Kim,94103,89.00,FALSE,

[
  {
    "order_id": 100045,
    "customer": "Maya Chen",
    "zip": "02108",
    "total": 1499.50,
    "paid": true,
    "notes": "Gift wrap, no receipt"
  },
  {
    "order_id": 100046,
    "customer": "Alex Kim",
    "zip": "94103",
    "total": 89.00,
    "paid": false,
    "notes": null
  }
]

The comma in Maya’s note doesn’t split the cell, because the note is in quotes. Alex’s empty note becomes null. If you’d rather have an empty string, or no notes field at all, change Empty cells under Options. For a file without a header row, untick First row is headers and each row comes out as an array.

Numbers and TRUE/FALSE

Each column is checked as a whole. If every value in it is a number, you get numbers, and Excel’s TRUE and FALSE become true and false. Anything else stays as text.

The zip column above stays as strings because 02108 would lose its leading zero as a number, and a column that’s half numbers and half strings is a pain to work with. To keep everything as text, untick Detect numbers and true/false.

Nested fields

Columns named like address.city and address.zip can be combined into an address object. Set Nested fields to From dots. Headers like /address/city, which the JSON to CSV converter writes, are nested automatically, so you can export JSON to a spreadsheet, edit it, and bring it back.

Exporting from Excel or Google Sheets

In Excel, use File > Save As and choose CSV UTF-8 (Comma delimited). The plain CSV option can mangle accented characters. In many European countries Excel uses semicolons instead of commas. The separator is detected automatically, or you can set it under Options. In Google Sheets it’s File > Download > Comma-separated values.

Before exporting, check any ZIP code or ID columns. If Excel already shows 02108 as 2108, the zero is gone from the file too. Format the column as Text and fix those values first.

Doing it in code

# Python, using only the standard library
import csv, json
with open("orders.csv", newline="", encoding="utf-8-sig") as f:
    rows = list(csv.DictReader(f))
print(json.dumps(rows, indent=2))

# Command line, with Miller
mlr --icsv --ojson cat orders.csv

csv.DictReader gives you every value as a string, so you’d have to convert numbers yourself. utf-8-sig strips the byte order mark Excel adds to UTF-8 files. Without it, your first column’s name starts with an invisible character.