Append worksheets from multiple Excel workbooks

[45179902]

I have several Excel workbooks all stored in the same directory. This directory includes only these workbooks. Each workbook has multiple worksheets. All the worksheets across these workbooks share the same format. The rows and columns can be appended together either horizontally or vertically.

Now, I'm looking to append the worksheets across all these Excel workbooks vertically and produce this output in a new Excel workbook. So the output of this workflow should be an Excel workbook with just one worksheet containing all data from these Excel workbooks in the directory. 

To give you an illustration, worksheets are like:

ID	20220505
First Name	George
Last Name	Li
Position	Alteryx Configurator

ID	20220505
First Name	George
Last Name	Li
Position	Alteryx Configurator

These are in the same worksheet. Each worksheet has around 500 records. Each workbook has around 30 sheets corresponding to each day in a month. I would also like to have the sheet names be appended to the data so that I can see which month the data comes from. 

ID	20220505	Sheet Name
First Name	George	Sheet Name
Last Name	Li	Sheet Name
Position	Alteryx Configurator	Sheet Name

[Qiu]

@45179902 
The data you provided are in the same worksheet or different one?

[45179902]

@Qiu They're in the same worksheet. Each worksheet has multiple records (persons) stacked together like this one. There're roughly 500 records in each sheet. Each workbook has multiple worksheets like this one. There're roughly 30 sheets corresponding to each day in a month. Each workbook represents a month of data.

[Qiu]

@45179902 
We can use a Dynamic Input tool since you state the the schema is the same for the all input.

[45179902]

@Qiu Hi Qiu, I just editted my question and reply. Your question reminded me that there should be a way to see where the appended data comes from. So I would also like to have the sheet names appended to the data as shown in the updated question. The goal is to see where each record comes from.

[Qiu]

@45179902 
Sure. 
There is an exact function in the Dynamic Input Tool.