Hello guys, I come to you for your knowledge, the fact is that I have to identify if the code E020 and S097 exist in an excel file, if it exists I must extract the value of each one that is found in column “F”, it can help me to design a logic applicable to this case? Thank you very much in advance
hi convert excel into datatable and run for each row activity in for each row place “if” condition
DT1.(“Column c”).equal(E020) if condition is true use get row item activity to fetch data from particular cell if its helpful please mark as solution
Hello, I need to extract the data from row “F” once I have found the code in row “B”.
Lets say that you are in a loop checking each row.
For Each row
If row(1).ToString = "E020" OrElse row(1).ToString = "S097" Then
Assign:
- MyVariable = row(5).ToString
End If
Next
row(1) = Column B
row(5) = Column F
You can try in two different ways
- Lookup datatable activity.
Using this activity we can lookup the values in a specific column in datatable and then if the value is present then the target value we need from that particular can be obtained. Much faster than looping concept.
For eg.
if u want to lookup the value E020 in the datatable dt1 in B column and if found need to extract the value from F column
So steps to be followed are:
- read the excel file and store in dt1
2.use the lookup datatable activity and configure the following properties in it.
DataTable : dt1
Lookup Value : “E020”
Lookup Column
ColumnIndex = 1
Output
rowindex = index, where index is a Int32 variable which stores the rowindex of lookup value row.
Now use an if condition with condition as index<>-1, when the value is not found in column of datatable, then index will be returned as -1, which indicates there is no value in datatable.
index<>-1 implies index not equal to -1,
if the condition is true, Then
use the dt1.Rows(index)(5) to get the value F column value corresponding to the E020 value.
Simillar apprach can be done for the other value too.
- Use filter datatable activity.
Use filter datatable activity to filter the datatable acoording to the Value in B column , like u are filtering the B column with values E020, like below
Where the dt1 is the datatable which we are filtering, dt2 is the output filtered datatable.
Filtering condition
I put column index as 1 to filter with value “E020”
then after filter datatable activity use if condition with condition as
dt2.Rows.count<>0 this implies if there is any rows in filtered datatable.
if the condition is True,
then the use the below to get the F column value.
dt2.Rows(0)(5).ToString
Hope the both ideas helps you
Mark it as solutions if it helps
Regards
Nived N
Happy Automation
Thank you very much, it was a perfect solution. i use LooupDataTable Activity
Hello friend I have one more question, if I should do the same with more codes, for example “E403” “F445” that are somewhere in the same column “B” and should I extract the information from column “F”. What could I do?
Hi @orlando.salas if u are coming with more codes then it is u can add them in a collection and then loop through it. Inside that loop use lookup datatable activity as same i desribed in the previous reply. it will get solved.
Regards
Nived N
Happy Automation
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.