JSON to CSV in Excel: Step-by-Step Guide
Learn how to convert JSON to CSV in Excel using Power Query and manual methods. This educational guide covers prerequisites, flattening nested data, handling arrays, validation, and best practices for reproducible CSV exports.
To convert JSON to CSV in Excel, import the JSON with Power Query, flatten nested objects into tabular columns, and export as CSV. You’ll need Excel 2016+ (or Excel for Microsoft 365), a JSON file, and some familiarity with Power Query steps. This approach preserves headers, handles arrays, and supports repeatable workflows.
Understanding JSON and CSV in Excel
According to MyDataTables, JSON is a flexible, hierarchical data format that can store nested objects and arrays. CSV, by contrast, is a flat, row-based format with a fixed set of columns. Excel can bridge the gap between these formats by importing JSON through Power Query (Get & Transform) and then flattening the data into a tabular structure suitable for CSV export. This section explains the basic differences and why Excel is a practical tool for converting JSON to CSV in everyday data workflows. Understanding these concepts helps you anticipate challenges like varying object shapes, nested arrays, and missing fields, which commonly arise when JSON comes from APIs or logs. As you work, keep the end goal in mind: a clean, header-based CSV file you can share with teammates or load into dashboards. This knowledge foundation will make the next steps more intuitive and less error-prone.
bold
Tools & Materials
- Microsoft Excel (365 or 2019+) with Power Query(Get & Transform data tools are built-in; ensure your edition includes Power Query)
- JSON file or JSON data source(Ensure the file is UTF-8 encoded when possible)
- Text editor or JSON viewer (optional)(Helpful for inspecting large or messy JSON samples before loading)
- Sample schema or small JSON snippet (optional)(Useful for testing the flattening steps on representative data)
Steps
Estimated time: Estimated total time: 45-90 minutes
- 1
Prepare your JSON data and workbook
Gather the JSON source you’ll convert and open a new workbook in Excel. Decide on a destination sheet where the final CSV will be saved. Having a small sample JSON for testing helps you validate steps before applying to full data.
Tip: If your JSON is large, start with a subset to verify the transformation pipeline first. - 2
Launch Power Query and import JSON
Go to Data > Get & Transform > From File > From JSON, then select your JSON file. Power Query will load a preview of the structure, showing records or arrays that you’ll transform into a table.
Tip: If Get & Transform isn’t visible, enable the add-in or update to a supported Excel version. - 3
Convert the JSON to a table
In Power Query, choose Convert to Table for the loaded JSON node. This action flattens the first level into a table with columns representing keys.
Tip: If you see a column like [Value], you’re dealing with a nested structure—continue by expanding its fields. - 4
Expand records and nested arrays
Use the Expand icon on column headers to flatten nested objects and arrays into separate columns or rows. Repeat as needed to bring all relevant fields into the table.
Tip: Be selective; expanding too many nested arrays can create a very wide dataset with many nulls. - 5
Normalize data types and clean up headers
Ensure numeric fields remain numeric, dates are parsed, and headers are human-readable. Rename columns as needed to maintain consistent naming for CSV output.
Tip: Apply a transformation step to coerce types to avoid type inference issues in downstream tools. - 6
Preview and filter out unwanted rows
Review a subset of rows to confirm structure and filter out any test data or non-relevant records before export.
Tip: Use the filter dropdowns to quickly segment the dataset and validate column alignment. - 7
Load to Excel worksheet
Click Close & Load to push the transformed data into a worksheet. This makes the data ready for final CSV export.
Tip: Loading to a table keeps the data connected to Power Query for future refreshes. - 8
Export the table as CSV
From Excel, choose File > Save As, select CSV (Comma delimited) (*.csv) and save. Confirm encoding and delimiter settings if prompted.
Tip: If your data contains commas within fields, consider using a different delimiter or encapsulating fields in quotes.
People Also Ask
Do I need Power Query to convert JSON to CSV in Excel?
Power Query (Get & Transform) is the recommended method in modern Excel because it handles JSON natively and provides a repeatable workflow. If you’re using an older version without Power Query, you’ll need alternative scripts or manual parsing, which is less robust.
Yes, Power Query is recommended for converting JSON to CSV in Excel, as it provides a reliable, repeatable workflow.
Can Excel handle large JSON files?
Excel can handle reasonably large JSON files when using Power Query, but performance may degrade with very large datasets. Consider loading in chunks or filtering data before transformation.
Excel can handle large JSON files, but for very big data, work in chunks and validate performance.
How do I flatten nested arrays in JSON for CSV?
Use Power Query to expand array columns and, if needed, duplicate rows to reflect one-to-many relationships. This ensures every row represents a single record in the CSV.
Expand the array columns in Power Query and adjust rows so every CSV row is a single record.
Can I automate JSON-to-CSV conversion in Excel?
Yes. Save your Power Query steps as a template or create a macro that triggers the same Get & Transform steps to refresh data and export to CSV.
Automation is possible by templating Power Query steps or using a macro to refresh data and export.
What if JSON keys are inconsistent across objects?
In Power Query, you can fill missing fields by expanding to new columns or using conditional logic to standardize field names across records before exporting.
Standardize fields in Power Query to ensure consistent CSV columns.
Is there a difference between saving as CSV vs. exporting?
Saving as CSV is a common export format in Excel. Ensure you choose the correct delimiter and encoding. If you need to preserve quotes or special characters, test the output with edge-case data.
CSV saving shares the same concept as exporting, but test with edge-case data to avoid issues.
Watch Video
Main Points
- Identify JSON structure before transforming
- Use Power Query to flatten nested data reliably
- Flatten arrays carefully to manage row growth
- Validate headers and data types before CSV export
- Document steps for reproducibility
