Remove Blank Rows From Excel File

This bot is used to remove rows from an excel sheet if there are any empty cells in the row. The filtered excel data is then write to a new excel file. This is an easy and effective method to remove the rows and requires no manual efforts.

Work Flow

Working

• Open Excel File activity opens the input excel file which has the required data with specified configuration, creates an Excel Handle and stores it in the specified Excel handle variable.

• Get Workbook sheet activity get the sheet based on its index and returns its name as a string

• The robot will read the value of the excel sheet by using Read Sheet activity and stores it in a Data Table variable

• Close Excel file activity closes the specified excel file in open excel file activity.

• Apply Row Filter activity can remove the empty rows according to the filter conditions specified in the filter window. Each conditions need column name, operators, and values that are used to filter the data table from the read sheet activity. In the output we will get the filtered data table.

There is a dropdown with keep and remove values which decide the filtered records are kept or removed from the data table. Preview button displayed the filtered data table on the grid below the filter conditions. Clear button removes all filter conditions except on default conditions.

Configuration window

• Here we have to use open excel activity again in order to write the filtered data table to a new excel sheet. We can select create and open in the open mode in the property panel.

• Write range activity can be used to write the values from the data table to the specified range of the excel sheet. We have to specify the starting cell address and the sheet name in which the data is to be written.

INPUT

OUTPUT