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)
May 25, 2023, 11:12am
2
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)
May 25, 2023, 11:12am
3
@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
(Lekshmi R)
May 25, 2023, 11:13am
4
You can read the data into a datatable and check the header using a contains method.
currentRow.item(0).toString.contains(“CLAIM_NUMBER”)
what if the column index is also changing can we pull the data
Anil_G
(Anil Gorthi)
May 25, 2023, 11:16am
6
@Gokul_Murali
This can be used in that scenario to get the exact name and then subsequently can use it
cheers
adiijaiin
(Aditya Jain)
May 25, 2023, 11:18am
7
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.
adiijaiin
(Aditya Jain)
May 25, 2023, 11:25am
9
you can use .toString at the end
Anil_G
(Anil Gorthi)
May 25, 2023, 11:28am
10
@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
@Anil_G
i got an error like this
Anil_G
(Anil Gorthi)
May 25, 2023, 11:37am
12
@Gokul_Murali
I believe you copied exactly…please remove inverted comma and add them back in the studio
Cheers
@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)
May 25, 2023, 12:37pm
14
@Gokul_Murali
Add .ToUpper
x.ColumnName.ToUpper.Contains…
Cheer
@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)
May 25, 2023, 12:48pm
16
@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
system
(system)
Closed
May 28, 2023, 12:58pm
18
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.