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

[Taxguy03]

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. 

[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

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

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

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

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

[AngelosPachis · Accepted solution]

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:

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 · Accepted solution]

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. 

[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

---