Adding & union of multiple sheets from an Excel file

[Pratyushkumar]

Hi,

I want to union multiple sheets from an Excel file. But using the dynamic input tool it is showing error. Is there a different way to input multiple sheets from an excel file and append them to create a large dataset?

[DataNath]

I'm guessing the error is due to your input files/sheets having different schema? I.e. varying names/numbers of columns/positions. If this is the case, you can tackle that with a batch macro:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

When it comes to finalising the macro, in the Interface Designer configuration (Ctrl+Alt+D), either of the bottom two options here will append the sheets, regardless of the schema (play around with both as which one you need to select will depend on the requirement - I'm unable to see your range of inputs so not sure which is most suitable):

[markcurry]

Hi @Pratyushkumar 

Do you want to share the error message you are getting?  Or you can also do this with a Batch macro, see this post...

Batch macro - Read multiple files and sheets - Alteryx Community

[Pratyushkumar]

The error is that its of different schemas.

[DataNath]

Will need to resort to a batch macro in that case @Pratyushkumar. From the chosen file, you'll need to import a list of the sheets within it that you'll be bringing together (see below), which you'll then feed into the upside down question mark to change the control parameter. The links above should set you on your way to building this!

[IraWatt]

I agree with @DataNath's solution. Though you can find what's causing the error using a field info tool and comparing the two files columns and data types if that useful @Pratyushkumar 

[Pratyushkumar]

I tried using the batch macro approach, and this is the snapshot of the error that is being shown.

I have attached a sample of the dataset. If a batch macro workflow can be designed based on this, it would be of great help.
The output has to be a combined dataset of the 4 sheets in the excel file.

Thanks

[IraWatt]

Hey @Pratyushkumar,

I've attached an example macro which works on that data:

If you have any issues opening it just click ok and it should still unzip the file then you can open the workflow. Also remember that you need to import the list of sheet names from the data input not the data itself:

Any questions or issues please ask :)
HTH!
Ira

[MichelleL]

@Pratyushkumar 

To help people out with the numerous ways to bring files/data in with either the Input tool or the Directory tool, I created a workflow with a variety of ways, including what you are trying to do.  Please check it out (attached) & let me know if it helps or if you have any questions :-)

If this works for you, please mark the solution as 'accepted'.  Thanks!