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
@Kaish what is the issue you are facing with the batch macro which reads all the files from the directory and produce the file name sheet names? attaching a sample workflow has two batch macro, the first one gives all the sheet names and file names, the second one combines all on one.
Hi @binuacs , the solution I want is to get all the sheets from all the files to be extracted and then I can filter out the sheet name instruction from the flow and then I will consolidate all the data from the remaining sheets which have similar data type and schema. Currently I have made a batch macro to read all the sheets but while running it reads only the sheet names from the macro input, my sheet names and file names are dynamic so I want Alteryx to load all the sheet names first, the attached flow that you've shared has the same issue where the sheet name mentioned in Macro input is loading the sheets from my directory excel files are not loading. Any way where I can get all the sheet names and file and then I can filter out the sheet name which is not required and then I will with the help of macro read all the data in those remaining sheets and consolidate the data accordingly.
@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.
@Kaish can you share the action tool settings of the macro?