Catch and log which Excel sheets were not found

Hello everyone,

I'm relatively new to Alteryx. I've made a macro that is reading a list of Excel files and two of their sheet names (called "Main" and "New Trades").

The macro works well, however, sometimes a few files are missing one of the two sheets. Thus, it outputs errors stating "Record #1: Tool #2: `New Trades$` does not match a sheet or named range in [FILE]. blabla...."

What I would like to do is to "flag" which files are missing which sheets (kind of logging them into a list). Is there any tool or any way to do this?

Also, I don't want my workflow to stop working because it couldn't find a certain sheet in one file (what would be great is to just output one warning or something)

Many thanks

Batch Macro

Accepted answers

neilgallen

Given that you're working with excel files, you have an opportunity here because Alteryx can read a list of sheet names from an excel file.

If you use a directory read tool you can then read the available sheets from each file, and then only input those sheets into a dynamic input tool, or whatever you'd like.

if you're assuming that each file should have at least those two sheets then you could also flag those files that do not, as well.

The attached workflow should get you started with the rough idea.

Read sheet names dynamically.yxzp

All comments

BrandonB

Could you isolate the file path/sheet names from what you are reading in and join them to a list of sheet names that you expect? You could get your list of files and append on the sheet names you expect, then join that against the actual list. You could then see the L or R from the join tool for files that do not have the expected sheets in them.

okka

I guess this is what my macro is doing already (attached is my macro)?

My inputs are the filename and the sheetnames.

Capture.PNG

BrandonB

Correct, but I am referring to outside of the macro. Take a look at the attached workflow:

sheets missing.png

Show sheets that do not exist in files.yxmd

okka

Thank you for your answer. However, here you are assuming that I already have a list of file names and their respective sheet names.

This is not what I have now. I just have a list of file names that I'm reading from (and I'm assuming that the two sheet names are always in each one).

neilgallen

Given that you're working with excel files, you have an opportunity here because Alteryx can read a list of sheet names from an excel file.

If you use a directory read tool you can then read the available sheets from each file, and then only input those sheets into a dynamic input tool, or whatever you'd like.

if you're assuming that each file should have at least those two sheets then you could also flag those files that do not, as well.

The attached workflow should get you started with the rough idea.

Read sheet names dynamically.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703