Skip to main content

Developer guide

Convert CSV to JSON without losing leading zeros or columns

By MultiZest · · 3 min read

A CSV file looks simple until a name contains a comma, a note spans two lines or a postal code starts with zero. A reliable conversion must parse field boundaries and preserve values before applying application-specific types.

The practical takeawayParse quoted fields correctly, keep identifiers as strings and validate the receiving application’s schema separately.

Start with the header and delimiter

The header supplies JSON property names. Make every header non-empty and unique so one column cannot overwrite another. Choose comma, semicolon or tab according to the actual export, not only its filename extension.

A row must have the same number of fields as the header. Missing or extra separators can otherwise shift one person’s value into the next column. The converter reports inconsistent rows rather than silently repairing them.

name,postal_code,note
Amina,00123,"Hello, world"
Bilal,00456,"He said ""yes"""

Quoted fields require a parser

Splitting every line on a comma does not correctly handle the example above. A comma inside quotation marks belongs to the note field. A doubled quotation mark represents one literal quote, and a quoted field may contain a line break.

Use the CSV/JSON converter and inspect a row containing those edge cases. A successful parse does not tell you whether the source data is factually correct, but it does establish consistent field structure.

Keep identifiers separate from quantities

A postal code such as 00123 is an identifier, not the quantity one hundred twenty-three. MultiZest keeps CSV values as strings. That also avoids converting an empty string to zero or rounding a long account identifier.

Convert a quantity deliberately when the destination requires a number. Large integers can exceed JavaScript’s safe precision, so quote identifiers in JSON as well. Dates should use a format and timezone that the receiving application expects.

Know what changes in the reverse direction

JSON-to-CSV takes the union of object keys as columns. Missing and null values become empty fields. Nested objects and arrays become JSON text inside a cell; converting back does not automatically reconstruct their original types.

Formula protection is enabled for spreadsheet exports. Potentially active cells receive an apostrophe, which can also change negative-number cells. Disable it only if preserving exact text is required and you understand the receiving workflow.

Verify a small sample before a large import

Compare the number of rows and the header names. Check blank values, Unicode text, leading-zero codes, quoted notes and the first and last records. Import a small sample into the receiving system before replacing a larger dataset.

The converter accepts up to 1,000,000 characters, 20,000 data rows and 2,000 columns. Sparse JSON expansion is capped at 1,000,000 output cells. Larger data pipelines need a processing environment sized for that workload.

Test one awkward row before the full import

Choose a row with a leading-zero code, a non-English name, an empty value and a comma inside a quoted note. That small sample tests the assumptions most likely to change meaning during conversion.

Compare the converted property names and types with the schema expected by the receiving application. A parseable JSON object can still be unsuitable if its keys differ or an identifier has been treated as a calculation value.

Review a spreadsheet distribution copy separately

CSV exported for a spreadsheet can need formula protection, while a trusted machine import may require exact unprefixed values. Decide who will receive the file before selecting that setting.

Keep the source and note any intentional changes such as trimming spaces or representing nested values as cell text. A round trip through CSV does not preserve every JSON type or structure automatically.

Try the tools used in this guide

Sources and further reading

Continue reading