How to continue the workflow when some input data is missing?

[horse9118]

Hello, 

I have a workflow that takes in a spreadsheet with 1 main tab and multiple supplemental tabs. I'm reading in the sheet list along with the file path, then use a formula to combine the 2, and finally feed that into a dynamic input tool in order extract the data from each tab. After that I use a series of joins in order to combine everything into 1 big data set. Everything works great except the supplemental tabs don't appear all the time. So if I feed in a spreadsheet that's missing a tab (like in the below screenshot). The workflow would throw out a parse error and stop working all together. I'm ok with no joining the missing tabs so how do I make the workflow ignore the error and keep going? 

[AngelosPachis]

Hi @horse9118 ,

After your dynamic input tools, you can add a record ID tool and a transpose tool, where you will group by the new record ID field and transpose all other columns. Then you can apply the filter with a slightly altered condition.

[Name]!="F5" AND !ISEMPTY([Value])

In this manner you will not reference [F5] column explicitly and the filter tool will not error. Then before your dynamic rename tool, you can add a cross tab to bring your table to the original structure.

Hope that helps,

Angelos

[horse9118]

Can you provide an example? 

[DawnDuong]

hi @horse9118 

From the workflow layout, it looks like for each of the supplement tabs, you apply the same processing before you combine them into 1 big tab.

In this case, I think you can use a batch macro to read only the supplementary tabs that are present in the file. Depending on how the tabs are named - if there are certain rules that can help you identify the supplementary tabs, then you can use a Data Input Tool to read the sheet names only, then apply a filter to keep only the supplementary tabs for further processing in a batch macro.

This is the method that I often use to process financial data such as monthly management accounts / GL where I do not know for sure all which tabs will be / will not be there.

Dawn.

[AngelosPachis]

@horse9118 of course, can you provide a sample workflow with some mock data that we can work on please?