topic Combine multiple excel files and rename tabs to the file name in Alteryx Designer Desktop Discussions

Combine multiple excel files and rename tabs to the file name

ZahinOsman — Sat, 09 Nov 2024 03:27:01 GMT

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

Re: Combine multiple excel files and rename tabs to the file name

flying008 — Sat, 09 Nov 2024 03:43:49 GMT

Hi, @ZahinOsman

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

Re: Combine multiple excel files and rename tabs to the file name

ZahinOsman — Sat, 09 Nov 2024 12:05:25 GMT

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)

Re: Combine multiple excel files and rename tabs to the file name

binu_acs — Sat, 09 Nov 2024 22:22:55 GMT

@ZahinOsman Sample workflow attached

Re: Combine multiple excel files and rename tabs to the file name

OTrieger — Sun, 10 Nov 2024 10:50:27 GMT

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

Re: Combine multiple excel files and rename tabs to the file name

ZahinOsman — Mon, 11 Nov 2024 07:38:24 GMT

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?

Re: Combine multiple excel files and rename tabs to the file name

ZahinOsman — Tue, 12 Nov 2024 06:58:01 GMT

@binu_acs

Re: Combine multiple excel files and rename tabs to the file name

binu_acs — Tue, 12 Nov 2024 12:04:52 GMT

@ZahinOsman you need to only update the directory tool path

Re: Combine multiple excel files and rename tabs to the file name

binu_acs — Tue, 12 Nov 2024 12:09:47 GMT

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

Re: Combine multiple excel files and rename tabs to the file name

ZahinOsman — Wed, 13 Nov 2024 06:27:02 GMT

@binu_acs owe you my life <3