CSV to JSON: How to Convert Spreadsheet Data to JSON Arrays

Why Convert CSV to JSON?

CSV is the lingua franca of spreadsheets. JSON is the lingua franca of APIs. Converting between them is one of the most common tasks in data engineering, frontend development, and backend ETL pipelines.

When you receive a CSV export from a database, an analytics tool, or a client's spreadsheet, converting it to JSON lets you: - Feed the data directly into a REST API or GraphQL endpoint - Load it into MongoDB, Firestore, or DynamoDB as documents - Use it as realistic fixture data in tests and UI prototyping - Process it in JavaScript or Python data pipelines

How CSV to JSON Conversion Works

A CSV file has two sections: a header row and data rows. The converter maps the first row to JSON keys and each subsequent row to a JSON object:

id,name,city,age,active,score
1,Ravi,Surat,28,true,98.5
2,Priya,Ahmedabad,24,false,87.2
3,Arjun,Vadodara,31,true,92.0

[
  { "id": 1, "name": "Ravi",  "city": "Surat",     "age": 28, "active": true,  "score": 98.5 },
  { "id": 2, "name": "Priya", "city": "Ahmedabad", "age": 24, "active": false, "score": 87.2 },
  { "id": 3, "name": "Arjun", "city": "Vadodara",  "age": 31, "active": true,  "score": 92.0 }
]

Automatic Type Detection

A smart converter infers the most specific type for each cell value:

Without type detection, you would get "28" (string) instead of 28 (number) — which causes subtle bugs in APIs and SQL comparisons.

Delimiter Options

Not all CSV files use commas as the delimiter. The most common variants:

Always inspect the first few lines of your CSV to identify the delimiter before converting.

Handling Quoted Fields and Embedded Commas

Per RFC 4180, fields containing the delimiter, newlines, or double quotes must be wrapped in double quotes. Embedded double quotes are doubled:

name,description,price
"Widget A","A high-quality, durable product","$29.99"
"Smart ""Pro"" Plan","Business plan, advanced","$99/mo"

[
  { "name": "Widget A", "description": "A high-quality, durable product", "price": "$29.99" },
  { "name": "Smart "Pro" Plan", "description": "Business plan, advanced", "price": "$99/mo" }
]

Common Pitfalls

No header row: If your CSV has no headers, add a header row before converting — otherwise row 1 becomes the keys and you lose real data.

Inconsistent column counts: If some rows have more or fewer columns than the header, missing cells become null and extra cells are ignored.

BOM character at start of file: Excel often adds a UTF-8 BOM (\uFEFF) at the start of CSV files. This turns the first column header into "name" instead of "name". Strip the BOM or re-save without it.

Encoding issues: CSV files from Windows may be in Windows-1252 (Latin-1). Garbled characters like é instead of é mean a charset mismatch. Re-save as UTF-8 first.

Duplicate headers: Some CSV exports repeat column names. The second occurrence overwrites the first in most converters. Rename duplicates before converting.

Converting CSV to JSON in Code

JavaScript (Node.js) with csv-parse:

// npm install csv-parse
import { parse } from "csv-parse/sync";
import { readFileSync, writeFileSync } from "fs";

const content = readFileSync("data.csv", "utf8");
const records = parse(content, {
  columns: true,           // use first row as keys
  skip_empty_lines: true,
  cast: true,              // auto-infer types (numbers, booleans)
  trim: true,              // trim whitespace from cells
});

writeFileSync("data.json", JSON.stringify(records, null, 2));
console.log(`Converted ${records.length} rows`);

Python with csv module (stdlib):

import csv, json

def infer_type(value):
    if value == "" or value.lower() == "null":
        return None
    if value.lower() in ("true", "false"):
        return value.lower() == "true"
    try:
        return int(value)
    except ValueError:
        pass
    try:
        return float(value)
    except ValueError:
        return value

with open("data.csv", encoding="utf-8-sig") as f:  # utf-8-sig strips BOM
    reader = csv.DictReader(f)
    data = [{k: infer_type(v) for k, v in row.items()} for row in reader]

with open("data.json", "w") as f:
    json.dump(data, f, indent=2)

print(f"Converted {len(data)} rows")

Python with pandas (recommended for large files):

import pandas as pd
import json

df = pd.read_csv("data.csv", encoding="utf-8-sig")
# pandas auto-detects types

result = df.to_dict(orient="records")
with open("data.json", "w") as f:
    json.dump(result, f, indent=2, default=str)  # default=str handles NaN

print(df.dtypes)        # verify column types
print(df.head())        # preview first 5 rows

Large CSV Files

For large files (millions of rows), stream instead of loading everything into memory:

// Stream CSV → JSON without holding all rows in memory
import { createReadStream, createWriteStream } from "fs";
import { parse } from "csv-parse";
import { stringify } from "JSONStream"; // npm install JSONStream

const output = createWriteStream("output.json");
output.write("[\n");
let first = true;

createReadStream("large.csv")
  .pipe(parse({ columns: true, cast: true }))
  .on("data", (row) => {
    if (!first) output.write(",\n");
    output.write(JSON.stringify(row));
    first = false;
  })
  .on("end", () => output.end("\n]\n"));

Best Practices

1. Validate the output JSON — paste it into JSONKit's Formatter to verify structure
2. Handle nulls explicitly — decide whether empty cells become null or are omitted
3. Check for duplicate headers before converting
4. Strip BOM when loading Excel-exported CSVs with utf-8-sig encoding
5. Test type detection — verify numbers and booleans are inferred correctly, not left as strings

Use JSONKit's CSV to JSON tool to convert any CSV in seconds — no upload, no account, no code required.