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

test.zip