Input multiple Excel sheets or tabs at once

[BrooksR189]

Hello,

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).

[steven-barsalou]

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.

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".

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

[BrooksR189]

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?

[steven-barsalou]

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.

[Veralps]

Hi, 

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!

[steven-barsalou]

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.  

Hopefully this gives you what you need.