Filtering Out / removing specific data in excel

[FrankieTEWV]

Hello All,

I'm building an automation where the bot will copy appraisal dates from a report and input them input them into our ESR system.

The appraisal report is in excel format - and i need it so that any staff that have 'N/A against  the 'Appraisal Date' column are removed so it only leaves staff which have dates . This action will take place before populating it to the work queue as i don't want any staff who have N/A to be sent to the work queue as the bot wont be able to upload any info into ESR if the Appraisal date show's as N/A.

An example of the report is below - i have removed any sensitive info and you can see in column N is the Appraisal Date ifno

I have used a delete function in the excel VBO however it just moves the data up in column N and doesn't remove the entire row which will cause issues as if the column moves up the dates will be pushed up to the next staff and will mis match ..( if that makes sense)

I need it so it searches the column in N and if it says 'N/A' remove the entire row against  the staff.

I'm not sure if there is an action under the excel VBO for this?

Any help would be greatly appreciated.

Thanks

Frankie

[harish.m]

Hi Frankie,

You can read the excel by using Get worksheet as collection action in MS Excel VBO. after that the out put data will be in  collection

Filter the collection using filter action . 

Here are the  threads which are useful for filtering collection

BP_KB_Filter Collection

https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=netframework-4.7.2 

Other way is you can copy the MS excel VBO and filter the data in excel itself before reading in to collection for this you need to use code stage.

[stepher]

Greetings @Frankie Legg ,

To amplify what @Harish M states...  At some point in the transition between the Excel worksheet and the Blue Prism work queue, your data is going pass through a Collection.  From there you can evaluate the Appraisal Date field.  As Harish says, you could filter the entire collection for records where the Apprailation Date is not 'N/A'.  The other option, which I often use, is to loop through the Collection.  The loop would have a Decision stage with the statement 'IsDate(ToDate([CollectionName.AppraisalDate]))'.  (That is from memory, so may not be exactly correct.)

I believe the benefit of this evaluation, compared 'Not N/A', is that only valid dates will pass down the path to loading the workqueue.  The need for this, though, is dependent on the level of data verification applied to the source data before your process.

I hope that helps,

Red

[FrankieTEWV]

Thanks All,

What we have done to fix this issue like you say is manipulate the data once it has gone into the collection and added in the filter logic there.

Previously I was trying to manipulate the data from within excel before sending to collection but manipulating it within the collection seemed to do the trick and was the easier option.

Thanks All really appreciate you taking the time to respond to my query.

Kind Regards

Frankie

[michaeloneil]

Hi @Frankie Legg 

The delete action will only delete the values from the cells and not the rows but if you need to do this within excel you can build a custom code stage. I previously built one you can utilise, the code is below along with screens of the flow/inputs etc. You can use this to delete columns or rows, if you are deleting entire rows then the range you enter is only the row range e.g. 1:6 would delete rows 1-6. Or for columns A:C etc

Dim wb, ws As Object

Dim strDir as String = direction.Trim().Substring(0,1).ToUpper()

Dim dirn as Integer = 0

Dim excel, sheet, range As Object

wb = GetWorkbook(Handle, Workbook)

ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()

ws.Activate()

excel = ws.Application

sheet = excel.ActiveSheet

range = sheet.Range(Reference)

range.Select()

Select Case strDir

Case "U"

dirn = -4162 ' Excel.XlDirection.xlToUp

Case "L"

dirn = -4159 ' Excel.XlDirection.xlToLeft

Case "R"

dirn = -4161 ' Excel.XlDirection.xlToRight

Case Else

Throw New ArgumentException("Invalid Direction: " & strDir)

End Select

range.Delete(Shift:=dirn)

'If direction = "UP"

'range.Delete(Shift:=-4162)

'else range.Delete(Shift:=-4159)

'end if