Import multiple excels files with different schemas and sheet names

[PeggyGithinji]

Hi, I'm working working on building a macro to import multiple different excel files with different sheet names from a directory and I keep getting an error that I have not managed to debug File selection does not contain data; select a valid data file. See snippet of what I have and the error I get. 

[binuacs]

@PeggyGithinji Update your directory tool to pull only the .xlsx files and try again

[PeggyGithinji]

I still get the error even when I specify the file extension. 

[binuacs]

@PeggyGithinji Can you try the attached workflow which does the same use cases as yours

[PeggyGithinji]

Thanks @binuacs. I have followed the workflow shared and its running successfully however I have noted the output is only from one file (the first one in the directory) and looped through it couple of times rather than compiling the data from all the files in the directory folder. 

[binuacs]

@PeggyGithinji Can you chek for any warning/error messages in the result window of Alteryx Designer, there might be a chance to skip the records with some error or warning

[PeggyGithinji]

Hi @binuacs thanks for your suggestions. The workflow now works correctly bringing in all the data from the files in the mapped directory. There's one small issue, when I map to include data from subdirectories which I also need to bring in, I get the error below. Not sure what file is causing this issue as I have tried importing in smaller subsets and it works fine. Is there a way to tell what data file is causing the error from the message (record #20: tool #8) or a way to ignore this error as a warning as it i think it may be a bad data file? 

[PeggyGithinji]

 I have figured it out, I have used a filter before the 1st macro to remove the blank excel files that were causing this error to occur :)