Uploading an excel file with multiple tabs through a single input in analytic app

[PN2527]

I have an urgent requirement where in there is an excel file which has several tabs say 20 (different for each material). I need to allow user to upload that file through file browse input tool via analytic app. Further, the workflow needs to be published on the server.

Data from all the tabs need to loaded into the workflow through a single input. The main challenge is to avoid selection of each tab through the analytic app.

Let me know in case of any questions.

Thank you for the help.

[JosephSerpis]

Hi @PN2527 a potential solution is when the users uploads the file they select "List of Sheet Names" this will mean they have to only upload the file once. Now depending on the data in the excel if they are the same data e.g. columns name you can feed the filename plus sheet names into a Batch Macro which will consolidate the different sheets and data into one datastream in Alteryx. If the sheets are different e.g. different column names and data types then you will need to filter the different sheets and feed them into dynamic Inputs. I would need to know what the file looked like and then the requirements/logic after the file is brought in to give a more detailed answer or mock up a solution.

[PN2527]

Hi @JosephSerpis

Thanks for looking into this. 

By "List of Sheet Names", do you mean that if there are 50 sheets in the file then user would need to select the each sheet name from the pop up window?

I have attached the sample data for you to help me with a solution. Hope this helps. There are basically two different data formats used in the file (Material 1 and Material 3). There are more than 50 tabs like these in the actual file. 

Further, the inputted data needs to be consolidated material wise, quantity (bold value) and Total Cost (bold value) for each material.

Once again thanks for your support. Let me know if you need any further information to help me with the solution.

[PN2527]

@JosephSerpis 

Please find the attached updated file.

[JosephSerpis]

Hi @PN2527 when I say List of sheet names I mean the option Import Sheet Names so therefore the users do not need to enter each tab individually. As per the screenshoot where i'm testing an App in Alteryx designer and selecting that option.

 

[PN2527]

@JosephSerpis 

Thank you

Will it be possible for you to please share the workflow based on the sample data that I shared in the last post?

[JosephSerpis · Accepted solution]

Hi @PN2527 I've attached a potential solution and showcases the approach I spoke about in previous posts. I have commented the workflow as much as possible. Its an Alteryx package as their is batch macro included in my workflow which I built in 2020.3. If you have any issues with opening because its a newer version then this post shows how to resolve this.