Filter and Delete Rows in Excel Files

The example below explains how to delete unwanted rows from an .xlsx file and how to filter the information by applying specific criteria. It presents activities such as Remove Data Row, Read Range Workbook or Write Range. You can find these activities in the UiPath.Excel.Activities package.

The .xlsx file used in the example includes the following columns: Name, Km, Price, and SellerType. The automation process deletes all the rows that contain the value Other and Private Seller from the SellerType column and creates a new sheet with the filtered results.

This is how the automation process can be built:

1. Open Studio and create a new Process named by default Main.

   Note: Make sure to add the .xlsx file that you want to use in the project folder. You can also use the file we provided by downloading the example available at the bottom of this page.

2. Drag a Flowchart in the Workflow Designer.

   • Create the following variables:

     Variable Name	Variable Type	Default Value
     carsList	WorkbookApplication	N/A
     inputList	System.Data.DataTable	N/A

3. Drag an Excel Application Scope activity in the Flowchart and connect it to the Start Node.

   • Double click on the Excel Application Scope activity to open it.
   • In the Properties panel, add the name Delete Data in the DisplayName field.
   • Add the path of the .xlsx file in the WorkbookPath field, in this case, Cars.xlsx.
   • Select the check boxes for the AutoSave, CreateNewFile and Visible options. The robot is now allowed to create a new Excel workbook, to automatically save all the changes made to it and to open the Excel file in the foreground while performing actions on it.
   • Add the variable carsList in the Workbook field.

4. Select the Do sequence inside the Excel Application Scope activity and create the following variable:

   Variable Name	Variable Type	Default Value
   filterList	System.String[]	{"Private Seller", "Other")

5. Drag an Excel Read Range activity inside the Do sequence.

   • In the Properties panel, add the name "Cars" in the SheetName field.
   • Select the AddHeaders check box for considering the first row of the table a header.
   • Add the variable inputList in the DataTable field.

6. Place a For Each activity below the Read Range activity.

   • Add the value word in the ForEach field.
   • In the Properties panel, select the option String from the TypeArgument drop-down list and add the variable filterList in the Values field.

7. Select the Body container inside the For Each activity and create the following variables:

   Variable Name	Variable Type	Default Value
   query	String
   RowsToDelete	System.Data.DataRow[]
   deletedRows	Int32

8. Drag an Assign activity inside the Do container.

   • Add the variable query in the To field and the expression string.Format("[SellerType]='{0}'", word) in the Value field. This returns the matching values in the SellerType column and stores them in the query variable. The string.Format method is used to convert the DataRow objects that match the filter words to strings.

9. Drag another Assign activity and place it below the first one.

   • Add the variable RowsToDelete in the To field and the expression inputList.Select(query) in the Value field.

10. Add a third Assign activity under the first two Assign activities.

    • Add the variable deletedRows in the To field and the expression RowsToDelete.Count-1 in the Value field. This creates a counter for the rows that needs to be deleted.

11. Place a While activity under the Assign activities.

    • Insert the expression deletedRows>=0 in the Condition field. This means that the activities in the Body section are performed until the value of the variable deletedRows equals 0.

12. Drag a Remove Data Row activity to the Body section of the While activity.

    • Add the variable inputList in the DataTable field.
    • Add the expression RowsToDelete(deletedRows) in the Row field.

13. Place an Assign activity below the Remove Data Row activity.

    • Add the variable deletedRows in the To field and the expression deletedRows-1 in the Value field. This creates a loop that is executed until the value of the variable deletedRows equals 0.
14. Return to the Flowchart workflow.

15. Drag an Excel Application Scope activity and connect it to the previous Delete Data Excel Application Scope activity.

    • Double click on the Excel Application Scope activity to open it.
    • In the Properties panel, add the name Filter Data in the DisplayName field.
    • Select the check boxes for the AutoSave and CreateNewFile options. The robot is now allowed to create a new Excel workbook and to automatically save all the changes made to it.
    • Add the variable carsList in the Workbook field.

16. Drag a Close Application activity inside the Do container of the Excel Application Scope activity.

    • Open the .xlsx file that you are using for this automation. For this example is Cars.xlsx.
    • Click on the Options Menu button, select the Indicate on screen option and indicate the .xlsx file.

17. Drag an Excel Write Range activity below the Close Application activity.

    • In the Properties panel, add the name "Cars2" in the SheetName field.
    • Add the variable inputList in the DataTable field and the path "Cars.xlsx" in the WorkbookPath field.
    • Select the AddHeaders check box for considering the first row of the table a header.
18. Run the process. The automation process deletes the specified data and filters the information in a new workbook sheet.

Download example