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

alfred

New member
Joined
Mar 16, 2017
Messages
6
Reaction score
0
Points
0
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
 

MarcelBeug

New member
Joined
Jun 21, 2014
Messages
100
Reaction score
0
Points
0
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).
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 

alfred

New member
Joined
Mar 16, 2017
Messages
6
Reaction score
0
Points
0
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
 

CharlieO

New member
Joined
Mar 2, 2022
Messages
1
Reaction score
0
Points
0
Excel Version(s)
M365 (2016 local client)
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?"
 

Nick Burns

Member
Joined
May 24, 2017
Messages
157
Reaction score
0
Points
16
Excel Version(s)
Office 365
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.
 

pinarello

Member
Joined
Jun 21, 2019
Messages
200
Reaction score
2
Points
18
Location
Germany
Excel Version(s)
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.
 
Top