CSV vs JSON: choose a format without losing meaning

Side by side

Look at the shape before the file extension

An equipment cupboard provides a useful example. A simple list of tools has one item per row and columns for item ID, name, and quantity. That naturally fits CSV. A borrowing record may include a borrower, several items, collection details, and a list of reminders. JSON can keep those related pieces inside a single nested record.

Neither format is a database, a spreadsheet workbook, or a guarantee of correctness. Both are text-based ways to represent data. CSV does not preserve multiple workbook sheets, cell colors, or formulas as working spreadsheet features. JSON does not automatically check whether an item ID exists or whether a quantity is sensible. Those are application and schema responsibilities.

Worked example: a flat equipment list

The following invented CSV contains a header and two records. There are three fields per record. The first item’s name contains a comma, so the name is enclosed in double quotes. Under the common convention documented in RFC 4180, a double quote inside a quoted field is represented by two double quotes.

The identifier 0042 deserves special attention. It is a label, not a quantity to add or average. A spreadsheet importer may interpret its digits as a number and display 42. Quoting a CSV field does not universally force every receiving application to preserve a text type. Configure that column as text during import, and check the result.

The practical check is to compare the imported ID to the source file in a plain-text viewer. If the source says 0042 but the spreadsheet says 42, repair the import settings before saving over anything. Once the leading zeros have been discarded and the altered data exported, the original identifier may be impossible to reconstruct without another source.

item_id,name,available
0042,"Clamp, medium",3
0043,Measuring tape,2

Worked example: one loan with several items

Here is an invented loan record expressed as JSON. The loan identifier is a string. Each entry in the items array describes one borrowed item. The boolean returned is distinct from the string "false", and the quantity is a number rather than text. These distinctions help a receiving program, provided it checks the expected structure.

The value null is used here to mean “no return date recorded”. That meaning is a decision for this example; JSON itself does not assign a business meaning to null. An absent property, an empty string, and null are different representations. The exporting and importing applications must agree on which is appropriate.

JSON strings use double quotes, and ordinary JSON does not allow comments or trailing commas. RFC 8259 recommends unique member names within an object because duplicate names can be handled inconsistently. A parser accepting the file is a first check. It still does not prove that item 0042 belongs to the correct catalogue.

{
  "loan_id": "L-108",
  "items": [
    { "item_id": "0042", "quantity": 2 },
    { "item_id": "0043", "quantity": 1 }
  ],
  "returned": false,
  "return_date": null
}

Converting the loan into CSV takes a design decision

To export that loan as CSV, one reasonable layout is one row per borrowed item. Both rows carry the same loan ID. You could use columns loan_id, item_id, quantity, returned, and return_date. This is useful for counting borrowed tools, but it repeats loan-level values. If one row later says returned and another does not, your importer needs a defined rule rather than a guess.

Another option is two CSV files: one for loans and another for loan items, connected by loan_id. That avoids repeating some information, but the receiving system must keep the files together. A third option places a whole JSON array into one CSV field. This can be valid text, yet it makes ordinary spreadsheet filtering more difficult and requires another parsing step.

The correct choice follows the question being answered. For a monthly count by item, one row per loan item is convenient. For restoring complete loan records into another application, the nested JSON may retain relationships more directly. A conversion tool cannot choose this meaning solely from the extensions .csv and .json.

Write down a small data agreement

Before an exchange, specify the columns or property names, required fields, text encoding, date representation, and missing-value convention. For CSV, also agree on the delimiter and quoting rules. Files called CSV sometimes use regional separator conventions, so a filename alone is insufficient.

For the cupboard example, state that item_id remains text, quantity is a nonnegative whole number, and return_date is either a documented calendar-date string or the agreed missing value. If date-and-time values are needed later, define the time zone too. Neither CSV nor JSON has a built-in calendar-date type that settles this for you.

Use a handful of deliberately awkward sample records before a large export: a name containing a comma, a name containing quotation marks, a non-English character, an ID beginning with zero, and a missing date. Microsoft’s import documentation explains why selecting column types and previewing separators is safer than assuming automatic interpretation will match your intent.

Check the result, not just that the file opens

After importing the sample, count records and compare several key values character for character. For the loan example, confirm that there are two item entries, that 0042 still has four characters, and that the total borrowed quantity is three. Export the imported sample again and inspect whether the intended meaning survives the round trip.

A successful small round trip does not prove every future record will work. Add validation for the documented constraints, and keep the untouched source until the migration is verified. Format choice makes an exchange easier; explicit meaning and checks make it dependable.

Common questions