From CSV to JSON: Working with Tabular Data in APIs

CSV is how the business world still moves data: exports from CRMs, bank statements, and inventory reports. JSON is what most REST and serverless APIs expect. Moving between them cleanly saves hours of one-off scripts.

The usual shape: array of objects

For APIs, the friendliest JSON shape is an array of objects, one object per row, keys from the header row:

[
  { "sku": "A-100", "qty": "12", "warehouse": "East" },
  { "sku": "B-220", "qty": "3", "warehouse": "West" }
]

Note that CSV carries strings unless you add a separate typing step. Your API should coerce qty to a number if needed.

Headers and consistency

• The first row must be column names; rename duplicates before conversion.
• Watch for BOM characters at the start of UTF-8 exports from Excel—they can break a naive parser and turn your first header into \uFEFFsku.
• Quoted fields can contain commas and newlines; good parsers handle them; regex split-by-comma does not.

When JSON → CSV

Reporting tools and email attachments often want CSV again. Export one row per object; use the union of all keys as columns if objects differ (sparse rows get empty cells).

Validate before production

After conversion, run your JSON through a JSON Formatter to catch trailing commas, single quotes, or accidental NaN values from spreadsheet formulas.

If your pipeline also accepts YAML from config repos, YAML to JSON can help align everything to the same JSON schema tests.

Try it locally

Our CSV ↔ JSON Converter runs in the browser: paste a sample export, get formatted JSON, or go the other way for a quick report. No files leave your device.

Treat CSV as untrusted input if users upload it—limit size, scan for formula injection in Excel (=cmd|), and never execute cells as code.

Summary

• Prefer header + rows → array of objects for APIs.
• Preserve quoting rules; avoid hand-rolled CSV parsers for production.
• Always validate JSON and schema before sending to downstream services.