CSV para JSON

Converting CSV to JSON: Parsing Tabular Text into Structured Data

CSV (Comma-Separated Values) is one of the oldest and most widely used data exchange formats, predating JSON by decades. Despite its apparent simplicity — rows of values separated by commas — CSV parsing is deceptively complex. Fields can contain commas, line breaks, and quotes that must be handled according to specific escaping rules. Unlike JSON, CSV carries no type information: every value is a string, and the consuming application must infer whether 42 is a number, true is a boolean, or 2024-01-15 is a date. Converting CSV to JSON transforms flat, untyped tabular data into a structured, typed array of objects — bridging the gap between spreadsheet exports, database dumps, and modern web APIs that expect JSON input.

CSV Parsing Rules and Real-World Challenges

RFC 4180 defines the formal grammar for CSV files, but real-world CSV data frequently deviates from this specification. A robust CSV-to-JSON converter must handle both compliant and non-compliant inputs gracefully. The core parsing challenges include:

• Quoted fields: When a field value contains a comma, a line break, or a double quote, it must be enclosed in double quotes. The value "New York, NY" is a single field despite containing a comma. Parsers that split on commas without respecting quoting will incorrectly fragment such values into multiple fields.
• Escaped quotes within fields: A literal double quote inside a quoted field is represented by doubling it: "She said ""hello""" represents the value She said "hello". Failing to handle escaped quotes produces truncated or malformed field values.
• Line breaks within fields: RFC 4180 allows CRLF sequences inside quoted fields. A field like "Line 1 Line 2" spans two physical lines but represents a single value. Naive line-by-line parsing will split this into two incomplete records.
• Trailing commas and empty fields: A row ending with a comma (a,b,c,) implies a fourth empty field. Consecutive commas (a,,c) indicate an empty second field. These must map to empty strings or null values in the JSON output.
• Inconsistent row lengths: Unlike a database table, CSV files may contain rows with different numbers of fields. A converter must decide whether to pad short rows with nulls, truncate long rows, or reject the file entirely.

The combination of quoting rules, embedded line breaks, and inconsistent field counts makes CSV parsing a task that regex alone cannot solve reliably. A state machine or dedicated parser that tracks whether the current position is inside or outside a quoted region is the only robust approach for production use.

Header Detection and Column Naming Strategies

The first row of a CSV file is often a header row containing column names, but this is a convention rather than a requirement — RFC 4180 mentions headers as optional. When converting CSV to JSON, header handling directly determines the shape of the output:

• First row as headers: The most common convention. Column names from the first row become JSON object keys. The CSV row name,age,city followed by Alice,30,London produces {"name":"Alice","age":"30","city":"London"}. Headers with spaces or special characters may need sanitization to produce valid JSON keys.
• Headerless CSV: Some CSVs have no header row — the data starts immediately. In this case, the converter must either generate synthetic column names (like column_0, column_1) or output JSON arrays instead of objects: [["Alice","30","London"]].
• Custom header mapping: Users may want to rename or remap columns during conversion. A header row containing fname,lname might be mapped to firstName,lastName in the JSON output, or columns may be selectively included or excluded.
• Duplicate headers: CSV files exported from spreadsheets sometimes contain duplicate column names (e.g., two columns both named Total). Since JSON object keys must be unique, the converter must either rename duplicates (appending a suffix like Total_1, Total_2) or merge them.
• Multi-row headers: Some enterprise exports use two or three header rows for hierarchical column grouping. These require special handling — typically concatenating the header rows or mapping them to nested JSON structures.

Reliable header detection often relies on heuristics: if the first row contains non-numeric values while subsequent rows are mostly numeric, it is likely a header. However, no heuristic is perfect, and explicit user input on whether headers are present is always the safest approach.

Type Inference from String Values

CSV's fundamental limitation is that all values are strings. The text 42 in a CSV cell has no intrinsic type — it could represent a number, a postal code, a string identifier, or part of a phone number. Converting to JSON requires deciding which values to parse as typed primitives and which to keep as strings. Common type inference rules include:

