Manage Databases in Excel

The example below explains how to read a database, create a new database and filter it based on the results. It presents activities such as Read Range, Create Table, Insert Column, Build Data Table, and Add Data Row. You can find these activities in the UiPath.Excel.Activities package.

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 have the .xlsx file inside the project folder. You can use your own .xlsx file or download the example we have provided at the bottom of this page. The .xlsx file is used for retrieving and processing data to generate a new database.

2. Drag a Sequence container in the Workflow Designer.

   • Create the following variables:

     Variable Name	Variable Type	Default Value
     excelFile	String	"Exam results.xlsx"
     resultsDt	System.Data.DataTable	N/A
     studentGradesDt	System.Data.DataTable	N/A
     passedStudents	Int32	0
     failedStudents	Int32	0
     statisticsDt	System.Data.DataTable	N/A

3. Drag an Excel Application Scope inside the sequence.

   • In the Properties panel, add the value excelFile in the WorkbookPath 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.

4. Place an Excel Read Range activity inside the Do sequence of the Excel Application Scope activity.

   • In the Properties panel, add the value "Sheet1" in the SheetName field and the variable studentGradesDt in the DataTable output field.
   • Select the check box for the AddHeaders options for considering the first row as header.

5. Drag a Create Table activity under the Read Range activity.

   • In the Properties panel, add the value "Sheet1!$A$1:$C$11" in the Range field, the name "Grades" in the TableName field and the name "Sheet1" in the SheetName input field.

6. Add an Insert Column activity below the Create Table activity.

   • Configure the following settings in the Properties panel: add the name "Result" in the ColumnName field, add the name "Sheet1" in the SheetName field and the name "Grades" in the TableName field.

7. Place a Build Data Table activity under the Insert Column activity.

   • Add the variable resultsDt in the DataTable output field.
   • Click on the DataTable button, inside the body of the Build Data Table activity.
   • Click on the Edit Column button and add the name Result in the ColumnName field.
   • Set the Data Type as String.
   • Select the Allow Null check box to allow the robot to take into consideration the null values.
   • Add the value 100 in the Max Length field.
   • Select the OK option for closing the window.
   • Select the OK button again to close the window.

8. Drag a For Each Row activity below the Build Data Table activity.

   • In the Properties panel, add the variable studentGradesDt in the DataTable field.

9. Add an If condition inside the body of the For Each Row activity.

   • Insert the value cint(row("Grade")) < 5 in the Condition field. This formula analyses the data available in the row Grade from the .xlsx file and decides if the value is smaller or bigger than 5.

10. Drag two new Sequences, one in the Then and one in the Else fields.

    • Name the sequence from the Then area Failed and the sequence from the Else area, Passed.

11. Place an Add Data Row activity inside the Failed sequence.

    • In the Properties panel, add the the condition { "Failed" } in the ArrayRow field and the variable resultsDt in the DataTable field.

12. Place an Assign activity below the Add Data Row activity.

    • Add the variable failedStudents in the To field and the condition failedStudents + 1 in the Value field.

13. Place an Add Data Row activity inside the Passed sequence.

    • In the Properties panel, add the the condition { "Passed" } in the ArrayRow field and the variable resultsDt in the DataTable field.

14. Place an Assign activity below the Add Data Row activity.

    • Add the variable passedStudents in the To field and the condition passedStudents + 1 in the Value field.

15. Place a Build Data Table activity under the For Each Row activity.

    • In the Properties panel, add the variable statisticsDt in the DataTable output field.
    • Click on the DataTable button, inside the body of the Build Data Table activity.
    • Click on the Edit Column button from the left side and add the name Failed in the ColumnName field.
    • Set the Data Type as Int32.
    • Select the check box for the Allow Null option for allowing the robot to take into consideration the null values.
    • Select the OK option for closing the window.
    • Select again the OK button to close the window.
    • Click on the Edit Column button from the right side and add the name Passed in the ColumnName field.
    • Set the Data Type as Int32.
    • Select the check box for the Allow Null option for allowing the robot to take into consideration the null values.
    • Select the OK option for closing the window.
    • Select again the OK button to close the window.

16. Add an Assign activity below the Build Data Table activity.

    • Insert the condition statisticsDt.Rows(0)("Failed") in the To field and the variable failedStudents in the Value field.

17. Add another Assign activity below the previous one.

    • Insert the condition statisticsDt.Rows(0)("Passed") in the To field and the variable passedStudents in the Value field.

18. Place an Excel Write Range activity underneath the Assign activity.

    • In the Properties panel, add the name "Sheet1" in the SheetName field, the value "D1" in the StartingCell field and the variable resultsDt in the DataTable field.

19. Add a new Excel Write Range activity and place in below the first one.

    • In the Properties panel, add the name "Sheet2" in the SheetName field, the value "A1" in the StartingCell field and the value new DataTable() in the DataTable field.

20. Drag another Excel Write Range activity below the previous one.

    • In the Properties panel, add the name "Sheet2" in the SheetName field, the value "A1" in the StartingCell field and the variable statisticsDt in the DataTable field.
    • Select the AddHeaders check box for the robot to consider the first row as header.
21. Run the process. The robot reads the .xlsx file, analyzes the data, creates a new database and reorders the results as specified.

Download example