Excel file with multiple sheets - Alteryx connection

[Narresh4ut]

On monthly i receive excel files which has multiple sheets. I would like to connect through Alteryx. could you please provide sample workflow

i have shared sample excel file as well.

[Pilsner · Accepted solution]

Hello @Narresh4ut 

To load in all the data from all your excel sheets, I would recommend using a batch macro. 

Firstly, load in your sheet names using the data input tool and append these names to the end of your file path with a formula:

Next, select your new field as the control parameter within the batch macro configuration window.

Inside the batch macro itself, you will need to use an action tool to allow the control parameter to overwrite the data input full path:

I believe this should then achieve the result your after. I have created a sample workflow and attached it below, please let me know if you have any questions.

Regards - Pilsner

[aatalai]

@Narresh4ut if it is the same fomrat you could use the directory tool and the dyanmic input 

[Narresh4ut]

@Pilsner @aatalai Thank you both for the help. It worked

[Pilsner]

Always good to hear! Thanks @Narresh4ut 

[Narresh4ut]

Hi @Pilsner currently i am able to read only one file in workflow but moving forward i will get files on monthly which the current workflow is not picking of those file. I have saved copy of test file in same folder but its not picking up. Can you provide some information here

[Pilsner · Accepted solution]

Hello @Narresh4ut ,

If you are trying to read in all files from a specific folder you should be able to do so as follows:

In the data input tool of the main workflow, change the file path so that it ends with the FolderName/*.xlsx. 

In the screenshoot below, it tells the data input tool to look in the "_externals folder", then folder "1". As I want all excel files from folder "1", I have then used the "*" followed by ".xlsx".

I hope this adjustment makes sence, feel free to ask any questions.

Regards - Pilsner

[Narresh4ut]

 @Pilsner it worked now. Thank you for your help.

[Pilsner]

Brilliant! Thank you @Narresh4ut