Need to convert now? Use CSV to JSON or JSON to CSV.
The short answer
Use CSV for flat tables that people will open in a spreadsheet. Use JSON for data that has structure, that a program will read, or that goes through an API.
Side by side
| CSV | JSON | |
|---|---|---|
| Structure | Flat rows and columns | Nested objects and arrays |
| Types | None, everything is text | String, number, boolean, null, object, array |
| Size | Smaller, because the column names appear once | Larger, because keys repeat in every object |
| Readability for people | Excellent in a spreadsheet | Good in an editor, with indentation |
| Readability for programs | Needs a parser that handles quotes | Built into nearly every language |
| Standard | Loose. RFC 4180 describes the common form | Strict, with an exact grammar |
| Streaming | Natural, one row at a time | Whole document, or JSON Lines for streaming |
| Typical use | Exports, reports, imports, data science | APIs, configuration, web apps |
When to choose CSV
- The data is one flat table, such as a list of customers or orders.
- Someone will open it in Excel or Google Sheets.
- The file is large, and size and speed matter.
- You import into a database or analysis tool that reads CSV.
When to choose JSON
- The records have nested parts, such as an order with a list of items.
- Records do not all have the same fields.
- You need real types: numbers, booleans and null.
- A web page or an API will use the data.
What goes wrong in a conversion
- Types. CSV has none, so a converter has to guess. A zip code like 01234 must stay text, and a number with more than 15 digits does not fit in a JSON number.
- Quotes and commas. A field with a comma, a quote or a line break must be wrapped in double quotes, and a quote inside it is doubled. Splitting a line on commas is not enough.
- Delimiters. Many European programs use a semicolon, and tab-separated files are common. The delimiter has to be detected or chosen.
- Nesting. A nested object has to be flattened to fit in CSV, for example address.city, and it cannot always be restored exactly.
- Missing values. An empty CSV cell can mean an empty string, null or a missing field. Decide which one you want.
- Encoding. UTF-8 is the safe choice. Excel often needs a byte order mark to show accents correctly.
- Formulas. A text value that starts with =, +, - or @ may be run as a formula when a spreadsheet opens it. Protect exports that include text from users.
Convert in code
# Python: CSV to JSON (values stay text)
import csv, json
with open('data.csv', newline='', encoding='utf-8-sig') as f:
rows = list(csv.DictReader(f))
with open('data.json', 'w', encoding='utf-8') as f:
json.dump(rows, f, indent=2, ensure_ascii=False)
// PHP: CSV to JSON
$f = fopen('data.csv', 'r');
$head = fgetcsv($f, 0, ',', '"', '');
$data = [];
while (($r = fgetcsv($f, 0, ',', '"', '')) !== false) {
$data[] = array_combine($head, $r);
}
echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
// JavaScript with the Papa Parse library
const { data } = Papa.parse(csvText, { header: true, dynamicTyping: true, skipEmptyLines: true });
In Python, csv.DictReader keeps every value as text. Convert the columns that should be numbers yourself, or use pandas: pd.read_csv('data.csv').to_json('data.json', orient='records', indent=2).
Convert on the command line
- csvkit:
csvjson data.csv > data.json - Miller:
mlr --icsv --ojson cat data.csv > data.json
Both handle large files and quoted fields.
A middle way: JSON Lines
JSON Lines keeps one JSON object on each line. It has the types and nesting of JSON, and it can be streamed and appended to like CSV. The CSV to JSON array page can produce it.
Tools on this site
- CSV to JSON and CSV to JSON online for files.
- JSON to CSV for exports to spreadsheets.
- JSON diff to compare two JSON files, and JSON vs XML for another comparison.