Process Millions of JSON Records with Excel Power Query in WPS Office
Question details
The user wants to import, parse, and combine millions of JSON or text records from Companies House files in Excel Power Query without relying on fragile delimiter splitting.
- Product
- WPS Office Excel / Power Query
- Device & OS
- not provided
- Scenario
- The user has downloaded Companies House public data files and needs to expand fields such as company number and year while handling optional fields that may be missing from some records.
- Observed behavior
- The desired state is a combined Power Query result that loads only the required fields into Excel or a data model instead of writing millions of raw rows to a standard worksheet.
Create a dedicated folder containing the downloaded Companies House JSON or text files, and open a small sample in Power Query to identify the paths for company number, year, and optional fields. Avoid cleaning the files with Text to Columns because delimiter splitting breaks JSON structures.
Import Companies House JSON files from a folder and expand required fields in Power Query
Use this query-first method when you need a repeatable import that parses JSON or line-delimited text and can safely handle missing optional fields.
Power Query reads all files in the folder, applies the same JSON parsing and column expansion, and lets you manage millions of records without loading them into a normal worksheet first. This avoids the fragile delimiter splitting used by Text to Columns and preserves the JSON structure.
Place all Companies House JSON or text files in one local folder, then open WPS Office Excel and create a new workbook.
Go to Data > Get Data > From File > From Folder, select the folder containing the files, and choose Combine or Transform Data.
In Power Query Editor, select the Content or Data column, then use Transform > Parse > JSON to convert each line or document into a record.
Click the expand icon on the record column and select company number, year, and other required fields. Missing optional fields appear as null and can be handled safely.
Filter or choose the required columns, then click Home > Close & Load To and select Only Create Connection or a Data Model load to avoid writing 11.5 million rows to a worksheet.
Process very large datasets outside the worksheet and aggregate before loading
Use this when working with approximately 11.5 million rows or when a standard worksheet becomes slow, so you only load the required results.
Process Companies House JSON records with WPS Office Excel
WPS Office provides a free spreadsheet environment for importing JSON or text files from a folder, expanding company number and year fields, and loading only the required results into .xlsx workbooks.
- 1. Download and open WPS Office: Download WPS Office, open WPS Spreadsheets, and create a new workbook.
- 2. Connect to the folder: Go to Data > Get Data > From File > From Folder and select the folder containing your Companies House JSON or text files.
- 3. Parse and expand the JSON records: In the query editor, use the JSON parse option, then expand the record column to choose company number, year, and other required fields.
- 4. Load only the required results: Use Close & Load To to load a filtered or summarized table to the workbook or keep the query as a connection.
Frequently Asked Questions
Can Power Query import Companies House files that are saved as .txt rather than .json?
Yes. In WPS Office Excel, use Data > Get Data > From File > From Folder, select the files, and in Power Query choose Parse > JSON. Power Query can read the content as JSON or line-delimited text without Text to Columns.
How do I handle optional fields that are missing from some records?
Missing optional fields become null when you expand the JSON record. Use Power Query's Replace Errors or filter arrows to manage nulls, or leave them null and only load the required columns.
Can Excel load all 11.5 million rows into a normal worksheet?
No, a standard worksheet is not the right place for that many rows. Keep the query as a connection or load to the Data Model, and aggregate or filter before loading the final table.
Should I split the columns with Text to Columns instead?
No. Text to Columns splits by delimiters and can break JSON structures. Use Power Query's JSON parser to expand fields such as company number and year reliably.
Where do I get the Companies House data?
Download the public files from the Companies House data-download site, for example the PSC data page, and put the JSON or text files in a dedicated folder for the Power Query import.
