Input Multiple Sheets with Different Formats

Question

Hello Alteryx Community,

I have multiple Excel files, each of which contain multiple sheets, and I am looking to import all of them into Alteryx hoping to minimize the number of times I need to use the File Input tool. Below is an illustration of the problem:

Sheet ASheet BSheet CSheet DFile 1√√ √File 2√ √ File 3√  √

Each sheet would have a different format (so Sheet A is different from Sheet B) , but across files, the sheet would be the same (so Sheet A in File 1 would be the same as Sheet A from File 2).

There is one more challenge to the above scenario. I receive such a file on a periodic basis from a system. And depending on the period, a file may or may not contain the certain sheets. So for the next period, the above matrix could look completely different. Unfortunately, connecting Alteryx directly to the system isn't possible.

Just wanted to check if there is a way by which I can read the names of the sheets from each file, and depending on that, feed that into a different stream for data cleansing. The only alternative I can think of for now is to use multiple file inputs (so in the above case, have 7 file inputs), but I don't quite like that approach as the next time I try to run the workflow, certain file inputs would throw errors as that particular sheet wouldn't be there the next time around.

Wanted to know if anyone had a solution for this.

DavidP · Accepted Answer

You can use the Directory, Formula and Dynamic Input tools to find the Sheet Names for each file as shown below. I'd suggest loading the sheets with a simple batch macro.

If you want each sheet in a different stream, you can use filter tools for each stream, as shown.

You could also load all the data into a single data set if you don't use the filter too and then you only need to run the macro once, but then you'll have to split the data into individual streams afterwards and you'll end up with null columns in each data set.

huynv96 · Accepted Answer

I used 2 batch macro: One to read sheet name in each excel file, another one to read by sheet name.

hope this helps you