Excel file with different schema in each tab

[lmbell]

Several times a year, my team gets an excel file that has 6 tabs. Each tab has a different schema.  I preform a different analysis on each of the tabs, none of which is particularly complex, just tedious. Each tab has the same name and schema every time but the file name is different every time. I would like to be able to connect to the excel file only once and feed each of the tabs into it's respective workflow without needing to redo the connection for each tab. I feel like this should be possible since the tabs have the same name, but I can't figure out how to do it. Does anyone know how?

[WeiLi]

Hi Imbell,

Practically speaking, I do one of these things, each with increasing level of difficulty.

Option 1 - Input.xlsx

I always accept a file named "Input.xlsx" and my output is always "Output.xlsx" that way, i just have to rename the file in Explorer, as opposed to having to open and change my Alteryx workflows. This is the quickest and dirties of 3 methods.

Option 2 - Alteryx Apps

Save the workflow as an Alteryx App. And add a user interface to prompt the user to select the file. This is a bit more complicated than Option 1.

Option 3 - MySQL

In some cases, I upload the data to a MySQL database, hosted on AWS. These are usually those "monthly tasks" that I do at the start or end of each month. Because its a regular activity, the use of database will ensure data is consistent and easily retrievable by other ppl in the company. Because the data is on AWS, my Input Tool simply connects to the AWS internet address.

This is the more complicated than the other options, but it is the most reliable, especially dealing with larger datasets. It also prevent other ppl from doing stupid stuff, liking giving me PDF files.

[Raj]

yes this can be done

you just need to select the main file path and add the sheet into the path then use dynamic input tool to pull the data

As sheet names remain the same this will work for new file pulled as well.

[AndrewDMerrill]

Regardless of whether you build an Alteryx App, you can use the Input Tool to "Import only the list of sheet names", then include the Full Path, and use the Dynamic Input Tool to pull in the relevant data from those sheets.

[lmbell]

Thank you! I got this to work on one of the tabs. Now I've moved onto the another tab and can't get it to work. I have set up the Dynamic Input tool the same way but switched the Table or Query in the Options window of the Input Data Source Template link to the name of the new tab. When I run the workflow and Browse the data that is coming out of the new tab connection, it is the same as the one I already got to work. For reference, the one I got to work is the 5th tab in the file and the one I'm now struggling with is the 3rd tab (i.e. when I try to connect to the 3rd tab it is returning the data that's on the 5th tab) - so it's not that it's just connecting to the first tab in the file. Any idea what I'm doing wrong? I can send screenshots if needed; will just need to mock up a something clean that I can share publicly.

[AndrewDMerrill]

Are you using a batch macro to pull in the data via the Dynamic Input Tool? Batch macro would look something like this:

Just make sure that the FullPath input doesn't already have (or still have) the "|||<List of Sheet Names>" attached to it.

[AndrewDMerrill · Accepted solution]

Alternatively, you could just build the process in your main workflow (after thinking about it, if you do different processing for each workflow, a batch doesn't necessarily make any more sense to build out anyway):

Attached a Sample Workflow for your reference. Happy Solving!!!

[lmbell]

Thank you! That one worked perfectly! I appreciate your further thought as I was struggling with the first one, but that 2nd one worked great!