CSV, XLSX and JSON: moving data between spreadsheets and code

Most data that moves between people travels in one of three shapes: a CSV file, an Excel workbook, or a JSON payload. They all hold tables of values, so converting between them sounds trivial. In practice, each format has quirks that quietly damage your data if you don't know they exist. This guide covers what each format actually is, what survives a conversion and what doesn't, and the handful of traps that account for almost every "my data got corrupted" complaint we hear.

What a CSV file actually is

A CSV is just plain text. Open one in Notepad and you'll see the whole thing: one line per row, values separated by commas, first line usually holding the column names. There is no styling, no formulas, no types — everything is a string until whatever opens the file decides otherwise.

The format looks so simple that people regularly write their own parser with a split-on-comma one-liner, and that works right up until a value contains a comma. The address "Seoul, Korea" is one value, but a naive parser sees two. The rules that handle this are small but strict: a value containing a comma, a double quote or a line break must be wrapped in double quotes, and any double quote inside a quoted value is doubled up. So a cell holding He said "hi", then left is written as "He said ""hi"", then left". Quoted values can even contain real line breaks, which means a single logical row can span multiple lines in the file — another thing split-on-newline parsers get wrong.

One more wrinkle: "comma-separated" is a polite fiction in parts of Europe, where the decimal separator is a comma and Excel writes CSV files separated by semicolons instead. If a file loads as one giant column, the delimiter is usually the reason.

The Excel encoding trap

Here's the failure everyone in Korea and Japan runs into eventually. You export a CSV from a script or a web app, double-click it, and Excel shows garbage where the Korean or Japanese text should be — strings like ë°ê¸¸ë instead of names. The file isn't broken. It's UTF-8, which is the correct, standard encoding. The problem is that when Excel opens a CSV by double-click, it doesn't assume UTF-8. It falls back to the system's legacy code page (CP949 on a Korean Windows machine, Shift-JIS on a Japanese one) unless the file starts with a UTF-8 byte order mark — three specific bytes, EF BB BF, sitting before the first character.

So the fix is oddly specific: if a CSV is destined for Excel and contains non-ASCII text, give it a BOM. Most programming languages let you write the encoding as utf-8-sig to do exactly this. If you receive a garbled file, the data is still intact — reopen it via Excel's Data → From Text/CSV import and pick UTF-8 manually, or convert it to a real Excel file with CSV to XLSX, since XLSX declares its encoding internally and never has this problem.

What an XLSX file really is

An XLSX file is a ZIP archive. Rename one to .zip, extract it, and you'll find a folder of XML files: one per sheet, one for shared strings, one for styles, one for the workbook structure. Every formula, every cell color, every merged range is XML markup inside that archive. That's why an Excel file holding the same table as a CSV is several times larger, and why you need a real library — not a text editor — to read one.

This structure tells you exactly what survives conversion to CSV and what doesn't. Converting XLSX to CSV keeps the computed values of one sheet: the numbers and text you see in the cells. It drops formulas (you get the last calculated result, not =SUM(A1:A9)), all formatting, merged cells, charts, comments, and every sheet beyond the one being exported. That's not a flaw in any particular converter; CSV simply has nowhere to put those things. If the formulas or the second sheet matter, keep the original XLSX around and treat the CSV as a snapshot.

JSON: the shape code wants

JSON stores structured data as nested objects and arrays, and the shape that corresponds to a table is an array of objects — one object per row, column names as keys:

[{"name":"Kim","city":"Busan","age":31}, {"name":"Sato","city":"Osaka","age":45}]

This is the shape APIs return and scripts consume, because every mainstream language parses it into native lists and dictionaries in one call. Unlike CSV, JSON distinguishes numbers from strings from booleans from null, and it's always UTF-8, so the encoding mess above simply doesn't apply. Its weakness is the reverse of CSV's: JSON can nest arbitrarily deep, and a nested structure has no obvious flat-table form. An object with an orders array inside each customer doesn't map cleanly onto rows and columns, so converters either flatten it with dotted column names or stringify the nested part. Flat JSON — arrays of simple objects — round-trips to CSV losslessly apart from type information.

Two workflows that cover most cases

Spreadsheet to script. You have data in Excel and need it in Python or JavaScript. Export the sheet, or convert XLSX to CSV, then run CSV to JSON — the header row becomes the keys, every following row becomes an object. If you want to skip the intermediate file, XLSX to JSON goes straight there. Either way you end up with an array your code can loop over immediately.

API response to spreadsheet. The reverse trip: you've pulled JSON from an API and someone needs it in Excel. JSON to CSV turns any array of flat objects into rows, using the union of keys as columns. Open the result in Excel directly, or convert CSV to XLSX first if the data contains non-ASCII text and you want to sidestep the BOM issue entirely. Everything here runs in your browser, so the data never leaves your machine — worth knowing when the file is a customer list.

The type pitfalls that actually bite

Since CSV has no types, whatever opens the file guesses — and Excel guesses aggressively. Three cases cause most of the damage.

Leading zeros. A phone number like 01012345678 or a product code like 00742 looks numeric to Excel, so it becomes 1012345678 and 742. Save the file and the zeros are gone for good. If a column is an identifier rather than a quantity, format it as Text before importing, or import through the CSV wizard where you can set column types.

Dates. Excel converts anything date-shaped, sometimes wrongly: the gene name SEPT2 famously became "2-Sep" in so many published papers that the genetics community renamed the gene. A value like 1/2 silently becomes January 2nd. Once converted and saved, the original text is unrecoverable.

Big numbers. Excel keeps about 15 significant digits, so a 16-digit card number or a long numeric ID gets its tail rounded to zeros: 4532015112830366 turns into 4532015112830360. JSON parsers in JavaScript share a similar limit for the same floating-point reason. Long IDs should live as strings, everywhere.

The common thread: the conversion itself is rarely what corrupts data. It's the application that opens the result and "helpfully" reinterprets it. Convert with a tool that leaves values alone, control the import step, and CSV, XLSX and JSON move data around reliably. For shorter, tool-specific advice, see our conversion tips page.