Combine multiple excel files and rename tabs to the file name

[ZahinOsman]

Hi guys,

I'm trying to combine multiple excel files each having only 1 tab. When I combine them into a single output, I need the tabs to be renamed into their original excel file name

The output should look like this

Does anyone know how this can be done? Thanks in advance

[flying008]

Hi, @ZahinOsman 

Are the all .xlsx file have same fields and schema?

[ZahinOsman]

Hi,

They all have the same fields and schema.

What if they have different fields? Would the join still be possible (Join all tabs into one workbook but separate individual tabs)

[binuacs]

@ZahinOsman Sample workflow attached

[OTrieger]

@ZahinOsman 

What you will need to do is to get the file name from the input data tool, and use it as the sheet name in the path. Sheet name is the text after the 3 pips |||.

Just set them as a variable.

[ZahinOsman]

Hey,

Just to clarify, do I need to specify all the sheet names in the formula tab?

Sep_Corrections_EM Macro.xlsx

Sep_Corrections_FI Credit.xlsx

Sep_Corrections_FX.xlsx

Sep_Corrections_Rates.xlsx

If yes, lets say I want to run this workflow for the next month, so naturally the files will be renamed with Oct_ instead of Sep_, is there a way to avoid manually inputting the file name?

[ZahinOsman]

@binuacs 

[binuacs]

@ZahinOsman you need to only update the directory tool path

[binuacs · Accepted solution]

@ZahinOsman for the dynamic month selection i added a filter tool in the workflow

[ZahinOsman]

@binuacs owe you my life <3