CSV to JSON Conversion: Handling Complex Data

Introduction

Converting CSV to JSON is a common task in data processing. While it seems straightforward, handling edge cases like quoted fields, nested data, and special characters requires careful implementation.

Basic Conversion

CSV Input

name,age,city
John,30,New York
Jane,25,Boston

JSON Output

[
  {"name": "John", "age": 30, "city": "New York"},
  {"name": "Jane", "age": 25, "city": "Boston"}
]

Handling Edge Cases

1. Quoted Fields with Commas

name,address
"Smith, John","123 Main St, Apt 4"
"Doe, Jane","456 Oak Ave"

2. Escaped Quotes

name,quote
John,"He said ""Hello"""
Jane,"She replied ""Hi"""

3. Multiline Fields

name,description
John,"Line 1
Line 2
Line 3"

Type Detection

Smart conversion should detect data types:

// String
"John" → "John"

// Number
"30" → 30
"3.14" → 3.14

// Boolean
"true" → true
"false" → false

// Null
"null" → null
"" → null or ""

JavaScript Implementation

function csvToJson(csv) {
  const lines = csv.split('\\n');
  const headers = parseCSVLine(lines[0]);
  const result = [];

  for (let i = 1; i < lines.length; i++) {
    if (!lines[i].trim()) continue;

    const values = parseCSVLine(lines[i]);
    const obj = {};

    headers.forEach((header, index) => {
      obj[header] = convertType(values[index]);
    });

    result.push(obj);
  }

  return result;
}

function parseCSVLine(line) {
  const result = [];
  let current = '';
  let inQuotes = false;

  for (let i = 0; i < line.length; i++) {
    const char = line[i];

    if (char === '"') {
      if (inQuotes && line[i + 1] === '"') {
        current += '"';
        i++;
      } else {
        inQuotes = !inQuotes;
      }
    } else if (char === ',' && !inQuotes) {
      result.push(current);
      current = '';
    } else {
      current += char;
    }
  }

  result.push(current);
  return result;
}

function convertType(value) {
  value = value.trim();

  if (value === '') return null;
  if (value === 'true') return true;
  if (value === 'false') return false;
  if (value === 'null') return null;

  const num = Number(value);
  if (!isNaN(num)) return num;

  return value;
}

Handling Large Files

• Use streaming parsers for files > 10MB
• Process in chunks to avoid memory issues
• Consider using workers for background processing

Common Pitfalls

Libraries and Tools

• JavaScript: PapaParse, csv-parser
• Python: pandas, csv module
• Online: Our CSV to JSON Converter