CSV and JSON: How to Convert Between the Two Most Common Data Formats

CSV and JSON are the two most common data exchange formats. CSV is the lingua franca of spreadsheets and databases. JSON is the standard for APIs and web applications. Converting between them is a daily task for developers, analysts, and data scientists.

When to Use Each Format

Common Conversion Pitfalls

• Commas inside values — Must be quoted: "Smith, John",35
• Newlines in fields — Must be quoted; many parsers break on these
• Different delimiters — European CSVs use semicolons; TSVs use tabs
• Nested JSON — Flattening nested objects into CSV columns requires decisions about naming
• Encoding — Always use UTF-8; Excel sometimes defaults to locale-specific encodings

Worked Example: Converting a Sales Export to JSON

Say your CRM exports a CSV of deals that a REST API must consume. One row reads "Acme, Inc.",EMEA,12000 — the company name contains a comma, so it must stay quoted. A correct converter produces:

The steps a reliable conversion follows:

1. Split rows on commas that are not inside quotes — this is where naive split(",") code breaks and produces ragged objects
2. Strip surrounding quotes and unescape doubled quotes ("" becomes a single ")
3. Cast numeric columns: 12000 becomes the number 12000, not the string "12000"
4. Emit one JSON object per row, using the header row as keys

Going the other way, JSON to CSV, is easy for flat objects but needs decisions for nested ones — flatten {"address": {"city": "Oslo"}} into a column named address.city, for example, and apply the same rule to every row.

Common Mistakes

• Splitting on commas blindly — text.split(',') destroys any field containing a comma, silently shifting every value after it one column to the left. Use a parser that understands quoted fields.
• Losing leading zeros — zip codes like 02134 become the number 2134 in Excel and in careless JSON casts. Keep them as strings end to end.
• Ignoring BOM and encoding — Excel exports often start with a UTF-8 BOM or use Windows-1252, turning é into é downstream. Always normalize to clean UTF-8 before converting.
• Assuming every row has the same columns — real exports have missing and extra fields. Decide up front whether missing keys become null, an empty string, or are omitted.
• Letting Excel reformat on open — opening a CSV in Excel can turn 1-2 into the date January 2. Inspect exports in a text editor before trusting what the spreadsheet shows.

Frequently Asked Questions

Which format should my API return?

JSON for anything consumed by code — it is self-describing and nests naturally. Offer CSV only when the consumer is explicitly a spreadsheet, such as an admin "Export" button built for finance teams.

How do I handle nested JSON when converting to CSV?

Flatten each nested object into dot-notation column names like address.city, or serialize the nested part as a JSON string inside a single cell. Choose one convention and document it, because there is no standard.

What delimiter should I use?

Commas for anything shared internationally; semicolons where European Excel is the primary consumer; tabs (TSV) when fields may contain both commas and quotes. A good converter detects the delimiter automatically.

Is there a size limit for conversion?

Practical limits come from memory, not the formats. Millions of rows of CSV stream fine, but the JSON equivalent can be several times larger because every object repeats its keys. Convert very large files in chunks when you can.

The Bottom Line

1. Use CSV for flat tabular data and spreadsheet exchange
2. Use JSON for APIs, configuration, and nested structures
3. Always handle quoted fields and special characters when converting
4. Use UTF-8 encoding to avoid character corruption

Disclaimer: This guide is for informational purposes only.