• Numbers: Values matching integer or floating-point patterns (42, 3.14, -7.5e2) can be converted to JSON numbers. However, leading zeros (007), phone numbers (+1234567890), and ZIP codes (02101) look numeric but should remain strings to preserve their semantics.
• Booleans: Values like true, false, yes, no, 1, 0 may represent booleans. Without schema information, it is impossible to know whether 1 means true or the number one.
• Null values: Empty fields, the literal string null, NULL, N/A, -, or none may all represent missing data. The converter must decide which representations map to JSON null versus remaining as literal strings.
• Dates and timestamps: Values like 2024-01-15 or 01/15/2024 14:30:00 could be parsed into ISO 8601 strings or left as-is. Date formats vary by locale (DD/MM vs MM/DD), making automated inference unreliable without context.

Conservative converters keep all values as strings (the safest approach), while aggressive converters attempt full type inference. The best strategy depends on the downstream consumer: APIs with strict schemas benefit from typed values, while data pipelines may prefer strings to avoid precision loss or semantic errors.

RFC 4180 Compliance and Real-World Deviations

RFC 4180 (published in 2005) formalized the CSV format with specific rules: CRLF line endings, optional header row, double-quote escaping, and comma as the sole delimiter. However, the vast majority of CSV files encountered in practice deviate from this specification in one or more ways:

• Alternative delimiters: Tab-separated values (TSV), semicolons (common in European locales where commas are decimal separators), and pipes (|) are all called "CSV" despite using different separators. A converter must support configurable delimiters or auto-detect the separator by analyzing the first few rows.
• Line ending variations: RFC 4180 specifies CRLF ( ), but Unix systems produce LF-only ( ) and legacy Mac systems used CR-only ( ). Robust parsers must handle all three variants.
• Character encoding: CSV files may be encoded in UTF-8, UTF-16, Latin-1, Windows-1252, or Shift-JIS. A BOM (Byte Order Mark) at the start of the file can indicate encoding, but many files omit it. Incorrect encoding detection produces garbled characters in the JSON output.
• Whitespace handling: Some exporters pad fields with leading or trailing spaces (e.g., Alice , 30 , London). RFC 4180 considers these spaces part of the field value, but many users expect them to be trimmed during parsing.
• Single-quote escaping: Some applications use single quotes instead of double quotes for field enclosure, or use a backslash (") instead of the RFC-specified doubled quote ("") for escaping.

Production CSV-to-JSON converters typically implement a superset of RFC 4180 with configurable options for delimiter, quote character, escape character, encoding, and trimming behavior. This flexibility is essential when processing data from diverse sources such as Excel exports, database dumps, legacy mainframe systems, and web scraping outputs.

Code Examples

CSV to JSON conversion with quoted fields and type inference

Input CSV:
name,age,city,active,score
"Alice Smith",30,"New York, NY",true,95.5
"Bob ""The Builder"" Jones",25,London,false,
Charlie,,"Paris, France",true,87.3

Output JSON (with type inference):
[
  {
    "name": "Alice Smith",
    "age": 30,
    "city": "New York, NY",
    "active": true,
    "score": 95.5
  },
  {
    "name": "Bob \"The Builder\" Jones",
    "age": 25,
    "city": "London",
    "active": false,
    "score": null
  },
  {
    "name": "Charlie",
    "age": null,
    "city": "Paris, France",
    "active": true,
    "score": 87.3
  }
]

Parsing notes:
- "New York, NY" → comma inside quotes preserved as single field
- ""The Builder"" → escaped quotes resolved to literal quotes
- Empty field (Bob's score, Charlie's age) → null in JSON
- "true"/"false" → boolean type inference
- "30", "95.5" → numeric type inference

Standards & Specifications

• RFC 4180 — Common Format and MIME Type for Comma-Separated Values (CSV) Files — defines quoting rules, CRLF line endings, header conventions, and the text/csv MIME type