JSON, CSV, and Excel: A Complete Guide to Data Format Conversions

CSV (Comma-Separated Values) is the simplest format: plain text, one row per line, fields separated by commas (or semicolons in European locales). CSV is universally compatible — every database, spreadsheet, and analytics tool can import and export it. Its weakness is that it has no inherent data types: numbers, dates, and text are all stored as strings, and the application reading the file decides how to interpret them. CSV also doesn't support hierarchical data — all data must be flattened into rows and columns.

JSON (JavaScript Object Notation) is a structured, human-readable text format that supports: string, number, boolean, array, object, and null data types. JSON can represent hierarchical and nested data naturally. It's the dominant format for web APIs and modern application data exchange. JSON is less suited for tabular data analysis than Excel or CSV, but its type system and nesting capabilities make it more expressive for complex data structures.

Excel (XLSX) is a binary (or XML-based in XLSX format) spreadsheet format with rich features: formulas, cell formatting, pivot tables, charts, multiple sheets, and more. It preserves numeric types and date formatting explicitly. Excel is excellent for business reporting, analysis, and data that needs to be reviewed and manipulated by non-technical users. It's less suitable as a data exchange format for automated systems due to its complexity.

Use CSV when: you need maximum compatibility across systems; you're doing a bulk import into a database or CRM; the data is flat (no nesting, no hierarchy); file size matters; or the data will be processed programmatically by a script or ETL pipeline.

Use JSON when: you're working with API data; the data has a nested or hierarchical structure (e.g., customer with nested address with nested city); you need explicit data types (distinguishing numbers from strings); or you're sending data to a modern web application.

Use Excel when: the recipient is a business user who will work with the data in a spreadsheet application; the data needs formatting, charts, or pivot tables; multiple related data sets need to be in the same file on separate sheets; or you need formulas and calculated fields.

The cleanest JSON-to-Excel conversions come from flat JSON arrays — arrays where every object has the same keys and all values are primitive types (strings, numbers, booleans). If your JSON has this structure, the conversion is straightforward and produces a clean, one-sheet Excel file.

For nested JSON, you need to decide how to handle the nesting before converting. Common strategies: flatten nested objects (address.city becomes a column called 'address_city'); separate related data into multiple sheets linked by ID; or convert nested arrays to semi-colon-separated strings in a single cell (least clean but sometimes acceptable).

After converting JSON to Excel, always verify: that numeric values are stored as numbers (not text) — check by right-clicking a cell and selecting 'Format Cells'; that dates are stored as Excel dates (if the JSON had ISO date strings); and that all rows from the JSON are present in the Excel output.

The most frequent data corruption issue in Excel-to-CSV conversion is character encoding. Excel on Windows defaults to saving CSV in Windows-1252 encoding. This works fine for English text but corrupts special characters (accented letters like é, ñ, or à; Indian script characters; currency symbols like ₹) when the CSV is opened by a system expecting UTF-8.

The safe practice is always to specify UTF-8 encoding when saving CSV from Excel: File → Save As → CSV UTF-8 (Comma-delimited). This option is available in Excel 2016 and later on Windows. In older versions, use the 'CSV (Macintosh)' option, which also produces UTF-8 output on Mac.

The second common issue is date formatting. Excel stores dates as serial numbers (number of days since January 1, 1900) and displays them using the cell's format setting. When exported to CSV, dates are exported as their display string, not as a standardised date format. This can produce CSV files where dates are in the display format (e.g., '01-Dec-2024') rather than an ISO standard format ('2024-12-01'). Standardise date formats in Excel to ISO 8601 (YYYY-MM-DD) before exporting to CSV for maximum compatibility.

The most important decision in CSV-to-JSON conversion is how to handle data types. A naive converter treats every CSV value as a string — '42' becomes '"42"' in JSON rather than 42. This is semantically wrong if the field is actually a number, and it breaks downstream JSON consumers that check types.

Good CSV-to-JSON converters offer type inference: they detect that a column contains only numbers and converts the values to JSON numbers, or detects ISO date strings and converts them to date-formatted strings. Review the type inference results after conversion, particularly for columns that contain mostly numbers but occasionally empty cells (nulls) or text values.

The second common issue is delimiter detection. Not all CSVs use commas — European CSVs often use semicolons, and data from legacy systems may use tabs or pipes. If your JSON output looks wrong (all data in a single field, or unexpected field splitting), check the delimiter setting of the conversion tool.