Import Excel Files From Multiple Zip Subfolders

Question

Hi Community,

I have a folder called "Main Folder" in C:\Users\MysticalAz\Desktop\Main Folder.

There are three subfolders in "Main Folder" for location

- California

- Florida

- New York

I'm specifically interested in the New York folder only (C:\Users\MysticalAz\Desktop\Main Folder\New York). The New York Folder contains more folders for dates

- New York Nov 2023 (Compressed (zipped) Folder)

- New York Dec 2023  (Compressed (zipped) Folder)

- New York Jan 2024 (Compressed (zipped) Folder)

Each of these folders have another folder in them:

- SUBFOLDER 202311

- SUBFOLDER 202312

- SUBFOLDER 202401

Within each of these subfolders, there's a file called "TestData" which is an excel file. Is there a way to use the main folder directory to capture all files that have the words "TestData"?

mystasz · Answer

Hi @Raj

I tested this out and yes, it does not matter how many folders or subfolders there are, it should work. Now, in my real life scenario, the folders in "New York Dec 2023".. and so on are compressed zip files.

I continued with the same approach and now it does not read. So the issue is the zip files. Is there a solution for that?

Raj · Answer

@mystasz so it does not matter how many folders or sub folders do you have it will take files from all your sub-directories within that folder which in your case is Newyork

also to pull files only with "Testdata.Xlsx" you can change the file specifications, and this will only pull testdata.xlsx for all the sub directories within main folder.
also youcan use special characters if you have some prefix or sufix to test data and this will work.

mystasz · Answer

Hi @DataNath - Thanks for your response. This approach definitely works if the subfolder names were consistent. I updated my question with more details about the issue.