How to read and append data from multiple xlsx files by sheets?

Question

Hi everyone,

I have a table containing file paths and sheet names. I want to read these xlsx files based on their sheet names and then append the data together into a single DataFrame.
I tried using macros with dynamic input, but it didn’t work as expected. Can anyone suggest a approach to achieve this task efficiently?

Any help or code examples would be greatly appreciated!

Thank you!

Gawa · Answer

@emmazyan

First approach is to use dynamic input tool. Filter out unnecessary sheets, and read data. Note that dynamic input tool does not allow different schema across input sheets, so in that case try to use batch macro.

For reference, I attach sample workflow.

How to read and append data from multiple xlsx files by sheets_gawa.yxzp

DanielG · Answer

@emmazyan - great timing because @madler_alteryx just updated this post earlier today.  Hope it helps.  :)

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range/ta-p/398220