It’s day 2 of the joyous Dashboard Week at the Data School and more fun with Alteryx and Tableau. Today we looked at importing multiple worksheets from an Excel file, merging the data together and then building a dashboard from the data, which was all related to migration statistics.
Initially I tried experimenting with the Crew Macros for importing multiple Excel files (or worksheets) and combining the output into a native Alteryx YXDB file as this is much faster to work with in Alteryx. However, in the end this didn’t pan out as expected, so later on I changed tack and rebuilt the workflow from scratch using a more standard method. Below is this finished workflow:
The big learning for the day (apart from not spending too much time going down a path that isn’t working as expected) was about the Dynamic Input tool. This is great for bringing data from multiple files or worksheets in the same file. It can be used in conjunction with a Text Input to hold the worksheet names as I did here:
The Text Input simply has a list of the worksheets that I wanted to import from the Excel file:
The magic happens in the Dynamic Input tool itself. Below is the main Configuration screen:
The Data Source Template contains a link to the Excel file that has the worksheets to import (see the next screenshot for more details). The Field parameter that has the highlighted blue text, “Field1” refers to the only header in the Text Input tool shown previously.
The Action is set to “Change File/Table Name” – what this means the Excel worksheet that I have selected when I browsed to the Excel sheet initially will be updated with each name in the list in the Text Input until all data has been imported.
Below is the dialog box that shows when you browse to your Excel (or other) file initially.
In parameter ‘3’ I selected ‘Table 14$’ – which is the worksheet named “Table 14”. A $ sign is shown at the end of each worksheet name.
In parameter ‘5’ I selected to output the Full Path, which includes the worksheet name, as I could then use that to distinguish between the data (which was different years per worksheet) later in the workflow.
Finally I selected to start the data input on row 17 as the prior rows contained headers and other information, which is dealt with a different part of the workflow.
I hope this has been useful for learning more about this tool. As always more information can be found on the Alteryx help pages – here is the help page for this tool: https://help.alteryx.com/11.0/index.htm#DynamicInput.htm