Preventing an error when an input tab does not exist

We have an Alteryx built off of one Excel with 42 different tabs. These tabs are all a bit different so we cannot use a Dynamic Input. The problem we are having is that if one of the tabs is missing in this Excel, the entire Excel crashes because it cannot find the specified file path. Is there a way to do some sort of an "if" statement on the input that lets that Alteryx know to continue with the other functions on the page and ignore that function if the tab does not exist?

Thank you for your help in advance!

Input

Accepted answers

NikyN

Hello @kkaplan1,

I would still highly recommend to use Dynamic Tool (but the attached macro), so you will be able to dynamically identified existing sheets and than load them into flow.

In case that some sheets have different columns/or should be passing different tool (and should not be loaded as single data stream), please use filter to choose needed sheets.

My recommendation is:

1) Directory Tool -- to load all files (or single file) that you want to load into flow

2) Formula Tool -- to add specific formula to list all existing sheets

3) Macro (attached) -- to read all sheets in specific file

4) Formula Tool -- to add to full path also sheet name from previous step

5) Filter Tool -- optional, in case that you want to treat every sheet differently

5) Macro (attached) -- to load data into flow

***Browse tool required for first run.

Best Luck!

Niky

Dynamic Excel Input (different schema).yxmc

Dynamic Input.yxmd

All comments

apathetichell

do you take in a list of sheet names from this file? you should be able to see if one of the sheets isn't in the list of sheet name and prevent running that part of the workflow.

kkaplan1

I do take a list of sheet names. Each input is already set up as one of the sheet names but we want to be able to run it if only 2 of the sheets are actually present without having to change the inputs every time we go to run the Alteryx. How do we go about preventing it from running that part of the workflow? Thank you!

NikyN

Hello @kkaplan1,

I would still highly recommend to use Dynamic Tool (but the attached macro), so you will be able to dynamically identified existing sheets and than load them into flow.

In case that some sheets have different columns/or should be passing different tool (and should not be loaded as single data stream), please use filter to choose needed sheets.

My recommendation is:

1) Directory Tool -- to load all files (or single file) that you want to load into flow

2) Formula Tool -- to add specific formula to list all existing sheets

3) Macro (attached) -- to read all sheets in specific file

4) Formula Tool -- to add to full path also sheet name from previous step

5) Filter Tool -- optional, in case that you want to treat every sheet differently

5) Macro (attached) -- to load data into flow

***Browse tool required for first run.

Best Luck!

Niky

Dynamic Excel Input (different schema).yxmc

Dynamic Input.yxmd

MichelleL

@kkaplan1 Please see attached for a variety of options to bring multiple Excel files together into 1. The solution is dependent on the set up of the Excel files (same schema, different schema, same sheet names, different sheet names, sheet sequence number, etc). You mention "These tabs are all a bit different so we cannot use a Dynamic Input". Why? How are they different and how are you bringing them together? If you could send a pic of the workflow, that may help me understand.

Hopefully one of the attached samples will help you out. Let me know if one of these works for you!

Directory+DynamicInput-Relative Paths.yxzp

Peachyco

You might consider the Tool Container. It has an Enable/Disable switch on it, so if you put something like a "subroutine" in it, you can effectively prevent that portion of the workflow from running altogether based on some rules that you set.

Maybe you can follow from this particular discussion to see how you might be able to pull this off based on conditions.

kkaplan1

Thank you very much for this!

DylanDowrick

In addition to the answers provided, there is a formula:

IF FileExists([FullPath])
THEN [FullPath]
ELSE ""
ENDIF

which can be directly before the input anchor of a macro/dynamic input.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703