All lessons T
Data Transformation beginner 10 min

Turn Messy CSV Into API-Ready JSON

Clean a spreadsheet export into predictable JSON, then inspect the shape before handing it to an API.

Scenario

Operations exported a CSV of accounts and someone needs it POSTed to a bulk import endpoint by end of day. The endpoint wants an array of JSON objects; the export has a header row and comma-separated values. Hand-wrapping rows in braces is tedious and error-prone — one missed quote and the import fails halfway through. Convert mechanically, then verify the keys came from the header row and not from a shifted column.

Broken input

name,email,plan
Ada,ada@example.test,pro
Grace,grace@example.test,team

Goal

Use CSV to JSON to convert rows into an array of objects whose keys come from the header. The conversion is deterministic, so the review effort goes into checking the header and column alignment once, not every row.

Tool sequence

  1. Open CSV to JSON.
  2. Paste the CSV including the header row — without it, the converter has to invent keys like field1.
  3. Convert and inspect the first object: keys name, email, plan with Ada's values in the right columns.
  4. Spot-check the last row too; column-shift bugs show up at the end of files with ragged rows, not the top.
  5. Validate the array against the import endpoint's contract with JSON Schema Validator / Generator, and if the codebase needs types for the rows, generate them with JSON to TypeScript Interface Generator.

Checkpoint output

[
  { "name": "Ada", "email": "ada@example.test", "plan": "pro" },
  { "name": "Grace", "email": "grace@example.test", "plan": "team" }
]

Each CSV row became one object keyed by the header fields — two rows in, two objects out, no shifted columns.

Common mistakes

  • Skipping header cleanup, so display labels like Account ID become awkward JSON keys mid-conversion.
  • Assuming every row has the same number of cells — a stray comma inside an unquoted value shifts every column after it.
  • Sending converted JSON to the API before checking the expected schema; the endpoint may want {"accounts": [...]} rather than a bare array.
  • Letting a spreadsheet "help" first — Excel loves converting long IDs to scientific notation before you ever reach the converter.

Related tools and lessons