Custom Error Handling for Excel check

[NidhiAg]

I am working on the workflow that need to check if in a particular excel a specific column exist. If column doesnot exist then throw error "Column <columnName> is missing <excelname>". Also I have multiple excel file with different set of column to check in each. I am  using Filter to check if all required column are not null. But the error is just pointing out column not the excel name. 

I tried using Message Tool not sure how to use it in this case

[dougperez]

Ok lets go

About your first question, see the workflow EXAMPLE1, It validates the particullar collumn "COL_3" and throw error if it not exists. I think you can just adapt to answer your second question

[danilang · Accepted solution]

Hi @NidhiAg 

Here's a ColumnValidator standard macro that you can drop anywhere need it.   

The top input is the list of required columns, with one column name per row.  The bottom input is the data input which requires a [filename] field.  After removing the filename column, use the Field Info tool to get the columns names in your input as rows.  Join this to the list of required columns and take the L output which will be the required fields missing from the input.  Concatenate list of missing fields and append the filename.  The Message tool to configured to run when the length of the MissingFields !=0. 

The tools in the synchronize output container are there to ensure that the error message is thrown before the data is passed through to the output.  You can directly connect the Data input to Output without these, but the data is then available at the output before the error message has a chance to be thrown.  The Union tool blocks the Data stream until the Error message stream has completed.  The remaining 2 tools remove the extra row and column added from the Error Message stream  so the data passes through unchanged if all the columns are present

The main workflow is quite simple. Connect the data to the D input of the Macro and connect the list of required columns to the C input.  I used a Text input tool, but you can generate the list any way you like.  The select tool is there to demonstrate what happens if the a column is missing.  If all the required columns are there the data is passed to the output.  If one or more  columns is missing the results look like this

Just one more thing.  In order to get the workflow to stop when the error is thrown, you need to check the following option in the runtime tab of the workflow configuration

Dan 

[NidhiAg]

Thanks this worked perfect.