CSV to JSON Transformation Without Losing Data
CSV has no types, quoted fields, and encoding traps. Here is how I convert CSV to JSON without introducing subtle bugs. CSV and JSON are the two formats I move data between most often. CSV is what spreadsheets export and what databases import. JSON is what APIs consume and what applications process. Transforming between them is a routine task, and it is also a task full of edge cases that trip up naive conversions. Here is how I handle CSV to JSON transformation without losing data or introducing bugs. Understand That CSV Has No Types CSV is pure text. Every value is a string, even if it looks like a number or a date. A JSON transformation that treats every CSV value as a string is safe but produces output where numbers are quoted, booleans are quoted, and nulls are the string null instead of the JSON null. A transformation that guesses types is more useful but riskier. I decide upfront whether I want typed JSON or string-only JSON. For a quick import where the receiving system parses the values itself, string-only is fine and avoids misinterpretation. For output that feeds directly into application logic, I convert obvious numbers and booleans, and leave everything else as strings. I never auto-convert dates, because date formats are too ambiguous to guess reliably. Handle Quoted Fields Correctly CSV fields can be quoted to include commas, newlines, and the quote character itself. A field wrapped in quotes can contain a comma without splitting the field. A doubled quote inside a quoted field represents a literal quote. A naive splitter that just breaks on commas will mangle any row with a quoted field. I never write my own CSV parser. The edge cases are well documented and well handled by existing libraries and online tools. The risk of a hand-written parser is that it works on clean test data and fails on real data with quoted addresses, multi-line descriptions, or embedded commas. Using a proven parser eliminates that risk entirely. Decide on the JSON Structure CSV is a flat table. JSON is hierarchical. The transformation needs a target structure. The two common shapes are an array of objects, where each row becomes an object with the header row as keys, and an array of arrays, where each row stays an array of values. The array of objects is almost always what I want, because it preserves the column names and makes the JSON self-describing. name age city Alice 30 London Bob 25 Paris becomes the following JSON, with ages kept as strings to avoid silent type coercion: [ {"name":"Alice","age":"30","city":"London"}, {"name":"Bob","age":"25","city":"Paris"} ] Note that the ages are strings. Whether to convert them to numbers depends on the downstream use, and I make that choice deliberately rather than letting the tool guess. This guide is part of the KitCraft Blog, where every tutorial pairs with a free browser-based tool — try the related tool while you read.