Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Taxguy03
9 - Comet

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. 

12 REPLIES 12
Emil_Kos
17 - Castor
17 - Castor

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
16 - Nebula

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
9 - Comet

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
15 - Aurora
15 - Aurora

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
16 - Nebula

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

Taxguy03
9 - Comet

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
16 - Nebula

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
9 - Comet

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
6 - Meteoroid

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

 

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


Labels