Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Input multiple Excel sheets or tabs at once

6 - Meteoroid



I have created an workflow that updates a table with data from an input file. The problem is that I need to update the table with roughly 150 inputs files. Unfortunately duplicating the work flow 150 times does not work as that makes the workflow so large it crashes the program. I am currently trying to utilize a file browse, action, directory and dynamic input flow to route the multiple files through the work flow but am not having much success. Any ideas on how to work around this issue would be useful. I have pictured my current input flow (not working below).


8 - Asteroid

Per the title of the question and your explanation, I'm going to assume all the data is located in one Excel file with 150 or so sheets (aka tabs).  Assuming all sheets are the same format, you'll use an Input Tool to get all of the sheet names in the file.  Once you select an Excel file,  you'll be prompted to "Select a sheet" or "Import only the list of sheet names".  Select the later.

input config 1.png


Then feed those sheet names to a Dynamic Input tool.  You'll configure the Dynamic Input to look at the same Excel file.  In the "Input Data Source Template" location of the Dynamic input, you'll select the same file and one of the sheets.  Then select the "Sheet Names" field from the previous Input Tool and set the action to "Change File/Table Name".


dynamic input.png

If needed, you can add interface tool to turn this into an app.  See the attached for a working example.



6 - Meteoroid

Thank you Steve for your response. I apologize, as I have misled you as the tabs are each in their own separate excel file. Do you think this solution would work?

8 - Asteroid

The solution I presented previously was really meant to support one file with many sheets.  I've created a new solution that can support multiple files with multiple sheets as long as those sheets have the same layout (schema).  I've also added interface tools to allow the user to change the directory where the files are located and the name pattern of the file.  Just to take that a step further, I also added a interface tool that will take text input and filter the sheet names that contain that input.  This would be helpful if you had multiple sheets in a file, but only wanted to read a subset of those sheets (an example would be a file with three tabs, "settings", "data1", and "data2".  If you input "data" in this input, you'd only read the second two sheets).



I would like for Alteryx to correct the Dynamic Input tool and have it maintain the "List of Sheets" setting, but this was fairly easy to work around.  This should work for just about any situation you can throw at it.


Workflow and sample files attached.

5 - Atom



Thank you for your solution, is there a way such that when data is read from each tab, the sheet name is appended at another column? 


Thank you!

8 - Asteroid

I don't have a license for Alteryx at the moment, so I'm not able to test this solution.  That being said, I do believe the attached workflow will do what you need.  In the previous version, the full path of the input file name should already be in the output.  When reading from an Excel file, that full path should look something like: "C:\Temp\your_excel_file.xlsx|data".  In this example, "data" is the name of the sheet.  With this, we apply the following formula to dynamically parse off the sheet name from the full file path.


Tab Name = Substring([FileName], FindString([FileName], "|"), Length([FileName]) - FindString([FileName], "|"))


The formula above was added in the new formula tool shown in the red box below.  

parse tab.png



Hopefully this gives you what you need.