Missing sheets with Dynamic Input tool

Hello community,

I have an excel file which contains 15 sheets within it and all the sheets have same structure/layout. I use input tool to initially import the sheet tabs and it shows all the 15 tabs present in the workbook. Then I use dynamic input to read in the data from all the tabs, however, one of the sheets doesn't show up. Can someone please help me resolve this error?

Below image is when I use the Input Data tool:

Below screenshots are from the Dynamic Input tool

Datasets

Accepted answers

Qiu

@keeprollin
I made something and hope it will work for you.
For your Data Cleansing, I use RegEx but works maybe only your given cases.

0929-keeprollin.yxzp

All comments

Qiu

@keeprollin
It appears to me that your sheets are not all with the same schema.

Check out this solution and it should help.

I particularly recommend the solution from @mceleavey

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multiple-files-with-multiple-sheets-into-one-file-with-multiple/td-p/723634

keeprollin

@Qiu Yes, you are right. I have three excel workbook with multiple sheets within them. They all have same columns, however, one of the worksheets in workbook A has few columns with different schema (their data type is different) from the rest. Since dynamic input tool is used to read in data having same schema, it has been giving me error with this particular worksheet. I'm fairly new to Alteryx and I tried going through the above link as mentioned, but I'm not able to understand the solution. Is there any other link which can help me solve my problem?

Qiu

@keeprollin
The importing of Excel Sheets with different schema normally requires macro and such.

If you will, you can upload your excels and eliminate the sentive data, we can make one sample flow for you.

Qiu

@keeprollin
I made something and hope it will work for you.
For your Data Cleansing, I use RegEx but works maybe only your given cases.

0929-keeprollin.yxzp

keeprollin

@Qiu This was wonderful! But let's say that I have to apply regex on multiple columns, how can I do that. The above scenario is good when using regex tool on only one column.

Qiu

@keeprollin
We can use multi-field Formula for the case.

0929-keeprollin-r1.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703