Hello,
I have the following ask at hand and would like some help tackling it
Description
I really appreciate your help
Thank you
Greetings,
- I would reformat these sheets so you can actually import them correctly (VBA) or you could use alteryx. Then strip out what you need on each sheet (transpose/summarize/append records) into a table.
- i would create a folder structure for each department and have the output tools respectively put the files there
- You can extract data from multiple sheets by loading the same sheet type, just select the different tabs.
- I would create some sort of wildcard input that will serve your needs to pull all of this in. Or i would house the data on a server or in its own alteryx database and pull everything from there
Hope this helps,
DV
Hi @Noorakroush
Start with a Directory tool to read in all the file names in the entire Assessment tree. use a "*.xlsx" wild card and start at the highest level with Include Subdirectories checked. Feed this into a Dynamic Input configured to return List of Sheets. This will give you a list of all the sheets in all the workbooks.
The way to proceed next depends on your version of Alteryx. If it's 2020.3 or newer, you have the option to select a range of cells on Input. Connect the output of the Directory tool to a set of Dynamic Input tools arranged in parallel to input each of the distinct range tables in your sheets. i.e. the first one inputs Name and Manager
The second Input tool would read the Roles range
Make sure to make the range wide enough to include the necessary row and column headers
After you perform whatever data gymnastics on each of the sections, use the FullPath from the Directory tool as a key to allow you to join all the range sections.
if your version of Alteryx is less than 2020.3, you'll have to read each of the sheets in their entirety and parse out the information that you need. This generally involves breaking down the file sections using a multi row tools to mark out the various parts Check out this post and this one for examples of how to do this
Good luck
Dan
Hi Dan,
Thank you for your response.
I have an earlier version of Alteryx. Im not sure I understand the sources you refer to on breaking down the file sections using a multi row tools to mark out the various parts. These also apply to excel files, not just text?