Hey once again everybody! I’m having a little trouble here… I’m making a scope into an excel file, I read all the file, then I create a new DataTable that I assign to a select that I made to the excel Read Range output.
And then I write range at “A40” cell the “filtered by select” table.
At the end of this I get this error:
If it helps, I let here my sequence:
And my selector is: “responsiblesDT.Select(”[column4]=‘P403146’“).CopyToDataTable”
please check first with write line Datatable_Name.Rows.Count.Tostring() , It is returning anything or not. I guess your DAtatable is no returning anything.
if contains rows then make sure that your datatable schema are same or not , in which you are copying the filtered results.
Hello @aksh1yadav ! Thank you for your answer. I’ve executed the row count and it contains 19 rows. I don’t understand what you mean with “if contains rows then make sure that your datatable schema are the same or not”, can you please explain me? Thank you a lot again!
See their will be a datatable which is containing your rows.
and the other is in which you are copying your filtered results.
Let us assume it is
Datatable New = Your_Datatable.clone // IN assigne statement before your filter query.
New = responsiblesDT.Select(”[column4]=‘P403146’").CopyToDataTable"
The datatable wich recieves the filtered results is a blank one so i guess its nothing about the schema…
@aksh1yadav Hi! One last question, is there a way to use datatable.select with multiple criteria? Something like : “(responsiblesDT.Select(”[column2]=‘P403146’ AND ‘P403470’“)).CopyToDataTable”
I find .Where to be most helpful with filtering tables and arrrays.
Syntax examples can be found all over for LINQ or lambda in vb.net and C#.
UiPath uses vb.net so your line will look something like:
responsiblesDT.AsEnumerable.Where(Function(row) row(“column2”).ToString.Trim=“P403146” OR row(“column2”).ToString.Trim=“P403470”).CopyToDataTable
- You can also replace “.CopyToDataTable” with “.ToArray” if you would like to access the original datatable through each DataRow within that Array.
- Using this method also lets you be flexible in your conditions so you can use .Contains, .StartsWith, and all of the above, as well as using If conditions inside your criteria. For example,
responsiblesDT.AsEnumerable.Where(Function(row) If(IsDate(row(“column1”).ToString.Trim),CDate(row(“column1”).ToString.Trim)=Now,False) AND (row(“column2”).ToString.Trim=“P403146” OR row(“column2”).ToString.Trim=“P403470”)).CopyToDataTable
My recommendation is use .ToArray if you want to update your original table to update the spreadsheet, and use .CopyToDataTable if you just want to process those items or create a new table.
Hope this helps.
(responsiblesDT.Select(”[column2]=‘P403146’ OR [column2]=‘P403470’")).CopyToDataTable"
Simple sample for your understanding Renato Sample.xaml (8.0 KB)
@aksh1yadav @ClaytonM Very, VERY thanks! I’ve already solved the problem and you both helped me a lot! Very thanks. Do you know how to get information in a certain row that you have only one cell value but you want to get another one?
Imagine that I have the value “P898870” stored in a variable, do you know how do I get the email of the same row?
So you can use the filter method you use to access the other columns. You can also use a ForEach to go through a series of rows until a value is matched then access the other column of that row.
First we can look at your filter method.
email = responsiblesDT.Select(”[column2]=‘P403146’").ToArray(0).Item(“emailcolumn”).ToString
or you can store it to a variable first
variable as Array[DataRow] = responsiblesDT.Select(”[column2]=‘P403146’").ToArray
email = variable(0).Item(“emailcolumn”).ToString
If there are a number of rows you need the values from then use a ForEach.
variable = responsiblesDT.Select(”[column2]=‘P403146’ OR [column2]=‘P403470’").ToArray
ForEach row In variable
email = row.Item(“emailcolumn”).ToString
You can also substitute the .Select filter method with .Where as I mentioned and procedure is basically identical. You can also use .CopyToDataTable instead of .ToArray but you need to use a datatable variable with .Rows(0) to pull first row or a ForEach row.
If your filter only ever results in 1 line item then you can pretty much just use the (0) next to the Array or dt.Rows which takes the first item in the list.
Ok I think I’ve understand it all.
So you are saying to me that if I write something like →
(responsiblesDT.Select(“[column4] WHERE [column2]=‘P403146’ OR [column2]=‘P403470’”)).CopyToDataTable
It should return the value from column4 where the values on column2 are P403146 and P403470?
Hey there is always multiple solutions so if what you posted returns what you would like, I’d say you’re good to go.
However, what I was saying exactly was that your initial .Select filter line already returns the email column but it is inside the datatable.
So if “column4” if your email column,
dtnew = (responsiblesDT.Select(“[column2]=‘P403146’ OR [column2]=‘P403470’”)).CopyToDataTable
Then, you can process the emails by using a ForEach, like
ForEach row In dtNew
email = row.Item(“column4”).ToString
and do other actions in the loop.
So really just depends on what you need the email for.
Very thank you ClaytonM. You have been a big big help in my problem. Thank you once again, bud
To whom it may concern: It took me some time to figure out where to put the quotation marks correctly in the same solution, but with selecting data rows by decimal values as criteria, put in variables.
So in “Renato Sample with decimals and variables.xaml”, you find a shortcut:
Renato Sample with decimals and variables.xaml (12.7 KB)
If you have 5 filters to be applied on DatTable & you have empty rows returned say after 3rd Filter. This error pops up when it is trying to apply 4th filter on returned empty data OR the end method CoptToDataTable ?
I assume it it because of the method at the end.
will this work only if number of rows are equal?
I have a similar problem. UiPath is giving me an exception due to “origin does not contain datarows” but on debug mode I see the values (see attached screenshot)
I do not really know how to solve it. Because the dataTable includes the datarows and it is clearly visible on debug mode. Have you ever had a similiar issue?
just open a new topic on your case and get individually support. Please share with us your filter statement also. Thanks