Extract folder and subfolders with Excel

Extract folder and subfolders with Excel
Last Updated on 03/05/2024
Reading time: 3 minutes

How to extract a folder and its subfolders in just 5 steps with Excel

  1. Load the path of your folder with Power Query

    Power Query is the easiest way to connect to a folder

  2. Keep only a few data

    Power Query will return many information but not all of them are needed

  3. Load the result into Excel

    Once you have finished manipulating and cleaning your data, load them into Excel

Step 1: Connect to a folder with Power Query

To Extract folders and subfolders with Excel, the best way is to use Power Query

  1. Got to the Data tab
  2. Click Get Data
  3. Then From File
  4. And finally From Folder
Menu Import Folder to extract files path into Excel
  1. In the following dialog box, click the Browse icon
Select the folder to import into Excel
  1. A new dialog box shows all the files in the folder and the subfolders selected. Simply click on the button Transform data
All the files in the folder and subfolders to extract into Excel

Step 2: Add the file's size

The size does not load by default. But the Record column contains some essential data that we will display.

  1. Click on the arrow in the Attributes column
Deploy the column Attributes
  1. Select only the Size field
  2. Unchecked Use original column name as prefix
Select the Size field

Step 3: Keep only 3 columns

We only keep 3 columns

  • The path
  • The names of the files
  • Size

With Power Query, there is a trick to remove and also reorder columns in the same action. Select in this order

  1. The Folder Path column
  2. Then the Name column (with the Ctrl key)
  3. And finally the Size  column
  4. Right-click on the headers
  5. Select Remove Other Columns
Only 3 columns kept be

The columns are re-ordered automatically 😉😮

Step 4: Delete the initial path

Power Query always displays the entire initial path. But we can easily remove the first part of the string like this

  1. Right-click on the header of the Folder Path column
  2. Select the Replace values option
Power Query menu to replace values
  1. Enter the search string (here C:\Users\fred_\OneDrive - FlgConsulting\20_EXCEL\)
  2. Leave the replacement string empty
Power Query dialog box to replace

Step 5: Split all the subfolders

Because we don't know how many subfolders we can have, we don't know how many times we will have the symbol \ in a string. So this task looks very complex to do .... but not with Power Query 😊😉

  1. Right-click on the header of the Folder Path column
  2. Select the option Split Column
  3. Then By Delimiter
Power Query option split column by delimiter
  1. Then, select the option Custom in the dropdown list
  2. Write the delimiter \
  3. VERY IMPORTANT, use the option Each occurrence of the delimiter
Option to split the column
  1. And then, immediately, Power Query creates as many columns as you have sub-folders In this example, the root folder has 4 levels.
Creation of 4 columns after the operation to split column
  1. To finish, we return the result to Excel (Home > Close & Load)
Power Query menu Close and Load
  1. Finally, you have extracted all the folders and subfolders in Excel
Extract of the folder and subfolders in Excel

5 Comments

  1. Luna
    07/09/2023 @ 12:55

    This is hands down the best tutorial I found. Should be made a template.
    Thank you so much for this!! Super helpful and straightforward.

    Reply

    • Frédéric LE GUEN
      07/09/2023 @ 13:01

      Thanks you so much for your nice comment Luca

      Reply

  2. morgan elmer
    11/07/2023 @ 19:40

    There is no transform data button. What version of Excell is this for?

    Reply

    • Frédéric LE GUEN
      12/07/2023 @ 02:40

      Hi, you don't have the Transform Data button? That's weird. I did my tuto with Excel 365 2 years ago, and I have checked, the button is still there.
      What is your Excel version? Which buttons do you have?
      Thanks

      Reply

  3. Abhishek
    25/08/2022 @ 07:01

    Hello,

    Great contents are there in this website. So many learning materials are there. Bui I am unable to find the practice data for those exercises. Please provide the way to download the data otherwise not able to practice properly.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Extract folder and subfolders with Excel

Reading time: 3 minutes
Last Updated on 03/05/2024

How to extract a folder and its subfolders in just 5 steps with Excel

  1. Load the path of your folder with Power Query

    Power Query is the easiest way to connect to a folder

  2. Keep only a few data

    Power Query will return many information but not all of them are needed

  3. Load the result into Excel

    Once you have finished manipulating and cleaning your data, load them into Excel

Step 1: Connect to a folder with Power Query

To Extract folders and subfolders with Excel, the best way is to use Power Query

  1. Got to the Data tab
  2. Click Get Data
  3. Then From File
  4. And finally From Folder
Menu Import Folder to extract files path into Excel
  1. In the following dialog box, click the Browse icon
Select the folder to import into Excel
  1. A new dialog box shows all the files in the folder and the subfolders selected. Simply click on the button Transform data
All the files in the folder and subfolders to extract into Excel

Step 2: Add the file's size

The size does not load by default. But the Record column contains some essential data that we will display.

  1. Click on the arrow in the Attributes column
Deploy the column Attributes
  1. Select only the Size field
  2. Unchecked Use original column name as prefix
Select the Size field

Step 3: Keep only 3 columns

We only keep 3 columns

  • The path
  • The names of the files
  • Size

With Power Query, there is a trick to remove and also reorder columns in the same action. Select in this order

  1. The Folder Path column
  2. Then the Name column (with the Ctrl key)
  3. And finally the Size  column
  4. Right-click on the headers
  5. Select Remove Other Columns
Only 3 columns kept be

The columns are re-ordered automatically 😉😮

Step 4: Delete the initial path

Power Query always displays the entire initial path. But we can easily remove the first part of the string like this

  1. Right-click on the header of the Folder Path column
  2. Select the Replace values option
Power Query menu to replace values
  1. Enter the search string (here C:\Users\fred_\OneDrive - FlgConsulting\20_EXCEL\)
  2. Leave the replacement string empty
Power Query dialog box to replace

Step 5: Split all the subfolders

Because we don't know how many subfolders we can have, we don't know how many times we will have the symbol \ in a string. So this task looks very complex to do .... but not with Power Query 😊😉

  1. Right-click on the header of the Folder Path column
  2. Select the option Split Column
  3. Then By Delimiter
Power Query option split column by delimiter
  1. Then, select the option Custom in the dropdown list
  2. Write the delimiter \
  3. VERY IMPORTANT, use the option Each occurrence of the delimiter
Option to split the column
  1. And then, immediately, Power Query creates as many columns as you have sub-folders In this example, the root folder has 4 levels.
Creation of 4 columns after the operation to split column
  1. To finish, we return the result to Excel (Home > Close & Load)
Power Query menu Close and Load
  1. Finally, you have extracted all the folders and subfolders in Excel
Extract of the folder and subfolders in Excel

5 Comments

  1. Luna
    07/09/2023 @ 12:55

    This is hands down the best tutorial I found. Should be made a template.
    Thank you so much for this!! Super helpful and straightforward.

    Reply

    • Frédéric LE GUEN
      07/09/2023 @ 13:01

      Thanks you so much for your nice comment Luca

      Reply

  2. morgan elmer
    11/07/2023 @ 19:40

    There is no transform data button. What version of Excell is this for?

    Reply

    • Frédéric LE GUEN
      12/07/2023 @ 02:40

      Hi, you don't have the Transform Data button? That's weird. I did my tuto with Excel 365 2 years ago, and I have checked, the button is still there.
      What is your Excel version? Which buttons do you have?
      Thanks

      Reply

  3. Abhishek
    25/08/2022 @ 07:01

    Hello,

    Great contents are there in this website. So many learning materials are there. Bui I am unable to find the practice data for those exercises. Please provide the way to download the data otherwise not able to practice properly.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *