Input file Column Validation

Help Activities
, ,
1

Hi Expert,

I wish to validate the all headers columns from input file. Bot will throw the exception to the user if expected header columns are not there in the input excel file. How can achieve this by Linq.?

Input file is attached for your reference.

Regards,
Balachander P
Inputfile.xlsx (9.0 KB)

2

@Balachander_Pandian

Say the columns names array is arr1…and datatable is dt where the column names to be verified are present

Then you can use the below to check in if condition

Arr1.Except((From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray()).Count>0

on then side you have column names which are not present and on else side all column names are present

this will give you the names which are not matchign as an array

Arr1.Except((From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray()).ToArray()

Hope this helps

cheers

3

Hey @Balachander_Pandian ,
I worked on your input file and replicated your scenario

Here is the solution in compressed ZIP file.
Validate_Col_Headers.zip (9.5 KB)

Hope it helps you!

4

Hi @Balachander_Pandian

Assign headersMatch=dt.Columns.Cast(Of DataColumn)().Select(Function(column) column.ColumnName).SequenceEqual(Array_Headers)

image
image874×1114 39.8 KB

Forum.zip (63.7 KB)

Hope it helps!!

5

Hi @Balachander_Pandian

image
image880×1061 43.5 KB

=> Use below in Assign acitvity:

expectedHeaders= {"Stud_ID", "Stud_Name",	"Department",	"English", "Tamil", "Maths",	"Science", "Computer_Science", "Toltal Mark"}

expectedHeaders is of DataType Array(System.String)
=> Use Read Range Workbook to read the excel and store the output in a datatable.
=> Use below syntax in assign acitivty:

missingHeaders= dt.Columns.Cast(Of DataColumn)().Select(Function(column) column.ColumnName).Except(expectedHeaders).ToList()

missingHeaders is of DataType System.Collections.Generic.List(System.String)
=> Use an If acitivty and give below condition

If
   missingHeaders.Any() 
    Then
             Throw -> new Exception("Missing headers: " + String.Join(", ", missingHeaders))
    Else
            Log Message -> "Headers Matched"
End If

Refer the below workflow for better understanding:
Sequence.xaml (8.2 KB)

Hope it helps!!
Regards

6

@Anil_G Thank you very much

1 Like
7

@pravallikapaluri Thank you very much

8

@Vikas_M thank you very much

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.