Every data import I’ve built has had a CSV bug. Customer IDs that lost their leading zeros. A German price list that parsed as one column. A “name” column that was actually name. None of these threw an error — the data was just quietly wrong.
This guide covers those traps and how the CSV ⇄ JSON converter handles them. It runs in the browser, which matters when the file is a customer list.
Leading zeros
Here’s a typical export:
customer_id,name,pincode,phone,balance
00417,Asha Rao,411001,+919812345678,1520.50
00932,Luis Ortega,080012,+34911234567,-45.00A naive converter “helpfully” turns anything numeric into a number: 00417 becomes 417, the Indian PIN code 080012 becomes 80012, and the phone number loses its +. None of those are numbers — they’re identifiers that happen to be made of digits.
By default the converter keeps every value as a string. If you turn on “Detect numbers & booleans”, it converts a column only when every value in it survives the round trip:
[
{
"customer_id": "00417",
"name": "Asha Rao",
"pincode": "411001",
"phone": "+919812345678",
"balance": 1520.5
},
{
"customer_id": "00932",
"name": "Luis Ortega",
"pincode": "080012",
"phone": "+34911234567",
"balance": -45
}
]pincode stays text even though 411001 alone would be a fine number, because 080012 in the same column wouldn’t — and a column that’s sometimes a number and sometimes a string is worse than either. balance becomes numbers: 1520.50 and 1520.5 are the same value. Integers longer than JavaScript’s safe range (2^53) are kept as strings too, so 16-digit IDs aren’t rounded.
Semicolons and European Excel
In locales where the comma is the decimal separator — Germany, France, Spain, much of Europe — Excel saves “CSV” with semicolons:
Name;Price;Qty
Kaffee;3,50;2Split on commas and you get garbage. The converter detects the delimiter (comma, semicolon, tab or pipe) and shows which one it used. Note that 3,50 stays the string "3,50"; converting locale-formatted decimals is a decision for your import code, not something to guess.
The BOM
Excel’s “CSV UTF-8” format starts the file with a byte order mark. Many parsers keep it, so the first column is named customer_id and row.customer_id is undefined. The converter strips it and tells you it was there.
The reverse matters too: when exporting CSV for Excel, you want the BOM, or Excel reads the file as Windows-1252 and München turns into München. The “For Excel” option adds the BOM and uses CRLF line endings.
Quotes, commas and newlines inside fields
RFC 4180 is small but people rarely implement all of it. A field containing the delimiter, a quote or a line break must be quoted, and a quote inside a quoted field is doubled:
sku,name,notes
KB-104,"Mechanical Keyboard, TKL",
MS-220,Wireless Mouse,"Says ""silent"" on the box"The converter uses Papa Parse, which handles all of this, including line breaks inside quotes. An unterminated quote — usually a stray " in someone’s data — is reported with its line number, because it silently swallows every following row otherwise.
Ragged rows and duplicate headers
When a row has more or fewer fields than the header, the converter keeps everything (extra fields become _extra1, _extra2) and lists each row with its line number. That list usually points straight at an unquoted comma in an address.
Duplicate headers are common in exports from reporting tools. Most converters build objects with obj[header] = value, so the second name overwrites the first. This one renames it to name_2 and warns you.
JSON back to CSV, and CSV injection
Going the other way, the converter takes the union of all keys (missing values become empty cells), flattens nested objects into address.city columns, and writes arrays either as JSON text or as indexed columns.
There’s one security issue to know about. A cell that begins with =, +, - or @ is treated as a formula by Excel and Google Sheets. If user-supplied data ends up in an export — a name like =HYPERLINK("http://evil.example","Click") — opening the file can run it. OWASP’s recommendation is to prefix such cells with an apostrophe. The converter counts them and, with “Escape formulas” on, does exactly that:
name,note
Asha,'=1+2
Luis,-5Plain negative numbers like -5 are left alone.
Why Excel shows 1.23E+15
Last one, and it isn’t the converter’s fault. Excel stores numbers as doubles with 15 significant digits, so when it opens a CSV with a 16-digit ID it shows 1.23457E+15 — and if you save, the digits are gone. The CSV was fine. Import with Data → From Text/CSV and set the column type to Text instead of double-clicking the file.
Once the data is in JSON, the JSON formatter is handy for checking it, and JSON to SQL turns it into INSERT statements when the destination is a database.