Extracing Excel files with multiple sheets from directory

Question

Suppose you have been given a directory with a couple of files. You have been tasked to extract the data from the directory to be able to play around with it. Below you will see an example of an excel file with two sheets present. Some of the excel files only have one excel file present. What I mean by that is some excel files will have "US" sheet but not "APAC". Some will have "APAC" but not "US" and others will have both.

I am playing around with my workflow using Dynamic Input to extract the data from the directory but I am having some trouble. Can someone help.

Data.xlsx

erhepler · Answer

Hi,

I'm working on a similar situation. I've tried @grazitti_sapna 's solution, but it isn't working correctly for me.

I have a folder with multiple project report cards:

Each file has tabs for different team members, which vary from year to year:

When I use the workflow with the 'all the sheets' macro, it attaches the sheets from the sample load, not from a live load.

I did have to re-save the macro, since it was created using a newer version of Alteryx.

What am I doing wrong?

Thanks.

grazitti_sapna · Answer

Hi @jpatel3868 ,

Interesting scenario. You will need batch macro for this to get the full path for all sheets in all excel files and then use dynamic input to read those. Here is a workflow you can try. I tested it with the file you provided with 3 different formats. One having both as you shared and 2 having US and APAC tab in 2 different workbooks. Hope it helps.

Please do accept my solution if it helped you.

Thanks!!

New Workflow30.yxzp

Qiu · Answer

@jpatel3868 
Hope this would help.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Import-multiple-excel-file-different-schema-and-union/td-p/533496