Combine multiple files (from common sheet name) into one sheet

[Jenina]

I want to consolidate multiple sheets into one sheet via putting all input files in a folder. Sheets will come from a common sheet name "GL Bal" saved in different excel files. But my source files have other tabs aside from "GL Bal" and this is where I'm getting an error. Would that be possible?

Thank you

[BS_THE_ANALYST · Accepted solution]

@Jenina yes this is possible! 

You'd use a Directory tool to point to the folder that contains all of these files (this gives the file paths for a batch macro to bring those files into the workflow)

The batch macro will union (vertically stack) each of the GL Bal tabs from each of those files into one long table (you'd just need to supply a filter to only take the GL Bal tabs)

I'd recommend reading through this: 
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Issues-using-a-batch-macro-to-combine-... 
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Batch-Macro-to-import-multiple-file-ha... 

You can find the batch macro in the first post, alongside a workflow. You can also find debugging in the second post (and also the first), should you encounter issues.

[Jenina]

 Hello, thank you for your prompt response. I'm still lost on how to connect the directory to the batch macro and will only capture GL Bal tab. I attached sample files assuming they're in the same folder. Do we have sample workflow? Thank you! 

[Robin_McIntosh · Accepted solution]

@Jenina - As long as all of the files will be in the same folder (file path) then another way to do this is by using the Input tool, bringing in one of your files, and then modifying the configuration.

Step 1 - Bring in one of your files into the Input tool.

Step 2 - Modify the configuration as highlighted below.  Simply change the file name only to an asterisk and keep everything else the same.  And if you want to pull in the file name on each record to show where it came from, change option 5.  This will pull in every Excel file within the given file path (folder) that has a tab as you specify (i.e. Common_Tab)

Hope this helps.

[Jenina]

After some trial and error I finally got it. All suggestions are helpful. Thanks all!