Searching_Excel

[Gokul_Murali]

Hello guys,

I have an excel with 3 column

i want to get the data from the column “CLAIM NUMBER”

Condition is that The column name can have some changes for every rpa run like this

for example : claim number name can be changed to “CLAIM NUMBER_02”
but the “CLAIM NUMBER” will be same and some added data,

so can we pullout data from that column

[mkankatala (Mahesh Kankatala)]

Hi @Gokul_Murali

You can iterate the every row of column by using for each row in datatable.
You can give like this Currentrow(“CLAIM NUMBER”*).toString

Hope it can help you!!

[Anil_G (Anil Gorthi)]

@Gokul_Murali

If the position of the column is fixed then use the column index instead of name to access it

alternately you can get the column names and get the exact name using below in assign

ColumnName = dt.Columns.Cast(Of DataColumn).Where(function(x) x.ColumnName.Contains("CLAIM NUMBER"))(0)

cheers

[Lekshmi_R]

You can read the data into a datatable and check the header using a contains method.
currentRow.item(0).toString.contains(“CLAIM_NUMBER”)

[Gokul_Murali]

what if the column index is also changing can we pull the data

[Anil_G (Anil Gorthi)]

@Anil_G

ColumnName = dt.Columns.Cast(Of DataColumn).Where(function(x) x.ColumnName.Contains(“CLAIM NUMBER”))(0)

@Gokul_Murali

This can be used in that scenario to get the exact name and then subsequently can use it

cheers

[adiijaiin (Aditya Jain)]

Hi @Gokul_Murali ,

Prior to start Iterating the Loop to read data,
you can find the exact column name by doing the following:

create a variable for ClaimNumberColumn as String;
and ColumnCollectionNames as DataColumnCollection
ColumnCollectionNames= dtRecords.Columns

Start a for each loop on ColumnCollectionNames and in an if condition write the below expression:
item.ColumnName.toLower.contains(“claim number”)
in the then section ClaimNumberColumn=item.ColumnName

that way you would be able to get the column name and then you can use it as needed.

[Gokul_Murali]

@Anil_G

[adiijaiin (Aditya Jain)]

you can use .toString at the end

[Anil_G (Anil Gorthi)]

@Gokul_Murali

Please add a .ColumnName like below

ColumnName = dt.Columns.Cast(Of DataColumn).Where(function(x) x.ColumnName.Contains("CLAIM NUMBER"))(0).ColumnName

Hope this helps

Cheers

[Gokul_Murali]

@Anil_G
i got an error like this

[Anil_G (Anil Gorthi)]

@Gokul_Murali

I believe you copied exactly…please remove inverted comma and add them back in the studio

Cheers

[Gokul_Murali]

@Anil_G

it is working fine thank you,

i have one more doubt if the column name in the excel is full upper case or full lower case does it work if not how can i alter the expression

[Anil_G (Anil Gorthi)]

@Gokul_Murali

Add .ToUpper

x.ColumnName.ToUpper.Contains…

Cheer

[Gokul_Murali]

@Anil_G

Iam asking regardless of upper or lower case in excel column name should it matches

can we do like that in the expression

[Anil_G (Anil Gorthi)]

@Gokul_Murali

.ToUpper will convert every letter to upper case whatever it gets as input let it be upper or lower case…so the comparision as I gave all upper i gave .Toupper to convert anything we get into upper case and then compare

Hope this helps

Cheers

[Gokul_Murali]

Got It thanks