CSV vs JSON: When to Use Each and What to Watch When Converting

Table versus tree

CSV (Comma-Separated Values) is a table. The first line holds column names and each following line is a row, with values separated by commas. It is the simplest, lightest way to store flat data in which every row has the same columns.

JSON is a tree. Objects can contain arrays that contain more objects, so hierarchical data such as 'one order with many items' is expressed naturally. The cost is size, because key names repeat on every record.

name,city,score
Kim,Seoul,92
Lee,Busan,85

[
  {"name": "Kim", "city": "Seoul", "score": 92},
  {"name": "Lee", "city": "Busan", "score": 85}
]

Which one to use

The lack of types in CSV matters. Save the postal code 01234 in a CSV and Excel may read it as a number and drop the leading zero; long card or order numbers may turn into scientific notation such as 1.23E+15.

• Data people will open or edit in Excel or Google Sheets: CSV.
• Hundreds of thousands of rows of simple logs or statistics: CSV (small and easy to stream line by line).
• Web API requests and responses: JSON.
• Hierarchical data with lists inside records: JSON.
• When numbers, booleans and null must be distinguished: JSON (in CSV everything is text).

Commas and quotes: CSV escaping rules

What if a value contains a comma? RFC 4180 says to wrap it in double quotes: "Seoul, Korea". A double quote inside a value is escaped by doubling it, so He said "hi" becomes "He said ""hi""". Line breaks inside values are allowed when quoted.

Code that simply calls split(',') ignores these rules, and columns shift as soon as an address or notes column appears. Always process CSV with a parser or converter that follows the standard.

Why Excel garbles non-English text

Double-clicking a UTF-8 CSV in Windows Excel often shows garbled Korean, Japanese or accented characters. Without a UTF-8 byte order mark (BOM), Excel guesses and falls back to the system's legacy encoding.

Either save the file with a UTF-8 BOM, or use Data → From Text/CSV in Excel and choose UTF-8 explicitly. Conversely, when a program reads a file with a BOM, check that an invisible character has not been prefixed to the first column name.

Flattening nested JSON into CSV

Turning a tree into a table requires a convention. A common one joins the path with dots, so {"user": {"name": "Kim"}} becomes a user.name column.

Arrays are harder. If an order has three items, you can either create three rows or store the items as a JSON string in one column. Extra rows suit analysis; a JSON string column suits preserving the original.

Checklist before converting

Convert with the AIBIZAP CSV ↔ JSON converter, then inspect the structure in the JSON formatter to catch mistakes early.

• Confirm the first line is a header and the delimiter really is a comma (semicolons and tabs are common).
• Keep code values with meaningful leading zeros as strings.
• Trim spaces around cells and remove blank lines first; the whitespace remover helps.
• Check that the row count after conversion matches the original. A mismatch usually means a quoting or line-break problem.