Data & spreadsheets · CSV Cleaner
Converting Between CSV and JSON: Shapes, Types and What Gets Lost
· How it works
csv json data-formats
CSV is flat text and JSON is nested, typed data, so converting between them involves decisions. This post explains the common JSON shapes for tabular data, how types are inferred or not, and what cannot survive the round trip.
An API wants JSON, the export is CSV, and every number arrives as a string — why the two formats disagree about types
A spreadsheet export can show 42 without saying whether those characters mean a count, a product code or an identifier. JSON can distinguish a number from a string, but the CSV source cannot supply that distinction. ToolAcre therefore chooses the conservative representation: every cell becomes a JSON string, including digits, true-looking words and empty cells.
That decision keeps text such as 0012 intact and makes the conversion predictable for an API integration. It also means downstream code must cast fields under its own schema before doing arithmetic. Treating the generated file as already typed merely moves an assumption from the converter into less visible application code.
The usual target shape — an array of objects keyed by header, and why headers therefore need to be clean and unique
The route produces a top-level array with one object for each data row. Header cells become object keys, and values are taken from the same column positions. A blank third header becomes column_3, while a second header named id becomes id_2 instead of overwriting the first id field.
Clean, unique names are useful, but the converter does not lowercase, transliterate or replace spaces. It trims a header only while computing the key, then invents a positional name or numeric suffix where needed. If an API requires customer_email rather than Customer Email, rename that column deliberately before relying on the output contract.
Alternative shapes — array of arrays and column-oriented objects, and when each is the better fit
The outline proposed arrays of arrays and column-oriented objects as selectable targets. They are reasonable data designs, but this interface does not offer them. Its output is always a JSON array of flat records keyed by the first CSV row, indented with two spaces for readable inspection.
That narrow choice removes ambiguity about where a column name lives and keeps every record shaped alike. If another consumer expects arrays, transform the downloaded JSON under that consumer’s schema. Claiming that ToolAcre chooses among several layouts would describe controls and branches that do not exist in the configuration or panel code.
This converter emits one shape: a top-level array of flat objects
No type guessing occurs. The CSV text 42 becomes "42", false becomes "false", and a blank becomes "" rather than null. This is explicit in the tool record and implementation, which map cells directly to string properties. The route does not inspect a column and decide that all non-empty values are numeric.
Preserving strings also separates this article from the published discussion of spreadsheet applications changing leading zeros, dates and long identifiers. Here the point is the converter’s actual contract: it avoids that class of coercion. Consumers remain responsible for parsing known quantities and leaving identifiers untouched.
Types are preserved as text; ToolAcre performs no inference
In the reverse direction, ToolAcre accepts a top-level JSON array whose items are objects. It builds columns from the union of keys across every object, in first-seen order, so a field introduced by a later record is not lost. Missing, null and undefined properties become empty CSV cells.
An object or array stored inside a property is serialized as JSON text inside that one cell. This preserves a textual representation but does not turn nested structure into additional columns or rows. A non-array root and an array containing primitive values are rejected because neither matches the table model this route supports.
JSON-to-CSV accepts an array of objects and writes nested values as JSON text
Consider name,active,count followed by Ada,true,007. The JSON result is an array containing an object with name "Ada", active "true" and count "007". Converting that flat object array back produces the same three textual cells because no type inference removed the zeros or converted the boolean-looking word.
Now add a blank header before another value. The JSON key becomes column_4, so the data remains reachable even though the source name was missing. Add an extra cell beyond the header width, however, and the loader warns about a ragged row; that surplus cell has no key and is absent from JSON.
What this does not cover — deeply nested JSON, schema validation and streaming very large JSON documents
The route is not a schema validator, recursive flattener or streaming JSON processor. It accepts one documented shape and reports invalid JSON or unsupported roots with a specific error. Deeply nested records need a mapping designed around their domain rather than an automatic promise that punctuation in a key creates structure.
The input file check permits files up to the configured 50 MB, and CSV parsing happens in a worker. Those facts do not establish streaming: file text and completed rows are still materialized for conversion. For very large JSON workflows, use a system whose implementation explicitly documents incremental parsing rather than inferring it here.
Schema validation, primitive arrays and non-array roots are outside the accepted shape
Conversion is a visible set of choices: first row as keys, flat objects as records, and strings as values. ToolAcre makes those choices stable instead of guessing from a sample. Blank and repeated headers receive deterministic keys, while ragged input is surfaced before an unnamed surplus value can disappear unnoticed.
Use the preview to confirm the delimiter and row shape, resolve warnings, then download or copy the JSON. The resulting file is suitable for code that knows its own schema. It is intentionally not a substitute for that schema, and its restraint is what protects textual IDs during the format change.