Sort Data in Excel Files

The example below explains how an .xlsx table can be processed to display the data presented in all rows in ascending order, without any duplicates.

It presents activities such as Remove Data Row, Read Range or Sort Table. You can find these activities in the UiPath.Excel.Activities package.

The example below uses a table comprising five columns - id, first-name, last-name, email, and gender. The entries in the table are arranged in descending order. Some of the rows appear several times along the spreadsheet. Using an automation process, data is sorted in ascending order and all duplicates are removed.

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
   existingColumns	WorkbookApplication	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 Sorting rows ascending in the DisplayName field.
   • Add the path of the .xlsx file in the WorkbookPath field, in this case, Book.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 existingColumns 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
   descendingOrder	System.Data.DataTable	N/A

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

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

6. Place a Sort Table activity under the Read Range activity.

   • In the Properties panel, add the name "id" in the ColumnName field.
   • Select Ascending from the Order list.
   • Add the name "Sheet1" in the SheetName field.
   • Add the name "Table1" in the TableName field.

7. Return to the Flowchart workflow.

8. Drag an Excel Application Scope activity and connect it to the previous Excel Application Scope activity.

   • Double click on the Excel Application Scope activity to open it.
   • In the Properties panel, add the name Deleting duplicates in the DisplayName field.
   • 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 existingColumns in the Workbook field.

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

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

10. Place a While activity below the Read Range activity.

    • Create the following variable:

    Variable Name	Variable Type	Default Value
    rowIndex	Int32	0

    • Add the expression rowIndex+1 < descendingOrder.Rows.Count in the Condition field. This expression process the entire table and enables you to perform the While loop until the row index +1 is smaller than the total number of rows.

11. Add a Sequence container in the body of the While activity.

    • Create the following variables:

    Variable Name	Variable Type	Default Value
    row	System.Data.DataRow	N/A
    rowNext	System.Data.DataRow	N/A

12. Place an Assign activity inside the previously created Sequence container.

    • Add the variable row in the To field and the expression descendingOrder.Rows(rowIndex) in the Value field. This enables you to assign the information contained in a row to the row variable according to its index.

13. Drag another Assign activity and place it below the previous one.

    • Add the variable rowNext in the To field and the expression descendingOrder.Rows(rowIndex+1) in the Value field. This assigns the value of the following row to the rowNext variable, and thus helps you iterate through all the table.

14. Add an If activity under the Assign activity.

    • Add the expression row(0).ToString=rowNext(0).Tostring in the Condition field. This condition checks if the data contained in the row variable is identical to the one in the rowNext variable.

15. Drag a Remove Data Row activity in the Then field of the If activity.

    • Add the variable descendingOrder in the DataTable field and the variable rowNext in the Row field.

16. Place an Assign activity in the Else field of the If activity.

    • Add the variable rowIndex in the To field and the expression rowIndex+1 in the Value field.

17. Add a Write Range activity below the While activity.

    • Add the name "Sheet2" in the SheetName field, the value "A1" in the StartingCell field and the variable descendingOrder in the DataTable field.
    • Select the AddHeaders check box for considering the first row of the table a header.

18. Run the process. The automation process creates a new sheet that displays the data in ascending order without any duplicates.

Download example