CSV to JSON: Handling Data Types, Nesting, and Arrays Correctly
CSV has no concept of a number, a boolean, or a nested object — every single cell is just text, and it's up to whatever reads the file to decide what that text actually means. Converting to JSON, a format that does distinguish between a string, a number, and a boolean, forces a set of decisions that a naive converter tends to get wrong silently rather than loudly.
The type-guessing problem
The cell 042 is ambiguous on its own: is it the number 42, or a string that happens to look numeric — a zip code, a product code, an ID that legitimately starts with a zero? A converter that blindly casts numeric-looking strings to JSON numbers will silently strip that leading zero, and the same problem hits phone numbers, ISBNs, and any ID system with fixed-width, zero-padded formatting. The safe default is treating every value as a string unless a column is unambiguously numeric across every row — and even then, giving you a way to override that per-column, since the "looks numeric" heuristic will eventually guess wrong on a real dataset.
Booleans have the same issue from a different angle: TRUE/FALSE, 1/0, yes/no, and Y/N all show up in real-world exports depending on what produced the CSV, and a converter that only recognizes one convention will pass the rest through as plain strings — which usually isn't wrong, exactly, but isn't what most people expect when they see a column that's clearly boolean data.
CSV genuinely can't represent nested structure
This is the part that's a limitation of CSV itself, not a converter bug. A relational-style row like name, address.city, address.zip is a common workaround — dot-notation column headers that a converter can reassemble into a nested object — but it only works for a fixed, known shape. It falls apart the moment the nesting is variable-depth or genuinely different per row, which CSV was never designed to hold in the first place. If your data is naturally nested or hierarchical, CSV is the wrong format to be exporting in at all; JSON, or a JSON Lines file, should be the source of truth, with CSV reserved for the flat, tabular parts of the data.
Repeated columns as arrays
A common pattern for representing a one-to-many relationship in a flat file: tag1, tag2, tag3 as separate columns, or a single tags column with values separated by a semicolon or pipe inside the cell. Neither is standard — both are conventions a specific export process invented — so a general-purpose converter can't safely guess which one you're using. Recognizing a delimiter-separated cell and splitting it into a JSON array on request is useful, but it should be something you explicitly turn on for a specific column, not an automatic behavior applied to every field that happens to contain a semicolon (which will occasionally be genuine punctuation, not a separator).
Empty cells: null, empty string, or missing key entirely?
These are three different things in JSON, and CSV can't distinguish between them at all — an empty cell is just an empty cell. null usually reads as "this field is explicitly known to have no value," an empty string as "this field has a value, and that value is blank," and an omitted key as "this field wasn't recorded for this row." Since CSV can't express which one was actually meant, picking a single consistent convention (most APIs expect either null or an omitted key, rarely an empty string, for genuinely missing data) and applying it uniformly avoids a downstream consumer having to guess the same thing your converter already had to guess.
Try it
FreeToolDev's CSV to JSON converter defaults to treating ambiguous values as strings rather than guessing, and lets you handle a whole batch of files with the same settings in one pass — useful for exactly this kind of "decide the rules once, apply them consistently" problem. It also auto-detects file encoding, which is a related but separate failure mode covered in this post on CSV encoding issues.