Working with data files: JSON, CSV and spreadsheets

JSON vs CSV, and when to use each

JSON describes nested, structured data. Most APIs return it. CSV is a flat grid of rows and columns that spreadsheets open directly.

Use JSON to move data between programs. Use CSV when a person needs to sort, filter or read it.

JSON keeps value types such as numbers, true and false, null, arrays and objects. CSV has no types. A spreadsheet guesses whether each cell is text, a date or a number. Those guesses can change IDs, drop leading zeros and round long numbers.

Before you convert, decide which copy is the source of truth. Keep the original export. Write down the columns and types you expect. Treat the spreadsheet copy as a working view until you have tested the round trip.

Flattening nested data

Spreadsheets can't show nesting. So converting JSON to CSV means flattening the data. A one-level flatten turns each object into a row and each key into a column.

Deeply nested values usually need to be summarized or split into several columns before they make sense in a grid.

Arrays need a clear rule. Joining the values into one cell is easy to read but hard to query. Repeating the parent row for each value can inflate counts. Moving child records to a second sheet keeps the structure, but you need a stable ID to link the two sheets.

Look at several records before you pick a shape. Optional fields, mixed types and objects that only appear late in the file often break an export that looked fine from the first row.

Escaping and encoding problems

Commas, quotes and line breaks inside a field will break a careless CSV. The fix is to wrap those fields in double quotes and double any quotes inside them.

Save the file as UTF-8 so accented letters and emoji come through intact in your spreadsheet.

The separator can change with the region and the app. Where a comma is the decimal mark, some spreadsheets expect semicolons. Some older importers need a byte order mark to detect UTF-8. When the data matters, use the import dialog instead of double-clicking the file.

Spreadsheet apps may treat cells that start with =, +, - or @ as formulas. If a CSV holds input from untrusted users, neutralize those cells before you open the file. Read the security advice for the app you import into.

Dates, IDs and empty values

Dates are safest as a standard format such as 2026-03-04, with a time zone when the time matters. A value like 03/04/26 means different days in different countries. A timestamp without a zone can shift when another system assumes local time.

Store postal codes, account numbers and other IDs as text so leading zeros survive. Keep an empty string, a missing value and zero apart. Treating them as the same thing can change the meaning of a later import.

Check before you import

Before you load the converted file into another system, compare row counts, required columns and a few totals. Look at a sample of unusual records too. A valid CSV can still hold the wrong rows or flatten an array the wrong way.

Run a small test import first and leave the original data untouched. If something is off, you can change the separator, encoding, date format or flattening rule and try again. That is much easier than fixing a half-imported dataset.