How to prepare CSV data before converting it to JSON

RFC 4180 documents a common comma-separated format, including quoted fields and doubled quotation marks, but CSV files in practice may use semicolons or tabs. Confirm the delimiter, quote character, presence of a header row and text encoding.

A comma inside a quoted address is data, not a new column. A line break may also appear inside a quoted field. Avoid splitting lines or commas manually; use a parser that understands quoting rules. UTF-8 is the safest interoperable encoding for JSON.

Headers become object keys, so they should be present, unique and meaningful. Trim invisible spaces and decide a naming convention such as customer_id or customerId. Do not silently overwrite one duplicate column with another.

Keep a mapping from the original heading to the normalized key when traceability matters. Renaming “Total ($)” to totalAmount may improve code, but the unit and currency must remain documented.

CSV stores text. A converter may infer numbers, booleans or nulls, but identifiers such as 00123 must often remain strings. Dates like 03/04/2026 are ambiguous across locales, and very large integers can lose precision in some programming environments.

Create a schema for every column: type, whether empty values are allowed, accepted date format and any enumeration. JSON supports strings, numbers, booleans, null, arrays and objects, but not special date or undefined types.

An empty CSV cell could mean unknown, not applicable, intentionally blank or zero. Choose whether the JSON property should be omitted, set to null or contain an empty string. These outcomes are not interchangeable.

Validate row width, required values, numeric ranges and allowed categories. Produce an error report with row and column numbers rather than quietly discarding malformed records.

Flat columns can be mapped to nested objects—for example address.city—but that convention is not part of CSV itself. Document it and reject collisions such as having both address and address.city unless the intended result is clear.

Arrays are harder because one cell may contain another delimiter or encoded JSON. Prefer a separate related table for complex one-to-many data, or define and validate the cell format explicitly.

Parse the generated JSON again to prove it is syntactically valid, then validate it against the intended schema. Compare source and output row counts, inspect the first and last records, and sample rows containing quotes, accents, empty cells and large values.

Keep the source file and transformation settings. A repeatable conversion is easier to audit and correct than a manually edited output whose history is unknown.