Combining Excel Multiple Spreadsheets into One AND show in a separate column the source

[Katha]

Hi all,

I need to combine multiple Excel Tabs (with different schema) from one Excel File into one tab AND I need to know where the data came from, so which row came from which tab.

Reason is, some of the tabs contains a year and I need the year to make further calculations.

Example: Sheet one "2020 Control Statement", Sheet two "2019 Control Statement", etc. and although the data are similar, there are sometimes a different number of columns (therefore different scheme).

In my imagination I should do something like the Union and put the data from sheet "2019 Control Statement" under the data from sheet "2020 Control Statement" and put also an additional column "Sheet Name" with the respective tab names.

Thank you,

Katha

[DannyS · Accepted solution]

Hi @Katha ,

I would highly recommend checking out this article: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Read-in-Multiple-Excel-Files-with-M... to get to your desired outcome. There are guided instructions and a sample workflow to help you out!

And then one thing to make sure to do to get the file names is to select the dropdown in option 5 of your Input data tool which allows you to "Output File Name as Field".

Hope this helps!

Best,

Danny

[Katha]

Hi @DannyS,

Thanks a lot for looking into it.

One issue is fixed. I could manage to bring all my sheets together into one. 🙂

But I still facing the problem that I don't know where the data (lines) come from, so from which tab.

Do you have any further ideas or proposals how I can fix this?

Thanks,

Katha

[Katha]

Hi DennyS,

I just tried it again but this time I did the change you suggested below "Output File Name as Field2 in the Macro it self and that works exactly like I wanted.

Thanks a lot! 😊

Best,

Katha