Tool Mastery | Dynamic Input

[MattD]

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Dynamic Input Tool on our way to mastering the Alteryx Designer:

For most tools that already have “dynamic” in the name, it would be redundant to call them one of the most dynamic tools in the Designer. That’s not the case for Dynamic Input. With basic configuration, the Dynamic Input Toolallows you to specify a template (this can be a file or database table) and input any number of tables that match that template format (shape/schema) by reading in a list of other sources or modifying SQL queries. This is especially useful for periodic data sets, but the use of the tool goes far beyond its basic configuration. To aid in your data blending, we’ve gone ahead and cataloged a handful of uses that make the Dynamic Input Tool so versatile:

• Reading in a list of files by their file paths (as opposed to opening files in the same directory with a wildcard in the Input Data Tool). To do so, you need only to specify a field of file locations as your field in “Read a List of Data Sources” and “Change Entire File Path” as your action. The Directory Toolis often used to listfiles:

• Modifying SQL queries to replace strings, update where clauses, apply spatial filters, and edit stored procedures for your input (see attached).

• Accessing multiple worksheets from the same Excel workbook (see attached).

• Batching into the designer multiple sheets from multiple excel files – with different formats!

Since modifying SQL queries and accessing multiple worksheets inside the same Excel file are the least documented approaches on our Community, we’ve detailed approaches to the two in our attached v10.5 workflow “Dynamic Input.yxzp.” For the sake of transferability (we won’t have access to the same database connections), the SQL query used as an example is simply selecting all the records from the same Excel file as the first example – you can view the original query by looking in the template file’s SQL Editor view by selecting “Table or Query:”

Looking for more information on how to use the Dynamic Input Tool? Try our Community Live Training video Dynamic Inputs.If you’d like to learn more about inputting data in general, be sure to check out our Ultimate Input Data Flowchart as well!

By now, you should have expert-level proficiency with the Dynamic Input Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

Stay tuned with our latest posts every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

Additional Information

Click on the corresponding language link below to access this article in another language -

• Portuguese
• Spanish
• French
• German
• Japanese

[attardc1]

Hi Matt,

Is it possible to use a Dynamic Input and replicate the "first row contains data" option as seen in a regular input tool?

My data requires a bit of standardization so coming up with a work around / any suggestions would be greatly appreciated!

Thanks for your time!

Best,

Chris

[DanM]

@attardc1

You won't be able to accomplish this with the Dynamic Input tool, but you may be able to accomplish this with the simple macro in the post below. This simple macro allows you to use the Input tool just like the Dynamic Input tool, but use the features of the Input tool

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-...

DanM

[cgaus]

Hi Matt and Dan,

Is there a way to ignore data types when using Dynamic Input? I have a file with 50 tabs, each with the exact 6 columns. When I use a select tool, I can see that a column is being read in as Double on some tabs, while the same column is being read in as V_String on other tabs. I want to ignore whatever Alteryx thinks the data type should be, input the data, then use a select tool to select my data type for each column. Is something like that possible? 

Thanks,

Christopher

P.S - I used a batch macro, but I think that's a terrible option. This should be a standard option within the Dynamic Input tool. 

[DanM]

@cgaus,

The Dynamic Tool at this time needs a template which Alteryx reads the schema from that template and brings in the appropriate schema. The reason why this tool cannot ignore the schema is due to problems that could arise where the schema could be so different and that data itself so different that it would cause a lot more work on the users behalf to have to parse that data out. Plus, if we ignored the schema, this tool would take a long time to pull in your data to sift through that schema. This is the reason why this feature in not in that tool.

You are correct in using the batch macro, where using the Interface Designer allows you to designate to have the macro ignore the schema and use either the position or name of the field. This allows for efficient use of memory and allows you to adjust how you want the Input tool to read the data.

If you are interested in seeing these features in the current tools, please add your idea to the Designer Ideas section.

Regards,

DanM

[Karthick461]

Hello,

 Do we have any option to read .txt files.. I have around 1000 files to read from common share..

[lepome]

@Karthick461 

I strongly recommend you use the guidance in this article:  The Ultimate Input Data Flowchart  It sidesteps a lot of the problems that people encounter when they use a Dynamic Input tool inappropriately.