community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Input multiple Excel sheets or tabs at once

Meteoroid

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

Capture.PNG

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.

 

 

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?

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

 

workflow.png

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.

Labels