Why CSV to JSON Keeps Failing (Quotes, Excel, and Encoding)

The happy path - header row, delimiter, paste, copy the array - is How to convert CSV to JSON. This page is the failure modes. When to keep the table as CSV at all is JSON vs XML vs YAML vs CSV.

CSV to JSON on DevOkk.com runs in the browser. No account. The conversion does not need to upload the export. That matters because these files are often customer lists. After you have an array, JSON Formatter is how you read it; JSON Viewer is how you walk a wide object. Formatter and viewer also stay in the tab. Analytics still load; that is ordinary site traffic, not your rows going to a conversion API.

Do not treat this as encryption. A local converter keeps the bytes off a random upload box. It does not hide them from the next person with the laptop.

RFC 4180 is a suggestion Excel never signed

The CSV people cite in arguments is RFC 4180: comma separated, fields optionally wrapped in double quotes, quotes inside a field doubled (""), records ending in CRLF, an optional header line. That document is useful. It is not what Excel Save As writes, and it is not what every “Download CSV” button emits.

Excel will:

• Use ; as the separator when Windows is in a European locale.
• Write a sep=; hint as the first line so Excel can reopen the file.
• Emit a UTF-8 BOM so Notepad does not mojibake the header.
• Keep the cell format you saw on screen: 03/04/2026, €1.234,56, 1.23E+12 for a barcode.
• Leave an empty column because someone resized a sheet with a stray value in column Z.

A converter that implements only the RFC and assumes comma plus UTF-8 without BOM will fail on a file a human opened and re-saved. The file extension is still .csv. The dialect is not.

If the first line is sep=;, that line is not a header. Delete it or skip it. If you keep it, your first object has a key named sep=; and one field.

Semicolons, tabs, and the one-column JSON

The most common “it failed” report is that every row became a single property. The file uses ; or a tab. You split on ,. There are no commas, so each line is one field. The JSON looks like:

You get objects with one key that contains the entire row as a string, or a single column whose name is the whole header line.

European accounting exports do this every week. German, French, and Dutch Excel users are not being difficult. Their list separator is a semicolon because the comma is the decimal mark.

TSV is the warehouse version of the same bug. Tabs look like spaces in some editors. The file is valid. The delimiter picker is wrong.

Fix: set the delimiter to match the file, not to match the letters “CSV.” CSV to JSON is the place to try comma, semicolon, and tab on the same paste. If two of those produce garbage and one produces sku, qty, price, you are done with that mystery.

Pipes (|) show up in older dumps. If none of the usual three work, look at the raw line in a hex-aware editor or simply count the separators on line two.

The UTF-8 BOM that poisons the first key

A byte-order mark at the start of a UTF-8 file is EF BB BF. Many Windows tools add it so the file is “obviously Unicode.” Many parsers include those bytes in the first header.

You wanted a key sku. You got \uFEFFsku or a key that looks identical in a printout and fails obj.sku in JavaScript. Every row “converted.” Your script says the field is missing.

This is why people swear the converter dropped a column. It renamed the first one.

Re-export without the BOM if your stack allows it, or strip the first character if it is the BOM, or map the key in code. Confirm by printing Object.keys(rows[0]) and looking at the first key’s length. If sku has length 4 in your head and 5 in the runtime, you found it.

UTF-16 CSV from “Unicode Text” in Excel is a different file. A tool that expects UTF-8 will show NUL bytes or Asian mojibake. That is not a quote problem. Re-save as UTF-8 CSV.

Windows-1252 exports with smart quotes and é will also parse as headers you cannot type. If names look fine in Excel and rotten in the JSON, the encoding of the text you pasted is not the encoding of the file on disk. Paste from a UTF-8 view, not from a terminal that lied.

Quoted commas and the field that split in half

"Acme, West desk" is one cell in a real CSV. A naive line.split(',') makes two cells: "Acme and West desk". The JSON then has a shifted row: name is "Acme, city is West desk", and every later column is off by one.

If only some rows are wrong, look for commas inside names, addresses, and notes. Those rows are the ones that were quoted. If quoting was lost in a previous Save As - Excel sometimes strips quotes when you copy a selection - the file is already broken before the converter runs. Re-export the sheet. Do not hand-edit fifty commas.

