Open Data Manual › Practical guides › CSV vs JSON: Choosing a Format for Open Data

CSV vs JSON: Choosing a Format for Open Data

By Open Data Manual editors · Published 4 November 2026 · Editorial standard

Almost every open data publisher ends up asking the same question: should a dataset go out as a CSV file, as JSON, or both? The two formats are both open, text-based and supported by every programming language, so the choice is less about right and wrong than about the shape of the data and who will use it. This guide sets out how each format works, where each one causes trouble, and a practical rule for deciding. It builds on the short format descriptions in the manual's File Formats appendix.

The short answer

Publish CSV when the data is a single flat table (rows of records, the same columns in every row) and a large share of your users will open it in a spreadsheet. Publish JSON when records are nested or vary in structure, or when the data is served through an API to developers. If you run a data portal or an API, offering both for the same dataset is common and costs little once the export is automated.

What CSV is, and what it is not

CSV (comma-separated values) stores a table as plain text: one record per line, fields separated by commas. The closest thing to a specification is RFC 4180, published in October 2005. It is an Informational RFC: it documents the format most software already used and registers the text/csv media type, rather than laying down a binding standard. Its main rules are simple:

• each record sits on its own line, ending with a line break;
• an optional first line can hold column names;
• every line should have the same number of fields;
• a field that contains a comma, a double quote or a line break is wrapped in double quotes, and a double quote inside a field is written twice ("").

What CSV does not carry is just as important. There are no data types, so a reader cannot tell whether 01234 is a number or a code with a leading zero, or whether 03/04/2026 is 3 April or 4 March. There is no standard way to say which character encoding or separator is used, and many tools in continental Europe write semicolons instead of commas. All of that context has to be supplied separately.

What JSON is

JSON (JavaScript Object Notation) represents data as objects (named fields), arrays (ordered lists), strings, numbers, booleans and null. It is defined by RFC 8259 (December 2017) and the matching ECMA-404 standard. RFC 8259 requires JSON exchanged between systems outside a closed ecosystem to be encoded in UTF-8, which removes one of CSV's most common problems.

Because JSON can nest, one record can hold a list of sub-records: a school with several addresses, a contract with many line items, a bus route with an ordered list of stops. In CSV, the same data needs either several linked tables or awkward repeated columns. JSON also distinguishes numbers from strings, so "01234" stays a string.

For very large datasets, publishers often use JSON Lines, where each line is one complete JSON object. That keeps JSON's structure while letting users stream the file record by record, much as they would read a CSV.

CSV and JSON side by side

Question	CSV	JSON
Best data shape	One flat table	Nested or irregular records
Opens in a spreadsheet	Yes, directly	Not without conversion
Data types	None (everything is text)	Strings, numbers, booleans, null
Encoding	Not specified in the file	UTF-8 required for exchange (RFC 8259)
File size for the same table	Smaller (column names written once)	Larger (field names repeated per record)
Typical use	Bulk downloads, statistics, registers	APIs, web applications, complex records
Describing the columns	Separate metadata file (e.g. CSVW)	Separate schema (e.g. JSON Schema)

Where CSV goes wrong

Most CSV problems appear when the file is opened rather than when it is written. Spreadsheet software guesses types: long identifiers are turned into scientific notation, leading zeros vanish from postcodes and phone codes, and strings that look like dates are converted. Microsoft Excel also stops at 1,048,576 rows per worksheet, so a large file can be silently cut short for anyone who opens it that way.

A few habits prevent most of the damage:

• save as UTF-8 and say so on the download page;
• use a comma separator and quote fields consistently;
• write dates in ISO 8601 form (2026-11-04), which is unambiguous;
• keep one header row, no merged cells, no notes or totals under the table;
• publish a short description of each column, including units.

W3C's CSV on the Web work, published as Recommendations in December 2015, standardises that last point. A metadata file named after the data (for countries.csv, the file countries.csv-metadata.json) can declare column names, data types, units, the primary key and the licence, and can guide conversion of the CSV to JSON or RDF.

Where JSON goes wrong

JSON's flexibility is also its weakness. Two JSON files with the same content can be structured very differently, so users need documentation or a schema before they can work with the data reliably. Non-programmers cannot easily open it, and very deep nesting makes analysis harder for everyone. Numbers can also be a trap: RFC 8259 notes that software may not handle very large or very precise numbers identically, so long identifiers are safer published as strings.

If you publish JSON, document the structure, keep it stable between releases, and announce changes in advance. A JSON Schema file lets users validate what they download.

Where the formats sit on the five-star scheme

Tim Berners-Lee's 5-star open data scheme, often used by portals to rate releases, gives one star to data on the web under an open licence in any format, two stars to structured machine-readable data (such as an Excel file), and three stars to structured data in a non-proprietary format. Both CSV and JSON reach three stars. Four and five stars require using URIs to identify things and linking to other data, which points towards RDF and formats such as JSON-LD; see the RDF entry in the manual's File Formats appendix.

A practical decision rule

1. Look at the shape of the data. If it fits in one table without repeating columns, start with CSV. If it doesn't, start with JSON or split it into several linked CSV tables.
2. Look at the audience. Journalists, analysts and the general public mostly use spreadsheets; developers building services prefer JSON from an API.
3. Automate both if you can. For flat data, generating JSON from CSV (or the reverse) is mechanical. W3C publishes a Recommendation for generating JSON from tabular data.
4. Always offer a bulk file. As the manual's chapter on making data available says, an API is useful but does not replace bulk access to the complete dataset.
5. Describe it. Whatever the format, add metadata: title, description, licence, update frequency and column definitions. Our guide to metadata standards covers how.

A note on geographic data

Location data has its own conventions. GeoJSON, defined in RFC 7946 (2016), is a JSON format for points, lines and polygons and is widely supported by web mapping tools. A CSV with latitude and longitude columns works well for simple point data, but shapes such as boundaries need a geographic format.

Frequently asked questions

Is CSV or JSON better for open data?

Neither is better in general. CSV suits flat, rectangular tables that people will open in a spreadsheet; JSON suits nested records and data served from an API. Many publishers offer a CSV download and a JSON API for the same dataset.

Is CSV an official standard?

CSV is described in RFC 4180 (2005), which is an Informational RFC rather than a formal standard. It documents common practice and registers the text/csv media type. W3C's CSV on the Web Recommendations (2015) add a way to describe CSV files with metadata.

What character encoding should open data files use?

UTF-8. RFC 8259 requires JSON exchanged between systems to be UTF-8, and declaring UTF-8 for CSV avoids broken accented characters when files are opened in different tools.

Can I convert CSV to JSON automatically?

Yes, for flat tables the conversion is mechanical, and W3C's CSV to JSON Recommendation defines a standard way to do it. Going from nested JSON to CSV usually means flattening or splitting the data into several tables.

More practical guides

• Metadata standards (DCAT)
• FAIR data principles
• The CC0 license
• CC BY 4.0 for data
• Open Database License (ODbL)
• Database rights