Looking for guidance using Dynamic Input

I am using a Directory module to import a list of filenames so that I can choose the one that is most recent and import the data with a Dynamic Input. My challenge is that the files I'm importing have multiple tabs with data. Is it possible to use some other logic to identify the tabs once I've selected the appropriate file so that the Dynamic Input can read the data from them? Has anyone else had a similar challenge?

My workflow begins with a Directory module pointed at a network drive and searching for *.xlsx files. Then, I'm using Formula to calculate Month, Day, and Year from the 8 characters at the end of the filename, sorting them descending, and sampling the first 1 row to identify the file I want. Normally, I would then use the Dynamic Input to get the data from the selected file. Can I use one Dynamic Input to identify the tabs and then another Dynamic Input to import the data? If so, would I use the same template file? I definitely need some guidance here.

Any and all help is appreciated.

Developer Tools

Input

Dynamic Processing

Accepted answers

apathetichell

This may get you close to where you need to go:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Run-Batch-Macro-for-Multiple-Sheets-per-Excel-File/td-p/910713

if not - there's a ton of discussion of how to build these kinds of macros... The first time you build one it can be rough - but it gets easier. the interface designer part above is key. really.

All comments

OTrieger

@EEnglish2

You will be able always use a batch macro to do just that.

This is Alteryx description how to set Dynamic Input tool
https://help.alteryx.com/current/en/designer/tools/developer/dynamic-input-tool.html#dynamic-input-tool

EEnglish2

Unfortunately, that doesn't help very much. Once I've identified the file I want, I've got a Dynamic Input where the template selected is producing a list of sheet names. I'm not sure how to configure the "Read a List of Data Sources" so that the output provides 3 possible file paths with each respective sheet name so that I can use another Dynamic Input to read each sheet. I currently have it configured for field FullPath with action Change Entire File Path, but I'm still getting an error stating "No sheet specified, you must specify a sheet". I'm stuck here.

apathetichell

As @OTrieger pointed out - do not use a dynamic input. It will not do what you want it to do if even one of the files/sheets schemas is different.

you will use a batch macro with an outer/inner designer.

batch macro 1 -> takes in the file from outerworkflow with "|||<List of Sheet Names>" in it.

in macro 1 - use a formala tool to replace <List of Sheet Names> with the sheet name.

in macro 2 - you take in the sheet name - you output to macro output.

in macro 1 - you take the macro outpt - and feed it into a macro output.

in both macros you go to interface designer and configure it to union by name. this is how you do what you want to do.

if you do not listen to this advice - I'd recommend looking at Python or R - because the way how I've described it is how you do it in Alteryx.

EEnglish2

@apathetichell Thank you for clarifying. Because there was a link to the Dynamic Input tool page, I thought that they were implying that I could find my answer there. I didn't understand the suggested solution. I've never used a Batch module before, so I will look into it.

apathetichell

This may get you close to where you need to go:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Run-Batch-Macro-for-Multiple-Sheets-per-Excel-File/td-p/910713

if not - there's a ton of discussion of how to build these kinds of macros... The first time you build one it can be rough - but it gets easier. the interface designer part above is key. really.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703