Dynamic Input Tool not working

[Hamza199]

Hi everyone!

I am having some issues with the dynamic input tool.

I have run a input directory tool which is bringing in 6 files. Each file contain 5 worksheets/tabs all with the same naming i.e. "Tab 1" in file 1 has the same format as "Tab 1" in file 2 etc. In essence, my aim is to find an efficient way to Union the data for each tab across all 5 files i.e. All of "Tab1" is in one dataset across all 5 files e.t.c.

I have inputted the list of sheet names and added this to the fullpath using the formula tool i.e. The directory now contains each file and the tabs in this file.

I have then used a filter tool to separate the directory per sheet i.e. The first filter filters all "Tab 1" across the files, the second filter filters all "tab 2" etc. Then, I have attempted to use a dynamic input tool as a way to union this data. I have selected a file at random with the correct tab as a sample. Then, I have selected the field which contains the full path including the sheet name. I have then changed the action to "Change entire file path". However, when running, it only outputs the data from one file rather than across all 5.

I kindly request for someone to assist in my issue.

Thanks! 

Hamza

[TheOC]

hi @Hamza199 

Are you able to attach your workflow so far? No worries if you cant send the data - i can create some dummy excel files to test against.

Cheers,
TheOC

[atcodedog05]

Hi @Hamza199 

Please check whether are you getting schema warning issues this would be dropping the sheets and not reading. Follow the below guide if first 2 methods doesn't work the 3rd method should definitely work.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

If it still doesn't work please share your result log snapshots.

Hope this helps : )

[Hamza199]

Hi,

Firstly, thank you both for responding so quickly!

Upon your feedback, I checked the warning and I noticed that the issue is occurring due to some files having an extra field or two i.e. the schema is not exactly identical.

Is there not a way to tell Alteryx to simply input the fields that do match similar to the union tool which matches all fields but name and any additional fields are added to the end of data?

Regards,

Hamza 

[TheOC]

hey @Hamza199 

a potential solution to this would be a batch macro. You could loop through the files and bring them in one at a time (with a very similar workflow to what you already have, just having a dynamic input in a batch macro, and looping through each file). This would allow for the functionality you mention. 

Here is a getting started guide:
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

Hope this helps,
TheOC

[atcodedog05]

Hi @Hamza199 

Try this workflow it should work. Make sure to mention the sheet name like below.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

Hope this helps : )

[TheOC]

hey @Hamza199 , @atcodedog05 Has linked a batch macro method, with attached solution. I would still recommend my article to understand batch macros for if you need to make any adjustments, but @atcodedog05 provides a direct solution.

Cheers,
TheOC

[Hamza199]

Hi,

Thank you both for your responses.

I shall look into the batch macro idea and if it works, will close this and accept your solutions.

Thanks!
Hamza

[atcodedog05]

Hi @Hamza199 

I have updated the post above. The newly mentioned method should work better in this scenario.

Make sure to mention the sheet name like below.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

Hope this helps : )