Import JSON to Excel: Healthcare MRF and Price Transparency Data

If you need to convert JSON to Excel, the most common built-in method is Power Query: Data > Get Data > From File > From JSON. From there, you transform the result into a table, expand nested fields, and load the output into a worksheet. For large, deeply nested healthcare machine-readable files (MRFs), flatten and filter the data before exporting a focused spreadsheet extract.

Key takeaways

• Payer transparency files are distributed across 180 payer websites.
• The AccessMRF catalog lists 3 million Table of Contents files.
• The catalog reports 2.5 PB of compressed negotiated-rate data in 1.7 million MRF files.

Can you import JSON to Excel?

Yes. Excel can import JSON using Power Query: Data > Get Data > From File > From JSON, then To Table, Expand, and Close & Load. This is the most reliable built-in way to convert JSON into rows and columns.

• Works best when the JSON is relatively flat and the resulting table fits in Excel.
• Expect extra work when you see nested Record or List values that must be expanded.
• Plan B: Use Python or a large-file workflow when files are too large to load comfortably.

What is JSON?

JSON, or JavaScript Object Notation, is a lightweight format for structured data exchange. It is common in APIs, software exports, logs, and machine-readable datasets.

• Object: Key-value pairs in { }.
• Array: A list of items in [ ].
• Why Excel struggles: Nested objects and arrays do not map cleanly to a single flat table.

What does JSON structure look like?

{
 "name": "John Doe",
 "department": "Finance",
 "rates": [150, 175, 200]
}

To analyze JSON in Excel, you typically flatten it so each object becomes a row and each key becomes a column.

Why convert JSON to Excel?

JSON is great for systems, but it can be slow to review by eye. Converting JSON to Excel turns structured data into a table you can filter, pivot, QA, and share with non-technical stakeholders.

The tradeoff is that JSON often needs flattening. Nested arrays, repeated objects, and very large files can push Excel past its practical limits.

How do you convert JSON to Excel with Power Query?

Use this workflow for small to medium JSON files you can flatten into a table:

1. Open Excel and create a blank workbook.
2. Go to the Data tab.
3. Select Get Data > From File > From JSON.
4. Choose your JSON file and click Open.
5. In Power Query, click To Table.
6. Select the expand icon to flatten nested fields.
7. Clean up columns by removing, renaming, and setting data types.
8. Click Close & Load.

• Sanity check: Columns should look like fields, rows should look like records, and totals should match what you expect.
• Export option: Save as XLSX or export the final table to CSV.

How do you handle nested JSON in Excel?

Nested JSON shows up in Power Query as Record and List values. The goal is to expand those fields until you have a usable table.

How do you flatten nested objects or Records?

1. Click the expand icon next to the Record column.
2. Select fields to expand into columns.
3. Optionally keep prefixes to avoid duplicate column names.

How do you flatten arrays or Lists?

1. Right-click the List column and choose Expand to New Rows.
2. Expand any remaining Records inside the new rows.

Rule of thumb: If you are expanding several levels deep or creating millions of rows, switch to Python or a tool designed for large JSON.

Should you convert JSON to CSV before opening it in Excel?

Converting JSON to CSV is a practical shortcut because CSV is already tabular. It can be faster than building a full Power Query workflow for one-off files.

• Dropped fields: The converter may ignore nested objects or arrays.
• Wrong flattening level: You may get duplicates or missing context if the wrong array is expanded.
• Lost traceability: Keep identifiers such as source file, plan IDs, provider IDs, and timestamps so the table stays auditable.

Which JSON-to-Excel method should you use?

Power Query is the default, but it is not always the best fit. Use the method below that matches your file size and workflow.

How do you convert JSON to Excel with Python?

Python is a strong option for large files and automated pipelines. With pandas, you can normalize nested structures and export to XLSX.

import pandas as pd

df = pd.read_json('yourfile.json')
df_flat = pd.json_normalize(df.to_dict('records'))
df_flat.to_excel('output.xlsx', index=False)

Are free online JSON-to-Excel converters safe?

Online tools are best for small, non-sensitive files. Before you upload anything, confirm whether processing happens in the browser or on a server. For healthcare pricing, patient, provider, or plan data, use a workflow with security controls that fit your organization's requirements.

Can Google Sheets import JSON?

