Extracting the list of sheet names across multiple files efficiently

[pickledgreens]

Hi, I'm new to Alteryx and would appreciate some advice on how I can develop my workflow for a data collection exercise I run every quarter.

My files are currently structured as such: Main input folder with individual respondent folders. Each respondent has sub-folders divided into each quarter (e.g., 2025 Q3, 2025 Q2, 2025 Q1). In each quarter's folder, there's an Excel survey response denoted by version type (e.g., v1, v2, etc.). Each survey response is structured the same, regardless of the respondent; sheet names are all named and structured the same.

I want to pull the list sheet names across all the most recent versions of the survey responses for each quarter. I currently have a directory of file names and file paths of the latest versions, but I am stuck as to how to efficiently pull the sheet names for each file and have them appended.

I then want to filter this output by distinct sheet names (e.g., filter by Sheet 1 to have all the Sheet 1s from all responses in one table), then pull all data relating to that sheet name into a union dataset. So the end goal is to have all data from Sheet 1 across all survey responses in one dataset, and all data from Sheet 2 across all surcey responses in another dataset, so on and so forth.

This is currently my logic for how I would go about eventually pulling all the data by distinct sheet names, but I'm open to suggestions. Let me know if it would be helpful to clarify any aspect of my ideal workflow, and thank you in advance for your guidance.

[Yoshiro_Fujimori]

I understand you want to get the sheet names in Excel files under multiple subdirectories, in short.

You can

- get the directory information with Directory Tool,

- get the Sheet Names in each file with Input Data Tool,

- filter the sheets you want, and 

- read the data of your desired sheet in your desired file with Dynamic Input Tool.

The attachment is a sample workflow with Data in Sub Folders. I hope it helps.

Main

Batch Macro

Input Data
There are 2 files in 2 folders and each file has 2 sheets;

FileName	SheetName
C:\Users\yoshi\Downloads\test\folder1\File1.xlsx	Sheet1
C:\Users\yoshi\Downloads\test\folder1\File1.xlsx	Sheet2
C:\Users\yoshi\Downloads\test\folder1\File2.xlsx	Sheet1
C:\Users\yoshi\Downloads\test\folder1\File2.xlsx	Sheet2
C:\Users\yoshi\Downloads\test\folder2\File1.xlsx	Sheet1
C:\Users\yoshi\Downloads\test\folder2\File1.xlsx	Sheet2
C:\Users\yoshi\Downloads\test\folder2\File2.xlsx	Sheet1
C:\Users\yoshi\Downloads\test\folder2\File2.xlsx	Sheet2

Output Data
You can find the folder name, the file name and sheet name from [FileName] column;

Name	Value	FileName
a	1	C:\Users\yoshi\Downloads\test\folder1\File1.xlsx|||Sheet1
b	2	C:\Users\yoshi\Downloads\test\folder1\File1.xlsx|||Sheet1
c	3	C:\Users\yoshi\Downloads\test\folder1\File1.xlsx|||Sheet1
a	1	C:\Users\yoshi\Downloads\test\folder1\File2.xlsx|||Sheet1
b	2	C:\Users\yoshi\Downloads\test\folder1\File2.xlsx|||Sheet1
c	3	C:\Users\yoshi\Downloads\test\folder1\File2.xlsx|||Sheet1
a	1	C:\Users\yoshi\Downloads\test\folder2\File1.xlsx|||Sheet1
b	2	C:\Users\yoshi\Downloads\test\folder2\File1.xlsx|||Sheet1
c	3	C:\Users\yoshi\Downloads\test\folder2\File1.xlsx|||Sheet1
a	1	C:\Users\yoshi\Downloads\test\folder2\File2.xlsx|||Sheet1
b	2	C:\Users\yoshi\Downloads\test\folder2\File2.xlsx|||Sheet1
c	3	C:\Users\yoshi\Downloads\test\folder2\File2.xlsx|||Sheet1

[pickledgreens]

Thank you, this is very helpful. I have run into an issue where the batch macro pulls the sheet names of the sample file (e.g. from Company 1) but the main workflow shows duplicates of all the same company instead of the sheet names of all companies.

I'm thinking this is because of my file reference within the macro. The current filepath I'm using as the sample is: ..\Inputs\Company 1\2025 Q3\Q3'25_Company 1 v1.xlsx. The issue seems to be that the filepath reference is fixed but I'm not sure how to correct it. Any ideas?

Appreciate your time helping me to troubleshoot this, thank you.

[EKasminsky]

Are you including subfolders with your Directory tool? You can move up the file path to \Inputs\ possibly to gather more files.

[Yoshiro_Fujimori]

In my sample workflow, Directory Tool is configured to get the list of files under the current directory (".").

You can change the setting to the full path to the top level of the folder where the data files are stored.

[caltang]

This is excellent @Yoshiro_Fujimori !