Import all sheets from all files in a folder into Excel

This is the second post of a series that covers everything about importing all files in a folder. Click here to see the series of post.

In this scenario I’ll show you to import all sheets from all excel files in a folder.  If you want to follow along, download the files from this link.

Important: I’ll show how to import all sheets from all Excel files in a folder, however, it is the same process if the files contain just one sheet.

Step 1: Import the files from the desired folder

Go to Data -> New Query -> From File -> From Folder

Click on ‘Browse’ and browse for the folder that contain the files, then click OK.

Once you click OK, press Edit on the next window.

Then you’ll see a window with several details of each file in the folder such as Extension, Date accessed, Date modified, etc.

We’re only interested in the Content column, therefore, right-click on the header of the Content column and select Remove Other Columns.

The go to Add Column -> Add Custom Column

Step 2: Extract the contents from each file

In the next dialog box, type a name for the new column (e.g. FileContent) and the custom formula: =Excel.Workbook([Content], true) and click OK.

Note: The second argument of the function Excel.Workbook is to promote the headers from each sheet. If you don’t set this argument to true, you’ll have to promote the headers afterwards and then filter out the headers from the data.

Then click on the column and click OK in the Expand dialog box.

Then go to the filter button of the FileContent.Kind column, select Sheet and click OK.

Right-click the header of the FileContent.Data column and select Remove Other Columns.

Then click in the Expand button in the column FileContent.Data

In the next dialog box, make sure to uncheck the option “Use original column name as prefix” and click OK.

The data from ALL SHEETS from ALL FILES should be visible now. Isn’t this amazing?

Now make any further changes needed. For example, change the format of the Date column to date (Right-click the header of the Date Column, go to Change Type -> Date)

Step 3: Load the data into Excel

Go to File -> Close & Load To…

Select Table and the destination of the results (New worksheet or Existing worksheet), then click on Load.

That’s All!!!

Click here if you want to see the other posts in the series.

Want to continue learning about automating your data preparation processes?? Subscribe to the blog.