Skip to content

CSV vs Excel vs JSON: the differences and when to use each

What each format can hold, where each one breaks, and a simple rule for picking the right one.

By Shahzad Ali ·

The short answer

Use CSV for one table of plain data that many programs must read, Excel (.xlsx) for workbooks people open and edit with formulas and formatting, and JSON for structured or nested data that programs read and write.

CSV: one plain table

A CSV file is plain text: one line per row, with values separated by commas, or by semicolons in many countries. It has no types, sheets, formulas or formatting, so every program can read it and files stay small. The cost is ambiguity: neither the separator nor the text encoding is stored in the file, which is why CSV files sometimes open in one column or show broken Arabic in Excel.

Excel (.xlsx): a workbook

An .xlsx file is a zip archive of XML files in the Office Open XML format. It holds several sheets, typed cells (numbers, dates, text), formulas, number formats, styles, charts and more. A sheet holds at most 1,048,576 rows and 16,384 columns. It is the right format when people will open the file, but it is harder for scripts to write and read than CSV.

JSON: structured data

JSON is text too, but it stores objects and lists that can nest: an order can contain a customer and a list of items. Values are typed as strings, numbers, true or false, and null; there is no date type, so dates are written as text. Almost every programming language reads it, which is why APIs use it, but a spreadsheet cannot show the nesting without flattening it.

Side by side

The differences that matter in practice:

  • Structure: CSV is one table; Excel has several sheets; JSON has nested objects and lists.
  • Types: CSV has none, everything is text; Excel has numbers, dates, text and true/false; JSON has strings, numbers, true/false and null.
  • Formulas and formatting: only Excel.
  • Size: the CSV and JSON formats have no limit; Excel stops at about a million rows per sheet.
  • Who reads it: CSV, almost everything; Excel, spreadsheet apps and libraries; JSON, programs, APIs and dedicated viewers.
  • Text encoding: not recorded in a CSV file; Excel and JSON handle Unicode by design, and JSON exchanged between systems must be UTF-8.

Which one to pick

A rule of thumb for the common cases:

  • Sending a table to someone who will open it in Excel: .xlsx, so nothing about separators or encoding can go wrong.
  • Importing into a database or another program: CSV in UTF-8.
  • Data for an app, an API or a configuration file: JSON.
  • Data with more than about a million rows: CSV or JSON, because Excel cannot hold it in one sheet.

Converting between them

Each conversion runs in your browser, uploads nothing, and opens the result in its own viewer: Excel to CSV, CSV to Excel, Excel to JSON, CSV to JSON and JSON to CSV. Turning JSON into a table flattens nested fields into columns such as address.city, and converting a CSV makes its header row the JSON keys.

Questions?

Write to support@openanydoc.com if a conversion does not give you what you expected (no personal data).