How CSV Files Work (and How to Convert Them)

CSV — comma-separated values — is the lowest common denominator for tabular data. Databases, banks, sensors, spreadsheets, and every analytics tool on earth hand data to each other in this format, which is so simple it fits in one sentence: one row per line, values separated by commas. The format's quirks are what this article is about.

The basic shape

A CSV file is plain text. Each line is a record; within a line, commas split the record into fields:

name,role,age
Ada,engineer,36
Grace,engineer,85

By convention the first row is a header naming the columns. The header is optional in the format but near-universal in practice, and it is the thing every converter keys on when turning rows into named fields. RFC 4180, the closest thing CSV has to a standard, settled on the comma as delimiter and CRLF line endings — though real files arrive with commas, semicolons, or tabs, and with any line ending imaginable.

The quoting rules

What if a value itself contains a comma? Ada, engineer would split into two fields. RFC 4180 defines the way out:

a value containing a comma, a quote, or a newline
  →  wrap the whole value in double quotes
a quote inside a quoted value
  →  double it: ""

So the line "Say ""hi"", friend",36 parses into two fields: Say "hi", friend and 36. Quoted fields may even contain literal line breaks — which is exactly why "split on every newline" parsers mangle real-world files. Quotes are only required when a value contains the delimiter, a quote character, or a newline; writing them otherwise is legal too, and parsers accept both forms.

Why CSV stays messy

The format has no schema, so every consumer guesses, and three guesses cause most of the pain. Types: everything is text, so 007, 7, and 7.0 are just characters; whichever tool opens the file decides whether a leading zero survives (spreadsheets notoriously strip it from zip codes and phone numbers). Dialects: locales that write decimals as 3,14 export with semicolons, so "just split on commas" fails on perfectly legitimate European files. Encoding: plain text carries no declared character set, so a file that looks fine in one tool shows mojibake in another unless the UTF-8 BOM or source encoding is handled.

Reading CSV safely

The quoting rules mean CSV cannot be parsed with split(',') — it takes a small state machine that tracks whether it is inside quotes, treats a doubled quote as one literal quote, and only then decides whether a comma is a separator or just text. The same machine handles the CRLF-versus-LF question by stripping the carriage return. One more trap lives at the start of values: spreadsheet applications interpret cells beginning with =, +, or @ as formulas when the file is opened, so data that exports cleanly can execute as a formula on someone else's machine. Prefixing such cells with an apostrophe or tab is the usual defense when you control the exporter.

Converting between CSV and JSON

CSV and JSON describe the same data at different levels: CSV is a flat table of text, JSON is a tree of typed values (the syntax is covered in JSON Explained, and its role among config formats in YAML vs TOML vs JSON). The conversion maps a row to an object — header names become keys, cells become values:

name,role,age
Ada,engineer,36
  ↓
[
  { "name": "Ada", "role": "engineer", "age": "36" }
]

Notice that age arrived as the string "36", not the number 36: CSV cannot say what type a cell was, so keeping everything a string is the honest choice. Going the other way, JSON-to-CSV flattens cleanly only for arrays of flat objects — nested structures must be serialized into a cell somehow, and the union of all keys across rows becomes the column list. Both directions hinge on the quoting rules above: a proper converter escapes commas, quotes, and newlines inside values, so nothing spills across columns on the way out or merges on the way back. And if the JSON side came from somewhere else, validate it first — a stray trailing comma that a validator catches in seconds otherwise surfaces as a mangled column.

Try it yourself

Convert an array of objects to CSV with the JSON to CSV Converter, which escapes commas, quotes, and newlines inside values and builds the column list from every key it sees. Run the reverse with the CSV to JSON Converter, a proper state-machine parser that handles quoted fields, doubled quotes, and header rows. Paste whatever JSON you plan to convert into the JSON Validator first — it pinpoints syntax errors before they can corrupt a column.