Macro to Import Multiple Excel Files and Append Multiple Tabs from Multiple Files?

Question

Hi Alteryx Community,

I am currently trying to create a workflow that can bring in 20+ Excel files and from each Excel file, extract two tabs, and then have the 40+ tabs all appended into one file. After everything has been appended to one tab, I will be performing some additional filtering and formulas to clean the data up.

I have spent the last day or so doing research on what batch import macros to create for the first step in the process and I am having a lot of trouble. I've been able to create a macro that is pulling in multiple tabs from separate files but afterwards, how do I get all of these tabs appended into one large file? Do I need an additional macro that involves the dynamic input function?

image.png

MichelleL · Answer

Hi @kfeng

To help people out with the numerous ways to bring files/data in with either the Input tool or the Directory tool, I created a workflow with a variety of ways, including what you are trying to do.  Please check it out (attached, see container labeled #7) & let me know if it helps or if you have any questions :-)

No additional macro needed.  Make sure in your macro, in the Interface Designer (if the Interface Designer window is not open, open it from the View menu), that you select an appropriate output mode.  If all the columns in the various Excel spreadsheets have the same name, 'auto configure by name' is a good option.  Then the files will stack on top of each other (like a Union).

Directory+DynamicInput-Relative Paths-INSPIRE2022.yxzp

Qiu · Answer

@kfeng 
I believe this is the one you are looking for.

https://community.alteryx.com/t5/Public-Community-Gallery/Import-Multiple-Schema-Excel-Files/ta-p/919226