CSV to JSON / JSON to CSV

CSV is a grid of text. Every cell is a string, there is no type information, and there is no way to express nesting. JSON has types (string, number, boolean, null) and structures (objects and arrays). Converting from CSV to JSON therefore means inferring things the source file never stated, and every inference is a chance to get it wrong.

Number inference is the sharpest edge. A postal code of 110001 is a string that happens to look like a number, and converting it to one is harmless until a code beginning with zero loses its leading digit. Phone numbers, order references, and product SKUs have the same problem. So do long identifiers: JSON numbers are IEEE 754 doubles, so any ID longer than about 15 digits loses precision silently. Keep identifier columns as strings unless you will do arithmetic on them.

The comma in comma-separated values is a convention, not a rule. European locales commonly use a semicolon because the comma is their decimal separator, and exports from Excel follow the machine's regional settings, which is why the same spreadsheet produces different files on different computers. Tab-separated files avoid the ambiguity entirely and are worth requesting when you control the source.

Within a comma-separated file, any value containing a comma, a double quote, or a line break must be wrapped in double quotes, and a literal double quote inside is written twice. So the address 221B Baker Street, London must appear as "221B Baker Street, London". Files produced by hand or by string concatenation frequently get this wrong, and the symptom is a row that parses with too many columns, shifting every value after it. If a converted record has fields in the wrong keys, look for an unquoted comma on that line.

The header row becomes your JSON keys, so it is worth cleaning before conversion rather than after. Trim stray spaces, since "Order ID" and "Order ID " become two different keys. Decide on one case convention, usually snake_case or camelCase, and apply it to every column. Remove characters that are awkward to address in code, such as dots and hyphens. Duplicate header names are a real hazard: the second column silently overwrites the first in an object, so rename them at source.

Empty cells need a deliberate decision too. An empty CSV cell can reasonably become an empty string, a null, or an omitted key, and downstream code treats those three very differently. Null is usually the most honest, since it distinguishes "no value recorded" from "recorded as blank". Finally, CSV cannot nest, so a converted file is always a flat array of objects. If the destination expects nested structures, group the flat records afterwards on whichever column identifies the parent.