Power Query Problems

How to Combine CSV Files with Multiple Sections Using Power Query

Huda Qurayshi Oct 14, 2026 871 views

Question details

The user needs to merge hundreds of complex CSV files that contain distinct named sections, variable-length tables, and coordinate-based calculations into a single dataset.

How to Combine CSV Files with Multiple Sections Using Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Aggregating large datasets from multiple complex CSV files with non-standard structures.
Observed behavior: The goal is to determine if Power Query and the Data Model can handle this transformation and combination process natively without requiring VBA scripting.

Before you start

Check your computer's RAM and CPU resources, and review Excel's Data Model limits, as processing hundreds of complex CSV files requires significant memory allocation.

Solution 1Recommended

Use Power Query to Extract and Combine Variable Tables

Build a custom Power Query transformation to parse sections and merge matching tables across all CSV files without relying on VBA.

Power Query is fully capable of handling complex CSV files with variable-length tables. The key to success is creating a robust query on a sample file that correctly parses the named sections before appending the files together.

Get Data from Folder

Open Excel, navigate to the 'Data' tab, select 'Get Data', choose 'From File', and then click 'From Folder' to target the directory containing your hundreds of CSV files.

Transform the Sample File

Click 'Transform Data' to open the Power Query Editor. Edit the helper query for the sample file by adding custom or conditional columns to identify and tag the named sections within the CSV.

Extract and Filter Tables

Filter the rows to isolate the specific variable-length tables you need. Use the 'Promote Headers' feature if necessary to structure each extracted table correctly.

Combine Matching Data

Once the sample file query correctly parses a single file, this logic will apply as a custom function to all files in the folder. Expand the table column in your main query to combine all matching names and sections into one unified view.

Load to Data Model

Perform any required repeated coordinate-based calculations inside the query Editor. Finally, click 'Close & Load To...' and check 'Add this data to the Data Model' to process the large dataset.

Use Power Query to Extract and Combine Variable Tables

Performance Warning: Processing massive amounts of unformatted CSV data can slow down Excel. Ensure you are using the 64-bit version of Microsoft Office to prevent memory overflow limits.

Free Microsoft Office alternative

Handle Large Datasets and CSV Files with WPS Office

While complex Power Query models are native to Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative for everyday data processing, CSV file management, and spreadsheet calculations without bogging down your computer.

1. Install WPS Office: Download and install the free version of WPS Office from the official website.
2. Open CSV Data: Launch WPS Spreadsheets, navigate to the 'Data' tab, and click 'Import Data' to easily load your CSV files.
3. Analyze Information: Use built-in pivot tables, formulas, and text-to-columns features to analyze and restructure your dataset efficiently.

Highly compatible with Microsoft Excel formats including .xlsx and .csvLightweight software architecture that consumes less RAM for large datasetsFree to use with a familiar, tabbed user interfaceBuilt-in data import tools and powerful formula support for coordinate-based calculations

microsoft office alternative - wps office

Frequently Asked Questions

Do I need VBA to combine multiple CSV files in Excel?

No, VBA is not strictly required. Power Query can automatically parse, filter, and combine multiple CSV files from a single folder as long as the transformation logic is correctly defined.

What are the limits of Excel's Data Model when processing large CSV files?

The Data Model in 64-bit Excel is primarily limited by your system's available memory (RAM) and resources, though it can theoretically handle hundreds of millions of rows. 32-bit Excel is strictly limited to 2GB of virtual memory.

How do I extract a specific named section from a CSV file using Power Query?

Within the Power Query Editor, you can use conditional columns and index filtering to identify the start and end rows of a named section, allowing you to extract only the relevant variable-length tables before merging the files.

Excel & Data Problems