I want to get values fro data table based on row value of a specific column. I have a data table of type shown below with 2 columns Property and value. I want to get what is there in value’s column if i give name property or telephone property as an input. For example if i give Name as an input it should return Rameez Imtiaz or if i give department as an input it should return RPA.
you can try put in in excel sheet then extract the data from excel like this
worked for me.
Lookup datatable will be very fast and reliable in this case
So once after getting the datatable variable named dt
Now use a FOR EACH ROW activity and pass the variable dt as input
—inside the loop use LOOKUP DATATABLE ACTIVITY and mention these in property panel
Input - row(“value”).ToString
Datatable - dt
Lookup column name - “value”
Our result - str_output // which will be our output string variable
Target column name - “Property”
Now next to this lookup datatable across a writeline and mention as str_output.ToString which will show us the output in the output panel
Send to excel your data. Put headers for your columns.
Then use Excel Application Scope for your Excel file.
Read range for your sheet.
For each row in Your Output From Read Range
In the body use IF
Your variable that you need to compare = row(“Column Name”).ToString
Then(True statement) use Assign activity and make something like newVariable = row(“Column”).ToString
Else(False statement) let him to be empty or put delay for one second.
Example how I foreach each row to find password. I’m comparing usernames.
why you not using Dictionary<string,object> for this kind of activity?
is there any challenge if you use dictionary?
if not use dictionary .
create new dictionary :
newDict = new system.Collections.Generic.Dictionary(of string,object)
to add values :
newDict(“Name”) = "name1"
newDict(“Telephone”) = 1234748477
I cannot use excel. There should be any other way
@Veselin_Ganchev cannot use excel
@Ajju i dont know but one thing is that the data will not always be in this sequence and there could be 50 rows or just one row.
Okay you must use the same logic, in your datatable use headers, put some names and again you can use for each row and etc…
if it more than 50 rows also possible if it is like key and value pair then you can go with dictionary.
if values are coming from excel then use a loop to add all the value to dictionary (same method used in REframework) then it is very easy to retrieve the values from it
did lookup datatable activity helped us on this
If you want to get the value from Value column considering the value of another respective column. then use following Linq Code.
YourdataTable.AsEnumerable().Where(Function(j) CStr(j("ColumnToValueSearch")).ToString.Trim.Equals("Value")).Select(Function(x) cstr(x("ColumnToGetValueFrom"))).ToArray
This will give you Array of String.
I hope this will help you.
below linQ is working for me give a try.
Linq to get the value
valueReq = Dt.Select("[Column1]=‘text’").ElementAt(0)(“Column2”).ToString
@Ajju i tried dt.Select("[Property]=‘Name’").ElementAt(0)(“Value”).ToString
i got an error “message”: “Log message: Cannot find column [‘Name’].”,
can you share the table structure ?
@Ajju it worked. rewrote the query and it worked. Thanks a bundle. the output is exactly as required
it seems some mismatch with double quotes, can you remove and give double quotes again