Doubled quotes are the other half of the rule. A note that says He said "ship it" should appear in the CSV as "He said ""ship it""". If someone wrote "He said "ship it"" you have a parser fight. The converter is not psychic.

Multiline quoted fields

A RFC-valid cell can contain a newline if the field is quoted. Address blocks and “notes” columns do this. A converter that splits on \n first will think the row ended in the middle of the quote. Row counts will not match. JSON will show a half object and then a line that is not a header.

If the output has fewer objects than data rows, or a value that starts in the middle of a sentence, this is the usual cause. Use a parser that understands quotes, not a split. CSV to JSON is built for that job; a one-line script in a hurry is not.

If you do not need the notes column, delete it in the spreadsheet before export. Conversion copies every cell. It does not know that column is a poem.

Empty columns, trailing commas, and ghost keys

A-104,2,19.00, has four fields. The last is empty. The header might be sku,qty,price with three names. Now every object has an extra key named "" or "field4" or the parser errors.

The opposite: a header with a trailing comma and rows without one. Same mismatch.

Blank lines at the end become empty objects. A title row above the header becomes a one-key document. A filter view in Google Sheets that you forgot to remove can drop rows you still think are there.

Empty cells in the middle (A-104,,19.00) become "", null, or a missing key depending on the tool. APIs care. A PATCH that sends qty: "" is not the same as omitting qty. Look at one object in JSON Formatter before you POST the array.

Duplicate headers (email, email) collide. One column wins. Rename in the sheet first if you need both work and personal.

Numbers that should stay strings

CSV cannot say integer. Everything is text. Convenience converters coerce 2 to 2 and true to true. That is nice until:

• SKUs like 000104 become 104.
• Phone numbers lose the leading zero.
• Barcodes become scientific notation (1.23e+12) because Excel already destroyed them in the grid.
• NO and YES become booleans in some stacks (more common in YAML, but people then paste the result around).
• Money 19.00 becomes 19 and you lose the scale you wanted to show.

If the JSON must match an API that wants strings, keep strings. Coerce in one place in your script, with tests, not in a UI checkbox you forgot you clicked.

Excel scientific notation on identifiers is not a JSON bug. The damage happened in the spreadsheet. Import those columns as text before anyone “helps” you.

Dates: 03/04/2026 is not a type

In the United States that string is March 4. In much of Europe it is 3 April. Excel stores a serial number and displays a format. CSV writes the display, or a different format after you open the file on another machine.

JSON will store whatever string you give it. It will not “fix” the locale. Two engineers on two laptops will parse the same file into two instants.

ISO 2026-04-03 (and 2026-04-03T12:00:00Z if you need a time) survives conversion. If you cannot change the export, parse with an explicit MM/DD or DD/MM in code. Do not let Date.parse guess.

Times without a zone (09:00) are the same class of lie. The warehouse meant local. The API will assume UTC. That is not the converter’s fault.

One-row header mismatch

You paste a file whose first line is a report title, second line is blank, third line is the real headers. The converter uses line one. You get one key. You blame JSON.

Or you paste without headers because “the columns are obvious.” You get arrays of arrays, or keys 0, 1, 2. Scripts that want row.sku fail. Add a header row. It is the schema.

Or you have a one-row file: only headers, no data. The JSON is []. That is success. It is also how people think the tool “ate” the row. The header is names, not a record.

Or you have one data row and a header, but you included the header in a copy that started on the data. Now the first object’s values are sku, qty, price and the keys are A-104, 2, 19.00. Swap.

Password manager dumps and CRM exports

CSV is how browsers and password managers export logins. It is how CRMs dump emails and phone numbers. It is how a store exports orders.

Do not upload those files to a random “convert to JSON” site to see the shape. Do not paste a production dump into a screenshot on Slack. Redact. Take three fake rows. If you must convert the real table, do it in the browser on a machine you control and delete the tab after.

