Missing column index

Hi Team,

I have header from excel file: s.no, Name, Age, Salary, Location, Address, Designation.

but from file two columns missing: Age, Designation.

we have to identify missing column index to add because we would need same column structure.

Can anyone help me here please

Hi @Sushma_Jaladi1, welcome to the Community.

The following approach will get you the indices of the missing column from the excel file:

  1. Use the Read Range activity to read the header row into an array of strings called headerRow.

  2. Create a new array of strings called expectedHeaders that contains all the column headers, including the missing columns.

  3. Use a For Each activity to loop through the expectedHeaders array.

  4. Inside the loop, use the “Index Of” function to find the index of the current header in the headerRow array. If the “Index Of” function returns -1, that means the header was not found in the headerRow array and its index is missing. Add the missing index to a new array.

  5. After the loop is complete, the new array or list will contain the indices of the missing columns.

Hope this helps,
Best Regards.

@Sushma_Jaladi1

Welcome to the community

First get the columns from both the files or read the data from both files and to get columns use

Dt1 columns array = (From dc In dt1.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray()

Similaryly get dt2 columns array…

Now use exceptarray = dt1Array.ToList.Except(dt2Array.ToList).ToArray which gives you the array of columns which are not present in the main dt

Now use Array.indexOf(dt1Array,exceptarray(0)) to get the index of first missing column from the exceptarray…

You can use loop to increment index and check the index of each column name

Hope this helps

Cheers

Hi Anil,

I am not getting index option here. Could you please let me know if I am making any mistake
image

@Sushma_Jaladi1

My bad…please use it like this

Array.indexOf(dt1Array,exceptarray(0))

cheers