CSV to JSON Converter: Transform CSV Data to JSON Online

# CSV row with address field containing a comma
name,address,city
"Smith, John","123 Main St, Apt 4",Springfield

# naive split(",") produces:
# [""Smith", " John"", ""123 Main St", " Apt 4"", "Springfield"]
# 5 tokens instead of 3 — broken

# RFC 4180-compliant parser produces:
# ["Smith, John", "123 Main St, Apt 4", "Springfield"]
# 3 tokens — correct

# CSV with multi-line description field
id,name,description
1,Widget,"This is a
multi-line description
with three lines"
2,Gadget,"Single line description"

# A line-by-line reader breaks this into 5 rows instead of 2
# A proper parser tracks whether we are inside a quoted field
# and only treats a bare newline as a record separator if unquoted

# RFC 4180: literal quotes are escaped by doubling
id,description
1,"He said ""hello"" to her"

# Parsed correctly: description = He said "hello" to her
# Naive regex looking for closing quote at first " after opening " breaks here

# Some exporters (MySQL, some Python scripts) use backslash escaping instead:
1,"He said "hello" to her"
# This is NOT RFC 4180 — but fast-csv handles it via the escape option

# Excel "Save As > CSV UTF-8" prepends a BOM: EF BB BF (3 bytes)
# The BOM is invisible in most text editors but breaks parsers

# Node.js: detect and strip the BOM before parsing
import { readFileSync } from 'fs'

let content = readFileSync('data.csv', 'utf8')

// Strip UTF-8 BOM if present
if (content.charCodeAt(0) === 0xfeff) {
  content = content.slice(1)
}

// Now safe to parse
import Papa from 'papaparse'
const result = Papa.parse(content, { header: true })

import Papa from 'papaparse'

// Convert a File object (from file input or drag-and-drop)
async function csvFileToJson(file: File): Promise<Record<string, unknown>[]> {
  return new Promise((resolve, reject) => {
    Papa.parse(file, {
      header: true,          // Use first row as JSON keys
      skipEmptyLines: true,  // Skip blank rows
      dynamicTyping: true,   // Auto-convert "true"→true, "42"→42, etc.
      encoding: 'UTF-8',     // Handle UTF-8 (strips BOM automatically)
      worker: true,          // Parse in Web Worker — non-blocking
      complete: (results) => {
        if (results.errors.length > 0) {
          // Report parsing errors with row numbers
          console.warn('CSV parsing warnings:', results.errors)
        }
        resolve(results.data as Record<string, unknown>[])
      },
      error: reject,
    })
  })
}

// Convert a CSV string (e.g., fetched from an API)
function csvStringToJson(csvString: string): Record<string, unknown>[] {
  const { data, errors } = Papa.parse<Record<string, unknown>>(csvString, {
    header: true,
    skipEmptyLines: true,
    dynamicTyping: true,
  })

  if (errors.length > 0) {
    throw new Error(`CSV parse errors: ${errors.map(e => e.message).join(', ')}`)
  }

  return data
}

// Auto-detect delimiter (handles semicolons, tabs, pipes)
function csvAutoDetect(csvString: string) {
  return Papa.parse(csvString, {
    header: true,
    delimiter: '',  // Empty string = auto-detect
    dynamicTyping: true,
    skipEmptyLines: true,
  })
}

import { createReadStream } from 'fs'
import { parse } from 'csv-parse'
import { pipeline } from 'stream/promises'
import { Transform } from 'stream'

// Stream a large CSV file without loading it all into memory
async function streamCsvToJson(filePath: string): Promise<void> {
  const records: Record<string, unknown>[] = []

  const parser = parse({
    columns: true,           // Use header row as column names
    skip_empty_lines: true,
    trim: true,              // Trim whitespace from field values
    bom: true,               // Auto-strip UTF-8 BOM (Excel exports)
    cast: true,              // Auto-cast numbers and booleans
    cast_date: false,        // Don't auto-cast dates (too error-prone)
    relax_quotes: false,     // Strict RFC 4180 quote handling
    on_record: (record) => {
      // Transform here — runs per record, not after full parse
      return {
        ...record,
        // Explicit date parsing is safer than auto-cast
        createdAt: record.created_at
          ? new Date(record.created_at as string)
          : null,
      }
    },
  })

  const input = createReadStream(filePath)

  // Process each record as it arrives (constant memory usage)
  for await (const record of input.pipe(parser)) {
    records.push(record)
    // In real code: write to database or downstream service here
    // rather than accumulating in memory
  }

  return records
}

// One-shot small file conversion
import { parse as parseSync } from 'csv-parse/sync'
import { readFileSync } from 'fs'

function csvFileToJsonSync(filePath: string) {
  const content = readFileSync(filePath, 'utf8')
  return parseSync(content, {
    columns: true,
    skip_empty_lines: true,
    bom: true,
    cast: true,
  })
}

