CSV to JSON Converter

RFC 4180 section 2.6 requires any field containing a comma, a double quote, or a line break to be wrapped in double quotes, and section 2.7 escapes a literal double quote inside such a field by doubling it. So "Smith, John","He said ""hi""" is two fields, the second holding He said "hi".

The consequence people underestimate is that a quoted field may contain a line break, which means one logical record can span several physical lines. wc -l is therefore not a row count, reading the file line by line is not a valid parse, and split('\n') breaks the first time somebody presses Enter inside a notes field. Python's csv module documents this explicitly: files must be opened with newline='', or the platform's newline translation corrupts embedded breaks before the reader ever sees them.

RFC 4180 also specifies CRLF between records, yet LF-only files are everywhere and every usable parser accepts both. It is an Informational document describing existing practice rather than a standard anyone is obliged to follow, so treat it as a careful account of one dialect rather than a guarantee about the file in front of you.

The C in CSV is aspirational. Exports produced in locales where the comma is the decimal separator use a semicolon instead, because Excel follows the operating system list separator — the same spreadsheet saved in Berlin and in Boston yields structurally different files. Tabs and pipes turn up routinely in data-engineering exports.

Parsers guess. Papa Parse works through a delimitersToGuess list that defaults to comma, tab, pipe, semicolon, and the ASCII record and unit separators. Python's csv.Sniffer is stricter and raises Could not determine delimiter when it cannot decide, which is more honest but means a batch job dies on one unusual file.

The dangerous case is a confident wrong guess rather than a failure. A semicolon-delimited file parsed as comma-delimited raises nothing: it produces exactly one column per row, whose name is the entire header line and whose value is the entire data line. Every row converts, the row count is correct, and every record is useless. If you control the producer, agree the delimiter out of band and stop inferring it.

UTF-8 files frequently open with the three bytes EF BB BF. Excel writes them whenever you save as "CSV UTF-8", and a good number of Windows exporters do the same. A parser that does not strip the mark folds it into the first header name, so the key becomes "\uFEFFid" rather than id.

This produces one of the more maddening debugging sessions available, because row.id is undefined while the key printed in your console looks character-for-character like id. Only the first column is affected, so every other field parses perfectly and the symptom reads as a data problem rather than an encoding one.

Underneath sits a structural gap: a CSV file carries no encoding declaration. RFC 4180 registers the text/csv media type with an optional charset parameter defaulting to US-ASCII, which is a formal way of saying the encoding must reach you out of band. Guess wrong and accented characters arrive as mojibake — é where é belonged — with no error raised anywhere in the pipeline. Strip a BOM if present and normalise header names before keying anything off them.

Every value in a CSV is text, so any type you get back was invented by the reader. The JavaScript coercions are unforgiving. Number("01234") is 1234, so postal codes, sort codes, and zero-padded part numbers lose their prefix permanently. Number("") and Number(" ") are both 0, turning a blank cell into a real zero, which is a very different thing in an amount column. Number("0x10") is 16 and Number("1e5") is 100000, so codes that merely look numeric become unrelated integers.

Library defaults add their own hazards. Papa Parse applies these rules when dynamicTyping is enabled. pandas treats a fixed list of strings as missing values by default, and that list contains the literal NA — so Namibia's ISO 3166 country code parses as NaN in any country column unless you pass keep_default_na=False.

Excel is the worst offender because it damages the file rather than merely the reading of it. It stores 15 significant digits, so a 19-digit Snowflake ID or a 16-digit card number has its tail rewritten to zeros and is displayed in scientific notation; re-save and that loss is now in the data. Its date coercion was severe enough that the HUGO Gene Nomenclature Committee renamed 27 human genes across 2019 and 2020 — SEPT1 became SEPTIN1, MARCH1 became MARCHF1 — having concluded that changing the identifiers was more achievable than changing everyone's spreadsheet settings. Read every column as a string and opt into typing deliberately.

RFC 4180 section 2.4 says every record should contain the same number of fields, and real exports break this constantly. A short row usually means trailing values were omitted; a long row almost always means an unescaped delimiter inside a value. Strict parsers raise, lenient ones pad with empty strings or discard the surplus, and either way you receive a well-formed object holding shifted data.

Converting to JSON introduces a failure CSV itself does not have: duplicate column names. Two columns both called name cannot both become keys of one object, so one silently overwrites the other while the record count stays reassuringly correct. Rename columns before the conversion rather than after discovering the loss.

Two smaller cases round this out. A trailing newline at end of file is completely normal, but a parser that does not skip blank lines emits a final record with every field empty, which then trips a not-null constraint three systems downstream. And exports from reporting tools often place a title or a filter summary above the real header, so row one is not the header at all — skip those rows explicitly instead of letting the parser assume. When you need to send the result back the other way, the JSON to CSV converter handles the reverse projection and its own set of trade-offs.