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 |
