Load a Folder of CSV or Excel Files into a Single Excel Worksheet

February 20, 2023 - by Bill Jelen

Problem: My I.T. department products a CSV file for every customer and I have to load all of those into Excel.

Strategy: Power Query can load all of the CSV files into a single grid. When the I.T. department adds more CSV files, you can simply refresh the query and Excel will load the entire folder again. If you have a folder of Excel files and you need to load a single worksheet from each workbook, the same steps will work.

Follow these steps:

1. Select New Query, From File, From Folder.
2. Browse to the folder.
3. In the Preview window, choose Edit instead of Load.
4. Excel will show you a list of all files in the folder.
5. Use the filter dropdown on the Type column to remove anything that is not a .CSV file.
6. Click the icon in the Binary column header. It is two arrows pointing down at a line.

The Content column is full of cells that say "Binary", which seems useless. But, next the header is an icon with two arrows pointing at a horizontal line. This is the Combine Files icon. — Figure 1014. The label on this icon doesn’t hint at the sheet power of loading all files.

I used Power Query for over a year before I realized this button did anything. I had to read the M is for (Data) Monkey book before I realized the awesome power of loading 200 CSV files into a single Excel worksheet in a single click.

This article is an excerpt from Power Excel With MrExcel

Title photo by Noah Windler on Unsplash