Results 1 to 8 of 8

Thread: How to exclude certain sub-folders when using Get Data From Folder option

  1. #1

    How to exclude certain sub-folders when using Get Data From Folder option



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    Appreciate any assistance with above query. Getting an IO error when trying to load data to model. I suspect it may be because some of the sub-folders in the source folder are protected.

    Alfred

  2. #2
    With "Get Data From Folder", you get a navigation table with all files in the folder and its sub folders. In this navigation table, you can deselect folders (folder paths are in the right-most column).

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,493
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Marcel is dead on with this, but I'll add a bit more from a best practice perspective.

    When I hit the Get Files From Folder, I'll do a couple of things:
    1) change the file extension type to all lower case
    2) filter the file type to ONLY the file type I'm expecting to work with
    3) filter out any subdirectories I do not want to combine
    4) filter out any files that start with ~$ (these are temp files)
    5) add any other required filters to get down to just the files I need

    I ALWAYS do the first two, just as a matter of good practice (in case some joker throws a Word doc in the folder), but the others can be applied as needed in order to protect against the issue you're experiencing.

    I then name that query "FilesList", create a new query by referencing my FilesList query, and combine from there. This practice will set you up best as you roll into the new Combine Binaries experience that may/may not have hit your version of Excel just yet.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Thanks gentlemen

  5. #5
    Actually tried #3 (filtering out sub-folders) but power query performance was so slow.

    I guess there are some performance limitations to power query's "get from folder" option, particularly if the source folder is huge and the reports in question are in so many different sub-folders e.g. the data dumps I was after were saved in separate daily subfolders in monthly subfolders in yearly subfolders in the source folder....

    As a workaround, I manually copied these files to a single folder and proceeded from there

    Thanks again
    Alfred

  6. #6
    Neophyte CharlieO's Avatar
    Join Date
    Mar 2022
    Posts
    1
    Articles
    0
    Excel Version
    M365 (2016 local client)
    Quote Originally Posted by Ken Puls View Post
    Marcel is dead on with this, but I'll add a bit more from a best practice perspective.

    When I hit the Get Files From Folder, I'll do a couple of things:
    1) change the file extension type to all lower case
    2) filter the file type to ONLY the file type I'm expecting to work with
    3) filter out any subdirectories I do not want to combine
    4) filter out any files that start with ~$ (these are temp files)
    5) add any other required filters to get down to just the files I need

    I ALWAYS do the first two, just as a matter of good practice (in case some joker throws a Word doc in the folder), but the others can be applied as needed in order to protect against the issue you're experiencing.

    I then name that query "FilesList", create a new query by referencing my FilesList query, and combine from there. This practice will set you up best as you roll into the new Combine Binaries experience that may/may not have hit your version of Excel just yet.
    Hi Ken...I've read a number of your articles, thanks for what you've done for the community! I have a slightly different nuance to this...Is there any way to limit the folder search to the specified folder w/o subfolders as part of the initial "Source" step rather than a filter subsequent to "Source?"

  7. #7
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    153
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by CharlieO View Post
    Hi Ken...I've read a number of your articles, thanks for what you've done for the community! I have a slightly different nuance to this...Is there any way to limit the folder search to the specified folder w/o subfolders as part of the initial "Source" step rather than a filter subsequent to "Source?"
    If you don't want subfolders, you would filter the Folder Path to just the path you want.
    So if the folder is called C:\My Files\, you would add a step where Folder Path = C:\My Files\. This would filter out any subfolder.
    Oh... by the way, YOU'RE WELCOME!

  8. #8
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    177
    Articles
    0
    Excel Version
    Office 365
    I have no idea if it is a new feature, but if you use Folder.Contents, instead of Folder.Files, then only files of the specified folder are imported, without taking subfolders into account.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •