How to filter an Excel file with values in another Excel file?

Help
#1

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:

  1. Added both Excels into Data Tables within UiPath
  2. 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)

1 Like
#2

I will check this and will send you the updated XAML.

Regards,
Karthik Byggari

1 Like
#3

Here you go.
Attached zip file contains the sample workflow with inputs and output.TestProcess.zip (28.5 KB)

Regards,
Karthik Byggari

1 Like
#4

Hi Karthik,

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:

Nested%20For%20each
Nested%20For%20each365×570 9.35 KB

Thank you!

#5

Add For each loop for dtFiltered after Assign Activity; in the body of the loop place the Add Data Row activity.

#6

Thanks a lot Karthik - that fixed it!

#7

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?

Thanks

#8

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.

Regards,
Karthik Byggari

1 Like