PDA

View Full Version : How to exclude certain sub-folders when using Get Data From Folder option



alfred
2017-03-16, 03:13 AM
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
2017-03-16, 07:35 AM
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
2017-03-16, 05:43 PM
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 (http://www.excelguru.ca/blog/2016/12/21/new-combine-binaries-experience/) that may/may not have hit your version of Excel just yet.

alfred
2017-04-04, 05:26 AM
Thanks gentlemen

alfred
2017-04-04, 05:33 AM
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