Delete rows based on a column value from an excel having huge data

[Shwetavd]

In an excel with more than 80K rows, I need to delete the rows which have specific text (e.g. #N/A) in a specific column. Using collections is not optimal, are there any other means to do this?​

[devneetmohanty07]

Hi Shweta,

For your requirement I have created a custom action which you can achieve by extending either 'MS Excel' or 'MS Excel - Extended' VBO.

NOTE: I would recommend create a duplicate object of these objects and make your changes there so that you always have a backup with you in case anything goes wrong.

Now, you need to create a new action in your already existing Excel VBO business object named 'Remove Rows On Criteria' and provide the following input parameters to your action:

Input Parameters:

Handle : This is a number type data item which will hold the current session dictionary value.

Workbook : This is a text type data item where you need to pass the workbook name that you might get from 'Create Workbook' or 'Open Workbook' action.

Worksheet: This is a text type data item which indicates the name of the worksheet in your excel workbook file where the rows need to deleted.

Field : This is a number type data item which indicates The column index which we need to provide in order to apply the filter. The column index here starts from 1 till N not 0 till N-1. For example, if you want to apply filter on the first column then provide the value as 1.

Criteria : This is a text type data item which indicates the text value which you want to compare for deleting rows.

Reference Column : This is a text type data item which indicates the column address of the first column in your range. For example, if your range starts from "A1" then you provide this value as "A".

Header Row: This is a number type data item which indicates the row number which indicates the header where the filter will be applied. For example, if your range starts from "A1" then you provide this value as "1".


Output Parameters:

Message: This is a text type data item which indicates the error message if the Success flag value is False.

Success: This is a flag type data item which indicates the execution status of the action.





Now, you can add a code stage named 'Remove Rows On Criteria' and add the following inputs parameter and map the data items to it:




Code:

Dim wb, ws As Object
Dim excel, sheet, range As Object
Const xlCellTypeVisible As Int32 = 12

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet

ws.Range(Reference_Column & Header_Row).AutoFilter(Field:= Field, Criteria1:= Criteria)

ws.Range(Reference_Column & Header_Row+1 & ":" & Reference_Column & sheet.Cells.Find("*", , , , , 2).Row ).SpecialCells(xlCellTypeVisible).EntireRow.Delete
sheet.AutoFilterMode = False

Success = True

Catch e As Exception
	Success = False
	Message = e.Message
Finally
	wb = Nothing
	ws = Nothing
	excel = Nothing
	sheet = Nothing
	range = Nothing
End Try

You should have your workflow ready as shown below:



Now, publish your action and then use all the actions of the same business object where you have done the changes in your process studio while interacting with the excel file in order to avoid any exceptions. I have a sample process studio workflow shown below:



Here, I am creating an excel instance, then opening my excel file, then making it visible on the screen, activating the worksheet and then I use my new created action which has the below parameters:



Here, I have an excel file with 1 lac rows such that my range starts from "A1" and I need to remove rows where the value of the cells in second column are as "N/A".

Now, before running this action my file looks like this:


After executing the action, you can observe below that the rows has been deleted which satisfy our criteria as we only have 15,943 rows left:



NOTE: So just by providing the appropriate values you can delete rows from a large excel file as well. Just remember that you must use all the actions for excel belonging to same VBO where you are making changes in your workflow otherwise you will face bad handle exception


------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------