topic Re: Extracting the list of sheet names across multiple files efficiently in Alteryx Designer Desktop Discussions

Extracting the list of sheet names across multiple files efficiently

pickledgreens — Sun, 16 Nov 2025 20:43:10 GMT

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.

Re: Extracting the list of sheet names across multiple files efficiently

Yoshiro_Fujimori — Mon, 17 Nov 2025 02:57:45 GMT

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

Re: Extracting the list of sheet names across multiple files efficiently

pickledgreens — Mon, 17 Nov 2025 20:45:53 GMT

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.

Re: Extracting the list of sheet names across multiple files efficiently

EKasminsky — Mon, 17 Nov 2025 21:25:35 GMT

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

Re: Extracting the list of sheet names across multiple files efficiently

Yoshiro_Fujimori — Mon, 17 Nov 2025 22:54:24 GMT

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.

Re: Extracting the list of sheet names across multiple files efficiently

caltang — Mon, 17 Nov 2025 23:06:02 GMT

This is excellent @Yoshiro_Fujimori !

Re: Extracting the list of sheet names across multiple files efficiently

pickledgreens — Tue, 18 Nov 2025 17:04:22 GMT

Thank you both, I have figured out the issue, which is that I did not update the Action tool to replace the specific filepath I was referencing. The workflow now works. Thanks!

Re: Extracting the list of sheet names across multiple files efficiently

amnahhussain02 — Tue, 02 Dec 2025 14:34:13 GMT

Hi, I'm stuck on this. Did you update the action tool as the file path of the input data file you put in? I'm guessing \Inputs\Company 1\2025 Q3\Q3'25_Company 1 v1.xlsx. ?

Re: Extracting the list of sheet names across multiple files efficiently

amnahhussain02 — Tue, 02 Dec 2025 15:08:55 GMT

Hi, also did you run into an issue where it said that all the other files have a different number of fields than the 1st file in the set and will be skipped? Any solutions?

Re: Extracting the list of sheet names across multiple files efficiently

NeoInfiniTech — Tue, 02 Dec 2025 17:20:53 GMT

Hi @amnahhussain02,

I would like to recommend to you a macro I developed, NeoInfiniTech CFMI. It is able to read the directory and filter both the file names and the sheet names separately, and by using formula if necessary.

Please follow the instructions found here (old version of the macro) for the installation of the new version of the macro: CSV Friendly Multi-Input (Inspired by CReW Wildcard Input).

If you are currently on a version of Alteryx Designer 2023.1 to 2024.2, then install the v23 version. If you are on the latest version of Alteryx Designer (currently 2025.1), then install the v25 version.

Unlike Dynamic Input, you should not run into any "will be skipped" messages when using this macro.

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