Single Excel File, Multiple Tabs, Different Schema - But Keeping Sheet Names

[MojoRisen]

Hello, I have read through all of the excel import/batch macro threads and guides, however I have NOT seen a solution when importing multiple sheets from an excel file, all with possible different schema, but also being able to keep the sheet names in the output file.

My end goal is fairly simple - to stack all of the separate tabs from one single excel file so I can manipulate them, however, I also want to keep the sheet name associated with each tab so I can easily identify which sheet each row of data came from.

Thank you in advance for your help. 

[cpet13]

@MojoRisen I'm a little confused on the question. Are you just looking for a way to output the tab name?

[MojoRisen]

Importing multiple tabs with different schema from a single excel file in which stacks/combines each tab on top of each other but keeps the sheet names in a field/column. 

[cpet13]

I used the Batch input macro for a similar problem I was facing. This document gives an overview of it and the macro can be downloaded at the bottom - The Ultimate Input Data Flowchart (alteryx.com)

[cpet13]

You may need to go into the macro itself and edit it a little bit to get the tab/file name

[MojoRisen]

Thank you - my question was regarding a single excel file, multiple tabs. The link you posted is for multiple excel files, multiple tabs using a directory tool. 

[cpet13]

You could possibly just use a separate input tool for each tab.

[binuacs · Accepted solution]

@MojoRisen Sample attached

[MojoRisen]

Thank you @binuacs the regex formula worked. One follow-up question - the tabs do not stack directly on top of each other entirely - it seems some of the data overflows in additional columns. Is there a way to prevent that?

[binuacs · Accepted solution]

@MojoRisen The reason for this behaviour is that the macro is set to "auto configure by name" in the interface designer. This means that columns with the same name will be stacked together, while columns with different names will be separated. If you want all columns to be stacked regardless of their names, you should use the "Auto configure by position" option in the macro.