Select specific tabs from different excel files in different folder and append them

Question

Hi,

Does anyone know if it is possible to extract tabs from files and then combine them? The structure of the data looks something like below:

Folder -> Multiple Files -> Desired File -> Multiple tabs in it

....

Folder -> Multiple Files -> Desired File -> Multiple tabs in it

(Total of 27 Folders)

I managed to filtered out all of the individual desired files using the Directory Tool, but I am stuck on how to pull out the tabs I want. The files have different names but the tabs I want have the same schema and name.

Can anyone help me with how to solve this problem? Thank you!

RachelMa · Answer

Hi Angelos,

Thank you for your advice! So I built the following workflow after looking through the post by @mceleavey, but I am still having issue. The dynamic input tools does not seem to be able to find the sheet I want out of the files.

The 28 are all the files I need to pull tabs from. The 9 records are the number of rows in the sheet I chose as example in the dynamic input tool. I'm not sure why there are only 9 records. There should be somewhere around 9*28 records if it is working correctly.

The error message is following by the pull path without of sheet name.

This is the last part of the FullPath column in the dynamic input tool so I know the formula should have worked. It added the sheet name I want.

Configuration page of the dynamic input tool:

I apologize if my explanation is confusing. I am totally lost LOL

AngelosPachis · Answer

Hey @RachelMa ,

Do you have a list of all the different sheet names? One way to go about it is to use an append fields tool after your directory tool, to append all sheet names to each Full Path.

Then with a formula tool, you have to create the New Full Path Name, that contains the Sheet Name in it

Finally, with a dynamic input tool, you can define one of the files you have as an Input Data Source template (you can select any sheet in that file) and configure the tool so itr eads a list of data sources from the New Full Path you have created earlier.

If your files have the same schema as you said, the dynamic input tool will be able to read all data from them.

EDIT : Posts by @mceleavey in the following thread might also be of help, he describes how to do that if you only need to keep a single sheet from multiple files.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-Tool-Import-Multiple-Excel-Files-and-only-specific/td-p/428712

Hope that helps.

Angelos