How to find data and extract data in excel

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

1 Like

Hi @orlando.salas

You can try in two different ways

  1. 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:

  1. 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.

  1. 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

image

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

1 Like

Thank you very much, it was a perfect solution. i use LooupDataTable Activity

1 Like

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.