Power Query Problems

How to Automatically Combine CSV Files from a Folder in Excel

Partner Editor Oct 12, 2026 870 views

Question details

The user needs to consolidate data from multiple CSV files located in a specific folder into a single Excel worksheet, ensuring the data updates automatically when new files are added.

How to Automatically Combine Multiple CSV Files from a Folder in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing and merging multiple CSV files into one master sheet for data analysis and reporting.
Observed behavior: Instead of manually copying and pasting data from each file, the user wants a dynamic solution where the combined worksheet can be refreshed to include newly added CSV files.

Before you start

Ensure all CSV files you want to combine are placed in the same folder and share identical column structures and headers to prevent data misalignment during the merge.

Solution 1Recommended

Combine CSV Files using Get Data (Power Query)

The most efficient way to merge multiple CSV files dynamically using Excel's built-in Power Query feature.

Power Query is a powerful data preparation tool built into Excel. By connecting directly to a folder, it allows you to append multiple CSV files instantly and refresh the connection whenever the folder's contents change.

Locate the Folder

Open Excel and navigate to the 'Data' tab on the ribbon. Click on 'Get Data' > 'From File' > 'From Folder'.

Select the Directory

Browse to the folder containing your CSV files on your computer, select it, and click 'Open'. A preview dialog will appear listing all the files within that folder.

Combine and Transform

Click the 'Combine' dropdown button at the bottom of the dialog box and select 'Combine & Transform Data'. This will open the Power Query Editor.

Verify File Format

In the preview window, confirm that the delimiter (usually a comma) and the columns are correct based on your sample file, then click 'OK'.

Load the Data

Once the Power Query Editor processes the merge, click 'Close & Load' in the top-left corner to output the fully combined data into a new Excel worksheet.

Combine CSV Files using Get Data (Power Query)

Auto-update feature: When a new CSV file is added to the folder, you do not need to repeat these steps. Simply go to the 'Data' tab and click 'Refresh All' to automatically append the new data to your worksheet.

Solution 2

Merge CSV Files using Command Prompt (Windows)

A quick workaround to physically merge all text/CSV files into a single file before importing them into Excel.

Free Microsoft Office alternative

Manage and Edit Spreadsheets Seamlessly with WPS Office

While Power Query is specific to Microsoft Excel, WPS Office provides a highly compatible, lightweight, and free alternative for viewing, editing, and managing your CSV files and complex datasets.

1. Download WPS Office: Visit the official WPS website and download the free desktop application for your operating system.
2. Open WPS Spreadsheet: Launch the application and select WPS Spreadsheet to easily manage your data.
3. Import and Edit: Open your CSV or Excel files seamlessly and utilize familiar spreadsheet tools for your data analysis.

Seamless compatibility with Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight architecture that loads large datasets quickly without consuming heavy system resources.Familiar user interface making migration from Microsoft Office intuitive and effortless.Free to download with robust built-in data processing and filtering tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my CSV files not combining correctly in Power Query?

This usually happens if the CSV files have different column structures, varying header names, or different delimiters. Ensure all files in the folder share the exact same layout and formatting before importing.

How do I refresh the data when I add a new CSV file to the folder?

Simply drop the new CSV file into your designated folder, open your master Excel workbook, navigate to the 'Data' tab on the ribbon, and click 'Refresh All'. Power Query will automatically process and append the new file.

Can I combine standard Excel (.xlsx) files using this same method?

Yes, the 'Get Data > From Folder' feature works equally well for standard Excel workbooks. You will just need to specify the sheet name or table you wish to extract from each workbook during the Combine & Transform step.

Will the combined data include the file names of the original CSVs?

Yes. By default, Power Query automatically adds a 'Source.Name' column during the combination process, which displays the original file name for each row of data. You can choose to keep this column or remove it within the Power Query Editor.

Excel & Data Problems