How to combine/pull multiple excel sheets with the same schema in one tab

Hi All,

We have an excel file with multiple excel sheets with the same schema. We want to pull all the tabs in one tab. We used the dynamic input tool and the output tool but it's not working. I have done it before but it's not working this time. Hope you can help.

Please see the sample file and the workflow.

Multiple excel tabs into one tab.yxzp

Output

Accepted answers

AngelosPachis

Thanks @Taxguy03 for providing a second version of the data.

Now the reason why the Dynamic Input tool is not working this time is because your field types change between the first tab (0711) and the next two (0811 and 0911). If you look at the metadata, the field types between 0711 and 0811 are different for the following field:

Screenshot 2020-11-10 180921.jpg

This is probably due to the fact that your data had a different format when typed and are misinterpreted by Alteryx. Same goes for tab 0911, whilst all the rest of your tabs are read normally.

Again I think the batch macro I attached above can overcome those issues, except if there is a certain reason why you only want to use a "Dynamic Input" tool.

Taxguy03

Hi AngelosPachis,

Thank you so much for your thoughtful and quick reply. I now see the issues with the "data type" which I didn't realize until you noted.

We will use the macro as you suggested instead of the "Dynamic Input Tool"

Have a great day.

All comments

Emil_Kos

Hi,

Did you try using in *.xlsx in the input file?

It will take all the files from the same directory

C:\Folder1\*.xlsx

AngelosPachis

Hi @Taxguy03 ,

The schema is not the same across your different tabs as some of your fields have different names. For example, when comparing the first to the second tab, 3 fields have different names

Screenshot 2020-11-10 171233.jpg

To overcome this, you can either rename your fields (temporary solution) or create a batch macro for that task (future proof solution).

Taxguy03

The file is in *.xlsx format. The file has many tabs. We want all the tabs to be in one tab when we output the file.

messi007

Hello,

Please see attached this post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Import-multiple-Excel-Sheets-with-sheet-name-as-column/td-p/532936

Hope this helps !

Regards,

AngelosPachis

So I have created a batch macro and configured it to output the tool based on the fields names (similar to what a Union tool can do).

Screenshot 2020-11-10 172019.jpg

It turns out that you have many fields across your different tabs having a different name, as shown below:

Screenshot 2020-11-10 172229.jpg

Instead, if you auto configure based on the fields position (more risky in case you have a new column somewhere in your data in the future) and remove the Tax Year column which is only present at tab "1011", I think you get what you are looking for.

Hope that helps, I've attached the workflow

Regards,

Angelos

Multiple_tabs_v2.yxzp

Taxguy03

I'm sorry that I overlooked that the headers were not the same. I have edited and made them all the same this time. However, I'm still not being able to output them as One file. Here is the new file and the workflow.

Hope you can help.

Multiple excel tabs into one tab.yxzp

blin

@AngelosPachis I download your solution but can't make the workflow portion work immediately due to not having enough knowledge to modify/troubleshoot Alteryx.

Would you please explain on top-level what Marco and workflow do?

My file doesn't have the inconsistent field name issue, do I still need Marco?

Thanks,

Betty

@AngelosPachis wrote:
So I have created a batch macro and configured it to output the tool based on the fields names (similar to what a Union tool can do).
It turns out that you have many fields across your different tabs having a different name, as shown below:
Instead, if you auto configure based on the fields position (more risky in case you have a new column somewhere in your data in the future) and remove the Tax Year column which is only present at tab "1011", I think you get what you are looking for.
Hope that helps, I've attached the workflow
Regards,
Angelos

hemant86

Hi @AngelosPachis This works fine for me. But I see the the number of line items I get in the output is more then what it is in the excel input. I also see some line items missing from the output. Any idea why?

Thanks in Advance!

DataMeister

Now it's been years but here goes: I love Alteryx's powerful features but it needs quite a bit of basic automation. Just look at the complicated mess of all these suggestions just to union some sheets! By contrast I can drop an Excel with ten tabs into Tableau and it automatically makes a Union without asking me a thing. Yes the columns are named the same from tab to tab--same schema. Tableau can't hold a candle to Alteryx in many other ways of course, but why not have Alteryx match the things it does right? For our convenience? Why do we have to still specify fussy little things? And not only that--if the schemas were a bit different, why can't Alteryx just prompt us for how we want to deal with little differences? C'mon, Man!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335