Pull in Excels with Different Sheet Names

I have a workflow where I use an Input Data tool where I exchange the file name for the wildcard (*). The sheets should all be named "Sheet" but occasionally one slips in that has a different name - "default", etc. I still want that data pulled it- the sheet name is not important for me. However, when this happens the whole workflow errors. Is there a way to either:

1.) Easily change all the files to have the same sheet name? Could probably write a macro to do this but I am macro illiterate...

2.) Have Alteryx not care what the sheet name is and also use a wildcard for it as well?

Thanks to everyone!!

Preparation

Accepted answers

Manoj_k

Hi @JGHOLLAND you need to open a new workflow and bring a directory tool and set your location in directory tool and then place this macro after it it will pull the data

All comments

binu_acs

@JGHOLLAND use batch macro

Multiple_Sheet_05102024.yxzp

Manoj_k

Hi @JGHOLLAND you can use a batch macro and then in directory you can select the entire folder. I have attached a batch macro which i use.

File Loader.yxmc

JGHOLLAND

@binu_acs I'm not too familiar with this. What parts of the workflow do I need to update? Obviously the directory location, but beyond that? Can you shed some more light on this?

JGHOLLAND

@Manoj_k Similar to the other suggestion, how do I use this to feed my normal workflow? I've pointed the input tool to my file and you can see it is pulling the sheet names. One is named differently - "Kyle", as oppposed to "Sheet". I'm not familiar with batch macro so unsure on how to use it to overcome my problem of my other workflow not wanting to read the file called "Kyle" since I told it all of the files would have sheet names called "Sheet".

image.png

Manoj_k

Hi @JGHOLLAND you need to open a new workflow and bring a directory tool and set your location in directory tool and then place this macro after it it will pull the data

binu_acs

@JGHOLLAND Update the directory path where your files located, the first macro extract all the file names and sheet names, the second macro combine all the sheets data into one

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167