Selecting tabs from an excel file using Dynamic input

[sergiogarciagt]

Hi everyone,

I have an excel file with approximate 30 tabs and I want to upload and combine just 14 tabs, approx. The tabs that I want to combine have the same format but the other ones don't. Could you please let me know if there is a way of using the Modify Query option in order to select just the tabs that I want?

Thanks,

Sergio

 

[bensilv]

Hi @sergiogarciagt,

Is there a rule you can specify on the sheet names? i.e. are the sheets you want to bring in predictable? If so, you could of course use the filter tool before the Dynamic Input.

If not, I would suggest using a batch macro for this, with the sheet name as the control parameter. With this method, you can bring in all the data from the sheet, then decide on whether to include or exclude it from the output (through a join, filter or otherwise).

See here for instructions on how to use macros for multiple inputs - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

[ConnorK · Accepted solution]

HI @sergiogarciagt ,

Please see my attached workflow. In the workflow you import the list of sheet names from the document and the full file path of the document. From here you can filter for the specific sheets that need to be brought in. 

I hope this helps!

[sergiogarciagt]

Thanks Connor, @ConnorK 

Thanks for your solution, it worked great. Do you know how can I add the name of the tabs in a new column? since those are account numbers I would like to be able to do a summary by account.

[ConnorK]

Hi @sergiogarciagt ,

Glad it worked! To include the sheet name you will want to click "Edit" within the Dynamic Input tool, and within the "Options" section of the pop up window you will want to change the option "Output File Name as Field' to "Full Path".

Once that has been selected you will need to parse out the sheet name from the full file path. For this portion please see the formula tool I added in the attached workflow.

Best,

[sergiogarciagt]

Thanks so much Connor, this worked great! @ConnorK