Missing sheets with Dynamic Input tool

[keeprollin]

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

1. 

2.

[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-in...

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

[keeprollin]

@Qiu Thank you for your reply! I'm uploading two spreadsheets, file1 and file 2, each having 2 tabs. If you look at sheet1 from file1, all the columns have values in them, but if you look at its sheet2, there is no value in the 'city' column and the column 'salary' has values mentioned as 10+, 12-15, and 12. What I would ideally like to do is to convert 10+ to 10 and 12-15 to 12 and fix similar issues if they exist in other tabs of the current and other spreadsheets (Not sure if regex will be able to take care of this issue). Next, I want to combine the values from both the tabs of both the spreadsheets after fixing any similar issues in it. Currently, I'm unable to do so because even if a column is supposed to of string data type but has no values provided, alteryx treats it as double data type. 

[Qiu · Accepted solution]

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

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