CSV to JSON / Excel Format 100% local

CSV to JSON from Excel: Power Query, the BOM trap, and quoting

Excel will happily save your sheet as CSV and call it done. The file that lands on disk is, ten times out of ten, not the file your downstream JSON tool expects. Knowing what Excel mangles is what stops the mystery import errors.

Save as CSV UTF-8, not just CSV

Excel offers three CSV options in the Save As dialog: CSV (Comma delimited), CSV UTF-8, and a regional variant. The default CSV (Comma delimited) is Windows-1252 on most installs, which destroys any non-ASCII character (accented vowels, emoji, smart quotes) the moment it leaves the file.

Always pick CSV UTF-8 (Comma delimited). The output is RFC 4180 compliant (with one caveat below) and it survives the trip through any modern JSON parser.

The catch: Excel's UTF-8 export prefixes the file with a UTF-8 BOM (EF BB BF). Strict JSON parsers and many CLI tools break on a BOM in the first column header. Strip it before parsing, or use a tool that handles it (Power Query does).

Quoting and the comma-inside-a-cell problem

RFC 4180 says a field that contains a comma, a newline, or a quote must be wrapped in double quotes, and any literal quote inside must be doubled (""). Excel follows this strictly when saving as UTF-8 CSV, which is why your nice clean CSV suddenly has lines like:

id,name,address
1,Ada Lovelace,"London, UK"
2,Margaret Hamilton,"Cambridge, ""MA"""

A naive split on , will produce four columns instead of three. Use a CSV-aware parser, not split. In Python the stdlib is csv; in Node the convention is csv-parse or papaparse; in jq see the next page.

Power Query is the right tool inside Excel

If you do not want to leave Excel, Power Query (Data tab, Get & Transform) reads the sheet, exposes a graphical pipeline, and exports JSON via a single line of M:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Json = Json.FromValue(Source)
in
    Json

The advantage over Save-as-CSV-then-convert is that Power Query handles BOM, mixed encodings, and column-type inference in one step. The result is a JSON record array, ready to drop into a fetch call or a fixture.

For occasional one-offs (one sheet, one conversion, no recurrence), Save as CSV UTF-8 and a browser tool is faster. For repeatable pipelines (a monthly export from finance), Power Query stays in the workbook and runs on refresh.

Date columns are the most common bug

Excel stores dates as serial numbers (days since 1900-01-01 with a quirk for the non-existent leap day). On CSV export, those numbers are rendered using whatever locale Excel decided to use, which is not ISO 8601 by default.

If your downstream JSON consumer expects "2025-11-19", set the column format to yyyy-mm-dd in Excel before saving, or convert in the JSON tool. Otherwise you get strings like 19/11/2025 (es-ES), 11/19/2025 (en-US), or 19-Nov-2025 (en-GB), and your TypeScript types lie about it.

A safer convention: store dates in Excel as text (with a leading apostrophe) in the exact ISO form, and let downstream tools parse them as strings. You lose Excel's date math; you gain a CSV that means the same thing in every locale.

Working example

powershell

# Strip the UTF-8 BOM that Excel adds, then convert to JSON
$path = "data.csv"
$raw  = Get-Content $path -Raw -Encoding UTF8
if ($raw.StartsWith([char]0xFEFF)) { $raw = $raw.Substring(1) }

# PowerShell 7+ ConvertFrom-Csv is RFC 4180 aware
$rows = $raw | ConvertFrom-Csv
$rows | ConvertTo-Json -Depth 5 |
    Out-File -FilePath "data.json" -Encoding utf8

Just need the result?

When you just want to drop the CSV your finance team emailed you into a JSON file your API can ingest, opening Power Query is overkill. Paste the CSV into the browser-based CSV to JSON converter, watch the BOM strip and the quoting normalise, copy the JSON. One step, runs locally, no upload.

Open CSV ↔ JSON Converter →

Frequently asked questions

Why do my JSON consumers see a weird character at the start of the file?

That is the UTF-8 BOM Excel adds on CSV UTF-8 export. Most JSON parsers reject it. Strip the first three bytes (EF BB BF) before parsing, or use Power Query, which handles it transparently.

Can I round-trip Excel to JSON to Excel without losing data?

Mostly. Numbers and strings survive cleanly. Dates lose Excel's serial format and become ISO strings. Formulas always become their evaluated value. If you need formulas back, save as XLSX and parse the binary, not the CSV.

Does Excel save CSV with CRLF or LF line endings?

Windows Excel writes CRLF, Mac Excel writes CR (older versions) or LF. RFC 4180 prefers CRLF. Most modern tools handle both, but legacy importers occasionally choke; if yours does, normalise to LF or CRLF in a preprocessing step.