Export folder or file list to Excel

Export the list of files contained in a folder or sub-folders has always been a difficult task. Now with Power Query this job is as easy as 1-2-3 😎😃😍

But more, if you don’t want the list of files but just the folder and sub-folders name, it’s also easy.

Connect Power Query to a path

  1. Go to the tab Data
  2. Select the menu Get Data
  3. Sub-menu From File
  4. Select the menu From Folder

In the dialog box, you select the path you want to import (click on the button Folder)

You can visualized a short list of the files in the folder and then you click on Edit

Power Query opens with the list of folders and files.

Export folder list

If you just want to export the folder list, and sub-folders, in Excel, you just have to follow these 4 steps

1. Remove Other Columns

First, we keep only the columns with the folders

  1. Select the column Folder Path (the last one)
  2. Right-click
  3. Select the option Remove Other Columns

2. Remove Duplicates

Then, we keep the name of each folder only once

  1. Right-click on the single column that you have in Power Query Editor
  2. Select the option Remove Duplicates

3. Remove the beginning of the path

This step in not necessary if you want to keep the starting path.

  1. Right-Click on the column
  2. Select the option Replace values
  3. In the text box Value to Find, write the original path (in this case C:\Users\fred_\OneDrive\20_EXCEL\)
  4. Leave the field Replace with empty 

Now, the result is

4. Split on the \

Last step, we split each string on each symbol \

  1. Right-Click on the column
  2. Select the option Split Columns
  3. Then the sub-option By Delimiter
  4. In the first Dropdown list, select Custom
  5. And write the delimiter, the symbol \
  6. The option Each occurrence of the delimiter must be selected

Final result

And that’s finish 😄 Click on the icon Home > Close & Load to load the result in Excel

Export file list 

If you want to extract the file list and the folder, there is few more steps but it’s still very easy. Let’s restart from the step of load of the path.

1. Add the file size

In the informations displayed, the size of the files is missing. Well in fact it is there but not visible yet.

  1. On the column header Attribute, click on the double-arrows icon
  2. Unchecked all the options except Size
  3. At the bottom of the list, unchecked Use original column name as prefix

Now you have added the file size for each files

2. Keep few columns

Here we must keep at last 3 columns. If you want to keep more (like the date of creation for instance), you have to include the column in your selection

  1. Select in this order, the column Folder Path, Name, Size (use the Control key to do the selection)
  2. Right-Click on the header (take care to not deselected the 3 columns)
  3. Select the option Remove Other Columns

And the result is this one

3. Redo previous steps

To finalize, redo the steps seen in the previous example

  1. Replace the original path
  2. Split by delimiter

Then load to Excel and you have immediately, the full list of files of the original path

Leave a Reply

Your email address will not be published.