Excel file with different schema in each tab

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?

Input

Accepted answers

CoG

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

_Dynamic Input Configuration.png

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

Sample Workflow.yxzp

All comments

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.

CoG

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.

CoG

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.

CoG

_Dynamic Input Configuration.png

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

Sample Workflow.yxzp

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!

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335