Import multiple excels files with different schemas and sheet names

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.

Macro error.PNG

Batch Macro

Accepted answers

All comments

binu_acs

@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.

binu_acs

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

CombineFile_190723.yxzp

PeggyGithinji

Thanks @binu_acs. 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.

binu_acs

@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 @binu_acs 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?

error message.PNG

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703