import csv
import json
from pathlib import Path

def csv_to_json(csv_path: str) -> list[dict]:
    """
    Convert CSV to a list of dicts (JSON-serializable).
    Handles UTF-8 BOM from Excel exports via encoding='utf-8-sig'.
    """
    records = []
    with open(csv_path, 'r', encoding='utf-8-sig', newline='') as f:
        # newline='' is required by csv module — it handles its own newlines
        reader = csv.DictReader(f)
        for row in reader:
            # row is an OrderedDict of {header: value} — all strings
            records.append(dict(row))
    return records

def csv_to_json_with_types(csv_path: str) -> list[dict]:
    """Type coercion: convert numeric and boolean strings to native types."""
    def coerce(value: str):
        if value.lower() in ('true', 'false'):
            return value.lower() == 'true'
        try:
            return int(value)
        except ValueError:
            pass
        try:
            return float(value)
        except ValueError:
            pass
        return value if value != '' else None  # Empty string → None

    records = []
    with open(csv_path, 'r', encoding='utf-8-sig', newline='') as f:
        reader = csv.DictReader(f)
        for row in reader:
            records.append({k: coerce(v) for k, v in row.items()})
    return records

# Usage
data = csv_to_json_with_types('export.csv')
print(json.dumps(data, indent=2, default=str))

# 1. Leading zeros lost when cast to number
# US ZIP codes, phone numbers, product codes with leading zeros
zip_code,phone
"07030","0012125551234"
# Auto-cast: zip_code: 7030 (wrong!), phone: 12125551234 (wrong!)
# Fix: explicitly declare these columns as strings, not numeric

# 2. Booleans misidentified
# Some CSV files use "yes"/"no", "1"/"0", "Y"/"N" for boolean fields
active
yes    # Cast to string "yes" — not boolean true
1      # Cast to number 1 — not boolean true (in most parsers)

# 3. Excel date serial numbers
# Excel stores dates as days since 1900-01-00 and exports as integers
# in some formats
created_date
45000    # Excel serial: = 2023-03-16
         # Auto-cast produces 45000 (number), not a date
         # Fix: use explicit date parsing with date-fns or dayjs

# 4. Scientific notation for large numbers
product_id
1.23456789E+15    # Auto-cast to 1234567890000000 — precision lost
                  # Fix: quote these values in the CSV export

# Safe approach: keep dynamicTyping off, parse manually
const result = Papa.parse(csv, {
  header: true,
  dynamicTyping: false,  // Keep everything as strings
})

// Then apply explicit type casting per column
const processed = result.data.map(row => ({
  id: row.id,                          // Keep as string (may have leading zeros)
  amount: parseFloat(row.amount),      // Explicit number cast
  active: row.active === 'true',       // Explicit boolean cast
  createdAt: new Date(row.created_at), // Explicit date parse
  zip: row.zip.padStart(5, '0'),       // Restore leading zeros
}))

# CSV with dot-notation headers
id,name,address.street,address.city,address.zip,contact.email
1,Alice,123 Main St,Springfield,62701,[email protected]
2,Bob,456 Oak Ave,Shelbyville,62702,[email protected]

# Standard conversion (flat):
[
  { "id": "1", "name": "Alice", "address.street": "123 Main St", ... }
]

# With dot-notation expansion (using 'flat' npm package):
import { unflatten } from 'flat'

const flat = { id: '1', name: 'Alice', 'address.street': '123 Main St',
               'address.city': 'Springfield', 'address.zip': '62701',
               'contact.email': '[email protected]' }

unflatten(flat)
// {
//   id: '1',
//   name: 'Alice',
//   address: { street: '123 Main St', city: 'Springfield', zip: '62701' },
//   contact: { email: '[email protected]' }
// }

// Apply to the full parsed CSV:
import Papa from 'papaparse'
import { unflatten } from 'flat'

const { data } = Papa.parse(csvString, { header: true, dynamicTyping: true })
const nested = data.map(row => unflatten(row))

# Miller (mlr) — the Unix tool for CSV/JSON/NDJSON at scale
# Handles GB-scale files in constant memory via streaming
brew install miller  # macOS
apt install miller   # Ubuntu

# CSV to JSON array
mlr --c2j cat data.csv

# CSV to NDJSON (JSON Lines — one object per line)
mlr --c2l cat data.csv

# CSV to JSON with type inference and filtering
mlr --c2j   filter '$revenue > 10000'   then sort -f country   then cut -f id,name,country,revenue   data.csv

# Python's csvkit for quick conversions
pip install csvkit
csvjson data.csv > data.json        # Convert to JSON
csvjson --stream data.csv > data.ndjson  # Convert to NDJSON