DEV Community

Sanjaya Sahu
Sanjaya Sahu

Posted on

CSV to JSON: 7 Traps That Corrupt Your Data (and How to Avoid Them)

CSV looks simple until it isn't. Here are the failure modes I see most when converting CSV to JSON (or loading it into a database), and how to avoid them.

1. Commas, quotes and newlines inside fields

A naive line.split(',') breaks on "Smith, John". RFC 4180 says fields containing commas, quotes or line breaks must be wrapped in double quotes, and embedded quotes are doubled (""). Always use a real parser.

2. Everything is a string

CSV has no types. 007 is a zip code, not the number 7; 1E5 may be scientific notation or a product code. Decide per column whether to keep strings, and never auto-convert IDs, phone numbers or zip codes.

3. Empty string vs null

a,,c and a,"",c look different but most parsers treat both as empty strings. Pick a rule (empty means null, or keep empty) and apply it consistently so your JSON consumers do not guess.

4. Duplicate and messy headers

Duplicate column names silently overwrite each other when you build objects. Trim whitespace, normalize case, and de-duplicate (name, name_2) before converting.

5. Encoding and BOM

Excel exports often start with a UTF-8 BOM, which turns your first header into \ufeffid. Strip it, and confirm the file is UTF-8, not Windows-1252.

6. Delimiters are not always commas

European locales commonly use semicolons because commas are decimal separators. Detect the delimiter or let the user choose.

7. Huge files

Reading a 500 MB file into memory to convert it will crash tabs and servers. Stream rows, or process in chunks.

A quick, private way to check

When I just need to eyeball a conversion, I use a client-side tool so the data never leaves my machine: CSV to JSON, CSV Viewer and CSV to SQL. Everything runs in the browser.

Disclosure: I built Web Developer Toolbox, which hosts these tools.

Top comments (0)