Compare column headers stored in config sheet with that of input excel column headers

I have stored column headers of some 7 input excel files in config file of RE Framework with name as pattern1, pattern2 …pattern7 and value as column headers separated by comma.
Everytime 7 excel files will be replaced by other input excel files and I want to validate each input excel file if it matches with the column headers stored in the config file. Each excel file has column headers different from each other. I have to take each input file at a time and match the column headers with each pattern stored in config and if match is found skip to next file for matching. How can I do that?

1 Like

@vamsikrish28

Lets assume the excel which is to check is available as da datatble (e.g. after read range)

following could be the core part

  • MatchListString.Split(","c)
    for splitting the column names by the coma into an array

  • dtData.Columns.Cast(Of Datacolumn).Select(Function (c) c.columnname).toArray
    creating an array for all column names

so with arrColumnNames.SequenceEqual(arrMatchNames) it can be cheked if column names are the same

Let us know your feedback

1 Like

But I have to match headers of each input file with the patterns stored in config until a match is found. If match is found, go to next file for comparison otherwise throw an exception and stop the execution

1 Like

@Palaniyappan can you help here?

1 Like

Fine
Hope these steps would help you resolve this
—let’s say like we have set of excel files in a folder
—and we have read the config file and stored that in a dictionary variable named dic_input
—now use a assign activity like this with a array variable
arr_files = Directory.GetFiles(“yourfolderpath”,”*.xlsx”)

—now use a FOR EACH activity and pass the above array variable as input
—inside the loop use a excel application scope and pass the input as item.ToString
—inside the scope use a read range activity and get the output with a variable of type datatable named dt
—then next to this excel application scope inside the loop use a assign activity like this
arr_columnname = dt.Columns.Cast(Of Datacolumn).Select(Function (c) c.columnname).ToArray()

—then a IF condition like this

arr_columnname.SequenceEqual(Split(pattern1.ToString,”,”)) OR arr_columnname.SequenceEqual(Split(pattern2.ToString,”,”)) OR arr_columnname.SequenceEqual(Split(pattern3.ToString,”,”)) OR …till last pattern

If the condition passes it will go to THEN part where we can leave it empty do that it will look for next excel file in loop or
Goes to else part where we can use THROW activity to throw exception

Cheers @vamsikrish28

2 Likes

It works great as expected. Thanks a lot man.
Cheers @Palaniyappan

1 Like

Cheers @vamsikrish28

1 Like

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