Read all the excel file sheets from the directory.

Kaish

Hi All, I am having problem in reading all the sheet data in Alteryx. I have a directory where everyday many files are saved, the excel files are of different names with multiple sheets in each excel file, there is a sheet in each file which has similar schema data with same position of data I want to consolidate that data from all files, the sheet name of that sheet is also different in each excel file. Example - Each file has 2 sheets one with data (Random Name), other with the name instruction, I can filter out instruction sheet and then read all the data from those sheets which have been loaded in Alteryx. Ex file

File 1 name = Orders - sheet1 name = Inventory , sheet 2=Instructions

ID        Name        Grade

1          John            B

3          Myan           V

7          Rohan         U

File 2 name = Buy - sheet1 name = Value , sheet 2=Instructions

ID        Name        Grade

4          Tom            A

8          Man             F

2          Mohan         U

File 3 name = July - sheet1 name = Data , sheet 2=Instructions

ID        Name        Grade

23         Han            A

5           Karen          F

25           Sam          U

Here I want to consolidate all he data from the sheets (Orders, Buy, July) because the schema and position is similar, the sheet named instructions I will filter it out, because in each file we have only 2 sheets.

Can someone guide me to the right steps, I am trying to figure out the solution but my macros are not reading properly, the sheet macro is not reading efficiently, the macro sheet name function is reflecting the name of the sheet which I gave the file in input file of macro. Can someone guide me through the correct steps. Thanks    

OTrieger

@Kaish 
I guess if you have shared your WF, we could see what need to be fix, however as you did not only can assume that something is not fully set for the path.
I guess that you are importing the List of Sheet Names to get the list of sheets. Therefore you actually need 2 batch macro, one that is asking for the sheet names and then one that will open them.
The only thing that you will need to do is to replace the path with a Replace Formula
Replace([Field], "<List of Sheet Names>",[Sheet Name]+"$"]
Now if you will have a Macro Output you will have the consolidation of all the files and sheets. Just ensure to set the Macro to Join by name and not Automatically, as this can generate an error.