In a previous post we showed how to export a list of filenames from a folder into Excel. But what can you do to export a list of files AND subfolders AND files in these subfolders into an Excel list? Here we are going to use a small trick in PowerQuery to export folder and subfolder list to Excel.
Table of contents
YouTube Export folder and subfolder list to excel
Export the sub folders to Excel
As shown in the post of how to list filenames in Excel, to get to import the file names into Excel you need to go to
- Data, Get Data, From File. From Folder (Excel 2016) or
- PowerQuery, From File, From Folder (Excel version with a separate PowerQuery Ribbon)
You will then be able to Browse to the folder. Depending on your version, you may only be able to click on a folder that has no other folders in it (the more recent versions seems to have addressed this). So as shown below we want to click on the Z_PowerQuery_Folder_Extract but we are forced to click on one of the dated folders.
If you can’t click on the folder you want and click OK, then go to the level that will allow you to click OK (in the case above click on the 20170131 folder. As shown below the file path will appear including this folder. Simply delete the last folder or folders until the last ‘\’ after the actual folder you want itemised.
Click OK and load it into Excel. As shown below, all the files (including non Excel and CSV files like word and powerpoint) will be itemised with their own column for the types (extensions) and the path name (which gives you the folder each file was in).
You could work on this in PowerQuery to make the filename contain the folder name e.g. in the example above the month is only in the folder name i.e. 20170131 and then it shows the division name. You can get PowerQuery to combine the folder name and file name to make it more explicit e.g. 20170131 Division 1 etc.
To learn how to do this, look at our Online Get & Transform course.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
How to export file names from a folder to Excel
Import all files in a folder into Excel
Merge multiple csv files into a single file for use in Excel (DOS method)