Validation of headers in an excel file

Help Studio
1

I have an Input excel file. I have to validate all the headers and find if any of them are missing

2

@jayanth_moguluri

  1. Create one array variable with all mandatory column names. Let’s say arrMandatoryColumns.

  2. And then use Read Range activity to read the Data from the Excel file and it will give output as DataTable. Let’s say InputDT.

  3. And then check below expression in IF activity.

         arrMandatoryColumns.Where(Function(x) InputDT.Columns.Contains(x)).Count = arrMandatoryColumns.Count

The above expression will give True and will go to Then side of all mandatory columns exists else it will give False and go to Else side.

3

hi what is function x in the above expression

5

This is backwards. There needs to be a list/array of the columns that should exist. Then you loop through that and check if it exists in yourDT.Columns

6

Right. It can be do more simple way

image
image641×615 17.5 KB

1 Like
7

When having a colset (columnNames) and a checkset (validationlist)
a reliable check is not done, when only it is checked if all items from one set are present in the other set

A check wil be true:

  • All items are same, and no any other items are present in the both lists

A check will be false:

  • if there are items in a set, but not present in the other

grafik
So we check for:
items count for all common items (intersect) is the same as the counts from the sets

False cases:
grafik
grafik

Done with datacolumns

grafik
grafik878×135 3.77 KB

Identification of not present cols can be done by
grafik

and vice versa for invalid contained in colset

1 Like
8

@lakshman How to use Trim function in this expression. Sometimes, column name have leading/trailing space in it.