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

Question

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 Answer

@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-multiple-files-with/m-p/886851 
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Batch-Macro-to-import-multiple-file-having-different-schema/td-p/684748

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.

Robin_McIntosh · Accepted Answer

@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.