How to Import Nested Text or JSON Data into Excel Columns Using Power Query
Question details
The user needs to parse a text file containing nested square-bracket blocks and quoted phrases into separate columns in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing structured text with custom brackets or JSON data into a flat, tabular format for analysis.
- Observed behavior
- The data imports as a single string or nested record, failing to automatically distribute the internal nested structures into separate flat columns.
Verify the structure of your text file to determine whether it is a properly formatted JSON document or a custom text format with inconsistent nesting, as this dictates the exact Power Query method required.
Import Properly Formatted JSON Data Using Power Query
Use this method if your file follows standard JSON syntax, allowing Power Query to automatically detect and parse the nested elements.
Power Query features a built-in JSON connector that accurately reads nested hierarchies like arrays (square brackets) and objects (curly braces) and provides tools to flatten them.
Open Excel, navigate to the Data tab, click on 'Get Data', select 'From File', and then choose 'From JSON'.
Locate your text or JSON file in the file explorer and click 'Import' to launch the Power Query Editor.
In the Power Query Editor, look for columns containing the words 'List' or 'Record'. Click the two-way expansion arrow icon located in the column header.
Select 'Expand to New Rows' from the dropdown menu. Repeat this process for any sub-levels of nesting until all required data fields are visible as flat columns.
Once your columns are correctly formatted, click 'Close & Load' in the Home tab to export the parsed data into your Excel spreadsheet.
Transform Custom Nested Text Formats Manually
Use this approach if your file contains custom text formatting with inconsistent brackets that the standard JSON parser cannot read.
Experience Seamless Data Management with WPS Office
While highly advanced Power Query parsing is specific to Microsoft Excel, WPS Office provides robust, lightweight, and highly compatible spreadsheet tools for everyday data formatting, CSV handling, and text-to-columns tasks without the premium price tag.
- 1. Download and Install: Visit the official WPS Office website to download the free installer for Windows, Mac, or Linux.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and instantly open your existing Excel workbooks and data files.
- 3. Manage Data Easily: Utilize powerful built-in data handling tools like Text to Columns and Data Import wizards to quickly organize your flat data files.
Frequently Asked Questions
How do I know if my nested text file is valid JSON?
Valid JSON uses standard key-value pairs separated by colons, with objects enclosed in curly braces {} and arrays in square brackets []. You can copy a snippet of your text file and paste it into a free online JSON validator to check its format.
Why is Power Query only showing the words 'List' or 'Record' instead of my actual data?
This happens when your imported text or JSON contains embedded tables or arrays at that level. To view the underlying data, click the two-way expansion arrow icon located at the right side of the column header to expand those records into new rows.
Can Power Query extract data from files with uneven nesting levels?
Yes. When expanding records or lists that do not share uniform nesting, Power Query handles missing fields gracefully by returning a null value in the generated column for the entries lacking data at that specific level.