CSV to JSON is built to keep that paste local. That is the safer default, not a promise that the file is harmless. What not to paste into online developer tools is the longer list. DevOkk does not encrypt the CSV. Base64 is not a way to hide the emails first. Hashing the file is not a conversion step.

If a column is password or token, delete the column in the sheet. Conversion will copy it otherwise. Then someone formats the JSON and the secret is still there, prettier.

After it converts: it still might not be valid JSON you want

Sometimes the converter succeeds and the next tool fails. You copied a single object without noticing the array. You copied with a trailing comma because you edited by hand. You wrapped the paste in markdown fences. How to fix invalid JSON is that incident. How to format and validate JSON is the pretty-print pass.

JSON Formatter after CSV to JSON is the habit: did types look right, did the first key look like sku and not a BOM, is the document one array. JSON Viewer when each object has forty keys and you only care about two paths.

Formatter will not tell you the date locale is wrong. You still have to read a row.

A worked example: the Berlin orders export

A shop in Berlin downloads orders. Excel emits:

First line sep=;. Second line a BOM plus Bestellnr;Datum;Betrag;Notiz. Amounts look like 1.234,56. Dates look like 03.04.2026. One note field has a comma and a line break inside quotes.

A comma-based converter produces one column. You switch to semicolon. Now you have four keys, but the first is \uFEFFBestellnr. Your Node script cannot find Bestellnr. You strip the BOM. Amounts are strings with a dot as thousands. parseFloat on 1.234,56 is 1.234. You replace dots and then commas, or you keep the string and parse with a German locale. Dates are D.M.YYYY. You do not run them through new Date(string).

You delete Notiz before convert because it has customer prose. You paste into CSV to JSON. You format the array. You see three clean objects. That is the job. The clicks for a clean file are in the how-to. This was the dialect.

A second worked example: the SKU Excel already ate

A catalog export has sku values that look like 1234567890123 in the database and 1.23457E+12 in the CSV. JSON conversion is faithful. The API rejects the scientific string. No quote setting will restore the digits Excel dropped.

The fix is in the spreadsheet: format the column as text, re-export from the source system as CSV without opening it in Excel, or export XLSX and read cells as text in a library. Conversion cannot invent lost digits.

If you only needed to see the shape, three redacted rows are enough. Do not keep the full catalog in a browser tab longer than you need.

Trailing junk that is not CSV

People paste a table copied from a ticket, including the ticket UI. They paste an email with a signature. They paste HTML from a rich editor that “looks like columns.” They paste JSON that is already an array and ask a CSV tool to fix it. They paste a log line.

If the paste starts with { or [, you are in the JSON tools, not the CSV tool. If it starts with <table, you have HTML. If it starts with PK you have a zipped XLSX and you renamed the extension.

What “success” looks like before you ship the array

One header line of real names. Delimiter matches the file. No BOM on the first key. Row count matches data rows, not including the header. One object in the formatter has the keys you will read in code. Numbers you care about still have leading zeros if they need them. Dates are ISO or parsed with a locale you chose. Sensitive columns are gone.

If that list is green, the converter worked. If you still have a parse error, you may have left CSV and entered broken JSON by hand - that is the invalid-JSON article, not this one.

The spreadsheet that was never CSV

A lot of “CSV to JSON keeps failing” tickets are not CSV. They are an .xlsx whose icon changed after a rename. They are an Apple Numbers package. They are a Google Sheet downloaded as “Excel” and then copied from the grid, which copies tabs some days and commas other days. They are a PDF of a table, or a screenshot, or an HTML report with <td> cells.

Excel’s native file is a zip of XML. Numbers has its own package. Google’s “CSV” download is the closest to a real table, and even that obeys the locale of the account that clicked Download. If you did not get a text file you can open in Notepad and see commas or semicolons as characters, you do not have CSV yet. Convert the workbook to CSV in the spreadsheet program, look at the text, then paste that text into CSV to JSON.

The converter will not unzip a workbook for you, and it should not. Uploading a customer workbook to a mystery site to “just get JSON” is the failure mode this page exists to avoid. Get a real CSV, fix the dialect, keep the paste in the browser, then format the array. If the file was never CSV, no quote setting will save the afternoon.