Convert CSV to JSON

How rows become objects

The first line is the header. Every line after it becomes one object, with the header's names as keys and the row's values as values. A header of name,year followed by the rows Ada,1815 and Grace,1906 becomes [{"name": "Ada", "year": "1815"}, {"name": "Grace", "year": "1906"}]. The edge cases:

• Short rows get an empty string for each missing value, so every object has every key.
• Long rows lose the values beyond the last header column, since there is no name to give them.
• Blank lines, and lines made only of separators, are skipped.
• Repeated header names: when two columns share a name, the later column's value wins, because a JSON object cannot hold the same key twice.
• Header text is used as it is, spaces and capitals included. Rename the columns in the header line first if your code expects keys like first_name.

Why every value is a string

CSV has no types. 1815, 00501, TRUE and 2024-10-08 are all just text in a CSV file, and guessing what each was meant to be is where conversions go wrong: ZIP codes and phone numbers lose their leading zeros, 1E5 turns into 100000, a product code like 12-3 turns into a date. Octoolo does not guess. Every value comes out as a JSON string, exactly as written, and empty cells come out as "".

That gives you faithful data to start from. Turn the fields that really are numbers or booleans into those types in the code that reads the JSON, where you know what each column means: Number(row.year) in JavaScript, int(row["year"]) in Python. If you need grouping or nesting, such as orders under each customer, that is a job for the same code: the converter makes one flat object per row.

Quotes, commas and line breaks inside values

The parser follows the usual CSV rules, those of RFC 4180 and of the CSV files Excel writes:

• A value in double quotes may contain the separator: "Smith, John" stays one value.
• Inside quotes, two double quotes stand for one: "He said ""hi""" reads as He said "hi".
• Line breaks inside quotes belong to the value, so a multi-line address or comment stays in one cell instead of breaking the row.
• Windows (CR LF) and Unix (LF) line endings both work.

One strict point: a quote only opens a quoted value when it is the first character of the cell. In a, "b" the space before the quote makes the quotes part of the value. Files from Excel, Google Sheets and databases never do this, but hand-edited CSV sometimes does. Also make sure Separator matches the data: a semicolon file read as Comma gives objects with one long key and one long value each.

Where CSV comes from, and PowerShell's version

Most CSV that ends up as JSON starts in a spreadsheet or an export: a product list for a shop, users to seed a test database, a sheet of translations, a bank statement for a budgeting script. If yours is an Excel workbook, open it in Octoolo's Spreadsheets and save a CSV copy of the sheet (see XLSX to CSV). Or, in Excel or Google Sheets, select the cells, copy them and paste them here with Tab as the separator.

PowerShell can do it too: Import-Csv data.csv | ConvertTo-Json writes the same kind of array, also with every value as a string. Add -Delimiter ';' for semicolon files. Note that Windows PowerShell 5.1 writes a single object rather than an array when the file has only one row. When you want to see the result as you go, without pasting the data into a website, the converter here shows it live.