You know when you receive this classic Excel file containing several tabs, all of them with the same data structure? Did you know how to use the tools at Alteryx?
All you need is the Input Data Tool and the Dynamic Input Tool (you will find it in the connectors section).
First step – Input tool
First of all, instead of the sheets. When you select this option, Alteryx will automatically generate a new field called “Sheet Names” to your file. The content, as you can imagine, is the name of each one of the tabs.
Second Step – Dynamic Input tool
Two input tools in the same workflow? Yes! The dynamic input tool acts slightly different from the “normal” input tool. First you should use the same file in the “normal” input tool.
Then you have two options to do it: One is to use the modifying SQL query function and replace a specific string (in this case, the name of all tabs).
The other is to configure the ‘read a list of data sources’ option, count Alteryx to use the field “Sheet Names” instead of the predetermined sheet.
Third Step – Run
And it’s done! You will see your final file. Easy peasy, is not it?
Bonus track – Turn it into an app
But what to do if my user generates this file several times a month and I’d like to turn this workflow into an app to facilitate the process?
With an app the user would be able to select the file on his/her computer, upload it through the gallery and send the data to the database, all of those steps on his/her own.
To make it real, only a few tweaks in the workflow are necessary. The secret here is to make the workflow “path agnostic”, I mean, it doesn’t matter the path and file name you have, the workflow will always pick it.
A small change and three new tools are enough to make it happens. The three new tools you will need are: a file browse tool, an action tool and a formula tool.
First Step – Add the file browse tool
It will automatically generate an action tool in your dashboard (if it doesn’t, drag and drop the action tool also, both can be found in the interface section).
In the file browse tool, make sure to specify the type of file the user should enter – as we’re talking about excel files, you should define it as “Excel Files (*xlsx)|*xlsx”
Second Step – Edit the Action Tool
Edit it to replace a specific string. Pay attention to not replace the full path – you should keep the part of the path getting the sheet names, the “|||<List of Sheet Names>” bit.
Third Step – Edit the “Filename” field
First of all, you will need to go back to your input tool and configure it to output file name as a field. It will generate a new column called “FileName” containing the full path. As you’re importing sheet names and not data from one specific sheet in the “normal” input tool, the path should end with “|||<List of Sheet Names>”.
All you need to do is to replace this bit by the real name of each one of the sheets. To do it, drag and drop a formula tool and use it to edit the “FileName” field.
As there is already a column with all sheet names, all you need to do is to add them to the full path with a calculation similar to the following: TrimRight([FileName],'<List of Sheet Names>’)+”‘”+[Sheet Names]+”$'”
Fourth Step: Tweak the Dynamic Input Tool
Since you have to use the “sheet names” field instead of using the “sheet names” field you can use the “FileName” field.
This way, even if I’m importing a file with a different name, the dynamic input tool will work. It’s now officially ‘path agnostic’.
Before running it to test, do not forget to enable your app to show the results. You can add a browser to the workflow and editing the interface design layout view as above.
All done? So you’re ready to go. You now have an app to open all Excel tabs at the same time using Alteryx!
Do you have any other method to grab all the files using Alteryx? Share it with us!