Google Sheets can work via Apps Script or by importing a JSON-to-CSV endpoint. It is useful when collaboration matters more than scale.

What causes JSON imports to fail in Excel?

JSON-to-Excel conversion usually fails for a predictable set of reasons. Here are the fastest fixes.

Why are healthcare price transparency JSON files harder to convert?

Healthcare machine-readable files use JSON because the data is deeply hierarchical. Teams still need the output in rows and columns to filter, compare, benchmark, and export what matters.

• Common fields: Plan identifiers, provider groups, NPIs, TINs, negotiated rates, billing codes, service locations, and metadata.
• Common reality: Nested rate structures can expand into millions of records.

Payer transparency files are also distributed across many sources. AccessMRF lists 180 payer websites and 3 million Table of Contents files in its catalog, which can make a file-by-file spreadsheet workflow difficult to maintain.

Success is not opening the file. It is answering pricing and network questions with a table you can trust.

• What are negotiated rates for a target CPT, HCPCS, DRG, or revenue code?
• How do rates vary by payer, provider, plan, or geography?
• Where are outliers and benchmark opportunities?
• Which rows need normalization, enrichment, or provider matching?

For hospital price transparency requirements and file questions, use the CMS Hospital Price Transparency FAQ alongside the data documentation supplied with each file.

When should you use Gigasheet for large JSON and healthcare MRF files?

Gigasheet's healthcare price transparency data tools help teams work with nationwide negotiated-rate data across payers and providers without building their own data infrastructure.

• Flatten at scale: Open and expand large JSON, including compressed files, into workable tables.
• Filter before export: Narrow millions of rate rows to the slice you actually need.
• Downstream-ready: Export focused CSV extracts for models, dashboards, or data pipelines.
• Healthcare context: Compare commercial negotiated rates alongside Medicare benchmarks in a price transparency workflow.

If you need negotiated-rate intelligence inside your own systems, Gigasheet provides downloadable datasets and APIs for direct integration. You can also use the Machine-Readable File (MRF) Viewer to inspect files before creating an export.

When should you use Excel versus Gigasheet?

Use Excel when the JSON file is small, the structure is simple, and the final table fits comfortably inside a workbook. Excel is still useful for lightweight review, ad hoc calculations, and sharing a narrow extract.

Use Gigasheet when the JSON file is large, nested, zipped, or part of a healthcare MRF workflow. It is also a better fit when teams need to filter across millions of records, export focused subsets, or use nationwide price transparency data in analytics and applications.

What is the best way to get payer negotiated rates into a spreadsheet?

Start by identifying the codes, payers, providers, plans, and geography you need. Then flatten the relevant MRF records, retain source identifiers and plan context, and export only the filtered rate rows needed for your analysis.

For repeatable rate research, a self-service platform can reduce the manual work of downloading files, expanding nested JSON, and managing spreadsheet extracts. Gigasheet supports negotiated-rate analysis and focused exports for healthcare price transparency workflows.

Frequently asked questions

Can Excel open JSON files directly?

Yes. Excel can import many JSON files via Power Query. It may struggle with very large or deeply nested files.

How do I convert JSON to CSV for Excel?

Flatten the JSON into a table and export it as CSV. Use a converter that preserves key IDs and context when the JSON is nested.

How can I convert JSON to Excel for free?

Use Excel Power Query. For larger files, Python with pandas is also free and more scalable.

How can I convert JSON to a table format?

Import the JSON into Power Query, click To Table, then expand nested Records and Lists until the result is flat. Each object becomes a row and each key becomes a column.

Can I convert large JSON files to Excel?

Sometimes, but Excel is limited by performance and the 1,048,576-row worksheet limit. For large JSON, use Python or a large-file workflow before exporting a smaller slice to Excel.

Is it safe to convert JSON to Excel online?

It depends on whether the tool uploads your data to a server or processes locally in the browser. For sensitive data, prefer offline tools or a compliant platform with clear security controls.

Can Excel handle healthcare price transparency MRF JSON?

Excel can handle small samples, but full MRF JSON files are typically too large and nested for practical workbook analysis. Most teams need a workflow that can flatten and filter at scale first.

What should healthcare teams use instead of Excel for MRF JSON?

Use a toolchain that can parse large JSON, flatten nested structures, and filter millions of records before exporting. That workflow should also support repeatable extracts and traceable identifiers.

Last reviewed: October 26, 2026