Can i use list of array in filter data table Values? To check OR condition?

actually pending ,approval , denails are different sheets. i need to filter in main sheet if Status column contains (Approved, approve,case was approve, success ,passed) then i need to move to Approval sheet.

@MitheshBolla Since you have only 4 conditions I guess you can write separate conditions for each status. Even if you filter all the status at once you have to filter from it again to get the required status

Steps:

  • Read excel data to data table (DT)
  • Now filter for status Approved (from DT) and write the data in a excel or you can store in the data table (DT1), finally you can write an excel
  • Now filter for status “Success” (from DT) and write the data in a excel or you can store in the data table (DT2), finally you can write an excel
1 Like

In main sheet i have 100+ status, but those i need to classify to 4 sheets.
Approval
Withdrawn
Denial
Pending.

in main sheet if status is (Approved or approve or case was approve or success
or passed) then i should move to Approval sheet,.

As u told i used filter data table and keept this conditions . but instead of adding these status in filter data table , can i use excel in storage bucket , so that later new status may come and changes will not be in bot?

@MitheshBolla

  • You have to use the filter data table activity any way to filter the required status. But as like you mentioned you can keep all these status in an excel or notepad and can store in a storage bucket
  • Every time when the bot runs you have to read the data from the storage bucket first
  • Now, retrieve each status and pass to the filter data table activity. If you are implementing this case then one filter data table activity should be enough. Every time you can pass the status name dynamically into filter data table

FYI - The output of storage bucket is string.You might have to perform some string operations to get the different status stored in that storage bucket

1 Like

Hi @MitheshBolla ,

If you are concerned about the variation of values that would be entered for Approval Status or other Statuses involved and would need to update them as received, you could use an Excel for this Purpose and Upload it to Storage Bucket.

The Excel maybe you could keep in the format as below :
image

Then Download the Excel from the Storage Bucket. Read it. Loop through the Columns one by one, thereby picking up it’s column values, converting it to an Array and using the Solution as above provided by @ppr for Filtering with the list/array of values.

We should then be able to Write the Filtered Datatable to Separate Sheets.
Note that, the Column Headers are the Sheet Names to be created.

Let us know what is your opinion on this, and if you need further help.

1 Like

The thing u explained was my clear requriment.ill implement this, one conditon to be checked, that is

If id =“101” status= “approved” i need to cut from main sheet and add this row in approval sheet,
before adding this i need to check if Id=“101” already exists in approval sheet.

if id not present in approval sheet ill cut from main sheet and add append in approval sheet.
if id is already present then i should delete from main sheet .

Bro can i have this expression in C#

bro can i have

(From d in dtData.AsEnumerable
Where arrValues.Any(Function (x) x.Contains(d(“USCIS Case Status”).toString.Trim))
Select r = d).CopyToDataTAble

this in c#

let’s start by this

(from d in dtData.AsEnumerable()
Where arrValues.Any(x => x.Contains(d["USCIS Case Status"].ToString().Trim()))
Select r = d).CopyToDataTable();

And keep in mind that in some scenarios also correct statements will not validate due some internal issues (C# combination with UiPath.System.Activities at certain version level in combination with some targetframeworks).

1 Like

still getting error

better ro use the line breaks for easier reading as well

As mentioned above and pre-announced

1 Like

means , we cant resolve in c#?


kept in rows

@MitheshBolla ,
I think the keywords need to be exact when specified in C#.

Could you try the below :

(from d in DT.AsEnumerable() 
where sheetNames.Any(x => x.Contains(d["USCIS Case Status"].ToString().Trim())) 
select d).CopyToDataTable();
2 Likes

This worked bro ,For copying list of status from array to new sheet.
to delete the same list of arry in same Main sheet ie : DT?

(from d in DT.AsEnumerable()
where sheetNames.Any(x => x.Contains(d[“USCIS Case Status”].ToString().Trim()))
select d).CopyToDataTable();

Delete, delete is not working i checked.

@MitheshBolla , What was the method used for the Delete Operation ?

1 Like

The query which u have provided for me is to filter in dt and add to new sheet and also i need to delete those filtered rows in my main sheet.

will remove work?

(from d in DT.AsEnumerable()
where sheetNames.Any(x => x.Remove(d[“USCIS Case Status”].ToString().Trim()))
select d).CopyToDataTable();

@MitheshBolla ,

For Updating the Excel Sheet by Deleting the Filtered rows, we can first get the Filtered rows which does not match the condition. Meaning the reverse of the First Query. We can get the filtered rows using the below Query.

(from d in DT.AsEnumerable() 
where !sheetNames.Any(x =>x.Contains(d["USCIS Case Status"].ToString().Trim())) 
select d).CopyToDataTable();

It is the same Query, except we are using a ! operator (Not) which reverses the condition and it will able us to get the Non-Matching rows.

Now, To Update the Excel Sheet, we could first clear the entire rows in the sheet and then write the Filtered rows into it.
For clearing the entire sheet, we could use a Write Cell Activity as mentioned in the Post below.

Then use Write Range Activity to write the filtered Datatable to this Sheet Starting from A2.

1 Like

Thanks Bro ,

In this Post i had got solutions for the list below.

  1. Passing arrylist to filter data table
  2. filtering datatable with arrylist c# code
    3)deleting filtered arraylist in datatable with c# code.

Thank you all for your help.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.