I am sorry if am double posting - this is my first post here. I posted this originally in the Beginner Forum, and through my previous research of the forums, I wasn’t able to find a post to help me solve this problem, so your help would be greatly appreciated!
Here is my question:
I have two files: “data table.xlsx” and “parameters.xlsx”
Within the “data table.xlsx” file, there are three rows with data, with the FIRST column being the key column. Within the “parameters.xlsx” file, I have only one column with some of the same values being the same as the FIRST column of “data table.xlsx”
What I would like to do is filter the “data table.xlsx” file so that I can output a new Excel file which contains ONLY the full rows where the FIRST column contained the values found within the “parameters.xlsx” file.
Here is the approach I have taken:
- Added both Excels into Data Tables within UiPath
- Created a nested “For Each” loop to see if I can filter the main DataTable by only the values found within the Parameters datatable.
I am having trouble with Step 2. I have included my original files (including “data table.xlsx” and “parameters.xlsx”) and I have also included my project XAML files as well.
Could you please take a look and offer some suggestions as to how I can solve my issue? I would really appreciate your help! Thank you so much.
For example, since the “parameters.xlsx” file only contains the values “A” and “C” , I would need my final output file to look like this:
“Column 1” “Column 2” “Column 3” (headers row)
A Bob Male
C Jill Female
data table.xlsx (7.8 KB)
parameters.xlsx (7.7 KB)
For Each Example.zip (4.9 KB)
I will check this and will send you the updated XAML.
Here you go.
Attached zip file contains the sample workflow with inputs and output.TestProcess.zip (28.5 KB)
Thank you for your help! That works, but my next question is how can I modify the filter so that it outputs every row with a matching parameter in the Final table?
For example, if the data table contains 5 rows of data, and there are 3 rows of data with "A"s in Column 1, I want the the output all 3 rows that contain “A” in Column 1 in the Results file. As of right now, the output in the Results file only outputs the first instance of “A” in the data table.
I tried to nest the current For Loop you created within another For Loop with DT1, but my Results file is not providing the required output. Please take a look below:
Add For each loop for dtFiltered after Assign Activity; in the body of the loop place the Add Data Row activity.
Thanks a lot Karthik - that fixed it!
One additional question I am having is that when I change the values in the “Datatable” to more complex values (like ‘XH891D’ instead of “Bob”) then I am getting the following error in the dtFiltered Assign activity you created:
" [DataTable Select function cannot perform “=” operation on System.Double and System.String]"
I think it is because it is no longer simple string values in the “Datatable.” Do you have any suggestions on how to fix this so that I can implement this workflow for more complex datatables that do not have just simple string values with no numbers?
In the data table filtered condition, I wrote assuming the values are variables.
In case of numbers you need to remove single quotations around the lookup variable.
For example, YourDataTable.Select(“Column1=” + variable + “”)
No single quotations around the variable in case of numbers.