Introduction
JSON is hierarchical; CSV is flat. The most common flattening strategy uses dot-notation for keys: "address.city" becomes a column header. Arrays are harder — a "phoneNumbers" array with two entries can produce either two rows (repeating parent data) or a single row with a JSON-stringified cell. Our converter offers both modes: "expand" creates one row per array element, "compact" stores arrays as JSON strings in a single cell.
Flattening Strategies for Nested JSON
JSON is hierarchical; CSV is flat. The most common flattening strategy uses dot-notation for keys: "address.city" becomes a column header. Arrays are harder — a "phoneNumbers" array with two entries can produce either two rows (repeating parent data) or a single row with a JSON-stringified cell. Our converter offers both modes: "expand" creates one row per array element, "compact" stores arrays as JSON strings in a single cell.
CSV Encoding Pitfalls
CSV has no universal standard. Values containing commas must be quoted with double quotes. Values containing double quotes must escape them as "". Multi-line values must be quoted. Our converter follows RFC 4180: all cells are properly quoted and escaped. For Excel compatibility (especially on non-English systems), use semicolons as delimiters — our tool supports switching between comma, semicolon, and tab delimiters.
Large Dataset Handling
For datasets over 10MB, browser-based CSV conversion may hit memory limits. For production ETL, use a streaming approach with Node.js streams or jq on the command line. Our converter is optimized for moderate datasets (up to 10MB) commonly encountered in data analysis and spreadsheet imports.
Frequently Asked Questions
What is the best way to flatten deeply nested JSON objects for CSV?
The most reliable approach uses dot-notation key concatenation. For example, {"address": {"city": "New York", "zip": "10001"}} becomes columns address.city and address.zip. Some converters use bracket notation (address[city]) or separator characters like underscores. Dot-notation is the most widely recognized and works best with spreadsheet software column headers.
How do I handle JSON arrays during CSV conversion?
Arrays are the trickiest part of JSON-to-CSV conversion. You have two options: the expand mode creates a separate row for each array element, duplicating the parent object's other values, which is useful when each array item is a distinct record. The compact mode stores the entire array as a JSON string in a single cell, preserving the original structure but making the data harder to query in spreadsheet software.
Why does my CSV file look corrupted when I open it in Excel?
Excel is notoriously picky about CSV formatting. Common issues include: using commas as delimiters on systems where Excel expects semicolons (common in European locales), missing BOM (Byte Order Mark) for UTF-8 encoded files with special characters, and unquoted values containing commas or line breaks. Our converter follows RFC 4180 and offers a semicolon delimiter option specifically for Excel compatibility.
What is RFC 4180 and why does it matter for CSV?
RFC 4180 is the formal specification for CSV format. It defines rules for quoting fields containing commas, line breaks, or double quotes, handling CRLF line endings, and properly escaping double quotes by doubling them. Following RFC 4180 ensures your CSV files are parseable by any compliant CSV parser, regardless of the programming language or platform being used.
Can I convert JSON with Unicode or special characters to CSV?
Yes, but you must ensure your CSV is saved with UTF-8 encoding and include a BOM for Excel compatibility on Windows. Characters like emojis, accented letters, and non-Latin scripts (Cyrillic, Chinese, Arabic) are fully supported in UTF-8 CSV files. Without the BOM, Excel may interpret the file as ANSI encoding and display special characters incorrectly.
What happens to null values when converting JSON to CSV?
Null values in JSON are typically converted to empty cells in CSV. Some converters offer the option to use a placeholder string like "N/A" or "null" instead. This is important because empty cells and null values may be treated differently by downstream data processing tools. Our converter preserves nulls as empty cells, which is the most universally compatible behavior.
How do I convert large JSON files without running out of memory?
For files over 10MB, browser-based converters may struggle because the entire file must be loaded into memory. For production workloads, use a streaming JSON parser (like JSONStream in Node.js or jq with the --stream flag) to process records one at a time without loading the full dataset. Command-line tools like jq, mlr (Miller), and Python's pandas library handle large files efficiently.
What delimiter should I use for my CSV file?
Commas are the standard delimiter and work in most tools. Use semicolons when working with European Excel versions, which expect semicolons due to comma being used as the decimal separator. Tabs (TSV format) are ideal when your data contains commas but not tabs. Our converter supports all three, and we recommend semicolons for guaranteed Excel compatibility.
How do I preserve data types when converting JSON to CSV?
CSV is a plain-text format with no type system — all values are strings. To preserve type information, you can either add a header row with type annotations (like "name:string, age:number") or export a companion schema file. JSON data types like numbers, booleans, and nulls all become string representations in CSV. Downstream tools like Excel may auto-detect types based on cell content.
Why does my CSV import show incorrect date or number formatting?
Excel and Google Sheets auto-detect types when opening CSV files, which can cause problems. Dates like "05/04/2026" may be interpreted as May 4th or April 5th depending on locale. Long numeric IDs may lose precision due to scientific notation. To prevent auto-formatting, prefix values with an apostrophe, use text-qualified imports, or prepend a zero-width space to force text interpretation.
What is the difference between JSONL (JSON Lines) and CSV?
JSONL stores one JSON object per line, making it line-delimited and streamable like CSV while preserving the full JSON structure. Unlike CSV, JSONL handles nested objects and arrays natively without flattening. JSONL is preferred for log files and streaming data pipelines, while CSV is better for spreadsheet analysis and business reporting.
Conclusion
Converting JSON to CSV is a common task in data migration, analysis, and reporting workflows, but the simplicity of CSV masks several technical challenges. Nested objects must be intelligently flattened, arrays require careful decisions about expansion versus compaction, and encoding issues can corrupt data when files cross platform boundaries. By understanding RFC 4180 compliance, choosing appropriate delimiters for your target spreadsheet application, handling null values consistently, and respecting the memory limits of browser-based conversion, you can build reliable data pipelines that produce clean, importable CSV files. For large-scale production workloads, complement browser tools with streaming command-line utilities. Whether you are migrating data between systems, preparing reports for stakeholders, or building ETL pipelines, getting JSON-to-CSV conversion right saves hours of manual cleanup and prevents costly data integrity issues downstream.
Frequently asked questions
What is the best way to flatten deeply nested JSON objects for CSV?
The most reliable approach uses dot-notation key concatenation. For example, {"address": {"city": "New York", "zip": "10001"}} becomes columns address.city and address.zip. Some converters use bracket notation (address[city]) or separator characters like underscores. Dot-notation is the most widely recognized and works best with spreadsheet software column headers.
How do I handle JSON arrays during CSV conversion?
Arrays are the trickiest part of JSON-to-CSV conversion. You have two options: the expand mode creates a separate row for each array element, duplicating the parent object's other values, which is useful when each array item is a distinct record. The compact mode stores the entire array as a JSON string in a single cell, preserving the original structure but making the data harder to query in spreadsheet software.
Why does my CSV file look corrupted when I open it in Excel?
Excel is notoriously picky about CSV formatting. Common issues include: using commas as delimiters on systems where Excel expects semicolons (common in European locales), missing BOM (Byte Order Mark) for UTF-8 encoded files with special characters, and unquoted values containing commas or line breaks. Our converter follows RFC 4180 and offers a semicolon delimiter option specifically for Excel compatibility.
What is RFC 4180 and why does it matter for CSV?
RFC 4180 is the formal specification for CSV format. It defines rules for quoting fields containing commas, line breaks, or double quotes, handling CRLF line endings, and properly escaping double quotes by doubling them. Following RFC 4180 ensures your CSV files are parseable by any compliant CSV parser, regardless of the programming language or platform being used.
Can I convert JSON with Unicode or special characters to CSV?
Yes, but you must ensure your CSV is saved with UTF-8 encoding and include a BOM for Excel compatibility on Windows. Characters like emojis, accented letters, and non-Latin scripts (Cyrillic, Chinese, Arabic) are fully supported in UTF-8 CSV files. Without the BOM, Excel may interpret the file as ANSI encoding and display special characters incorrectly.
What happens to null values when converting JSON to CSV?
Null values in JSON are typically converted to empty cells in CSV. Some converters offer the option to use a placeholder string like "N/A" or "null" instead. This is important because empty cells and null values may be treated differently by downstream data processing tools. Our converter preserves nulls as empty cells, which is the most universally compatible behavior.
How do I convert large JSON files without running out of memory?
For files over 10MB, browser-based converters may struggle because the entire file must be loaded into memory. For production workloads, use a streaming JSON parser (like JSONStream in Node.js or jq with the --stream flag) to process records one at a time without loading the full dataset. Command-line tools like jq, mlr (Miller), and Python's pandas library handle large files efficiently.
What delimiter should I use for my CSV file?
Commas are the standard delimiter and work in most tools. Use semicolons when working with European Excel versions, which expect semicolons due to comma being used as the decimal separator. Tabs (TSV format) are ideal when your data contains commas but not tabs. Our converter supports all three, and we recommend semicolons for guaranteed Excel compatibility.
How do I preserve data types when converting JSON to CSV?
CSV is a plain-text format with no type system — all values are strings. To preserve type information, you can either add a header row with type annotations (like "name:string, age:number") or export a companion schema file. JSON data types like numbers, booleans, and nulls all become string representations in CSV. Downstream tools like Excel may auto-detect types based on cell content.
Why does my CSV import show incorrect date or number formatting?
Excel and Google Sheets auto-detect types when opening CSV files, which can cause problems. Dates like "05/04/2026" may be interpreted as May 4th or April 5th depending on locale. Long numeric IDs may lose precision due to scientific notation. To prevent auto-formatting, prefix values with an apostrophe, use text-qualified imports, or prepend a zero-width space to force text interpretation.
What is the difference between JSONL (JSON Lines) and CSV?
JSONL stores one JSON object per line, making it line-delimited and streamable like CSV while preserving the full JSON structure. Unlike CSV, JSONL handles nested objects and arrays natively without flattening. JSONL is preferred for log files and streaming data pipelines, while CSV is better for spreadsheet analysis and business reporting.