Error data summary for Incorrect data

[Kaish]

Hi all, I am working in a file and I want to get a solution for my output. I have file in which I have applied macro according to the schema, there are multiple files with different sheets, the macros help to consolidate the file according to matched schema. Now, I have a new request from my team 1) I want to know if all the files have the data or not, if the data is coming from all the files or not, I want to get the summary for how many files have the data and how many don't have the data. 2) In my input file I have few rows which have different data rather than the usual data, Ex- Date column has few data in which some random texts or numbers are there, So I need to push only the data which has correct value, if the column has incorrect data type then the file will push only the correct data and if there is incorrect data I will get a new summary of the incorrect data file. 

Can this be done ? I can provide examples if anyone can help me out. Thanks

[Raj]

@Kaish 
yes this can be done

first thing first , update your macro to pull the complete file path as well
so when you get the consolidated data you can have all the file paths
then you can summarize this an using join tool you can figure out the empty sheets and empty files

2 -you will be creating multiple filters to filter out the exceptional data form the regular data

in short this can be achieved

hope this helps you
mark done if solved.

[Kaish]

Thank you for your reply @Raj , could you explain, I am new to Alteryx and I am unable to perform the tasks properly.

[Raj]

@Kaish 
ok so first we will update the batch macro you are been using as of now.
just change from configuration of input tool
output file name as filed - "NO" to "Full path"

once this solved we will move on next step to comparison

[caltang]

If you’re following this: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

It will be useful to use the directory tool as an input to see how many files are there. Then you can compare with what @Raj suggested at the end to see if the count from the start ties to the end. You can also join based on the file path, which is a one to many relationship. From there you can see which file path was left behind -> that indicates no data if you’ve done your macro correctly that is.

[Raj]

@Kaish 
were you able to follow the first step?

[Kaish]

@Raj While doing "Full Path" from "NO". I am not getting the desired output, when it was "NO", I got proper results. What should I do now ? There are many files which has multiple sheets, so the data output has some issues while doing Full path. Any Solution ?

[Raj]

@Kaish 
will you be able to share the macro?

[Kaish]

These are the two macros, I cannot provide the data, you can add a random data, the first macro "Excel Tab Macro" reads the list of sheet names and the second macro reads the data mapped accordingly. Am I doing the correct step ? Or else I have to take some other step for my workflow ? My requirement is : I have multiple files 20-30 Excel files and in those excel files I have multiple tabs in most of the files, but there is a single tab which is common for all, the data coming in that tab has a same schema and structure, but the only problem is the name of the tabs and the position of the tabs is not equal, it needs to be mapped properly and the data has to be consolidated properly, next steps as I mentioned in my current blog. Can this be done ? Could you please check and let me know. Thanks

[jdminton]

@Kaish There were a couple of changes needed for your macros to work. The first was to change your input data tool (not provided) to "<List of Sheet Names>" in the Table or Query field in the configuration. Also, the first macro needs to pass the filename with the sheet name included in the file path. If you want to feed multiple Excel files through, you will want to add a directory tool to select the files and a dynamic input to default to the list of sheet names.