How to use excel sheet data as a database in UiPath

A lot of times while automating a business process we will use excel as an input and read data from it and update it.

While doing that we have an option to convert that excel sheet data as a database and use it in our process automation.

In this article, let’s see how to convert excel sheet data as a database in UiPath.

Let’s get into the practical implementation of this task:

Step1: Make sure the excel file needs to be converted as a database is available.

Step2: Install the UiPath.Database.Activities from Manage Packages as shown below:

Step3: Drag and Drop Connect activity from the activities panel to the designer panel as shown below:

The required properties are shown below:

Here:

• ConnectionString – contains the information that the provider need to know to be able to establish a connection to the database or the data file.
• ProviderName – It is the invariant name of the . NET Framework data provider, which is registered in the machine config file.
• SecureConnectionString – Pass the connection string in secure string variable type.
• DatabaseConnection – It will store the database connection in a DatabaseConnection variable.

The connection string is shown below:

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Aditya\Documents\UiPath\Demo\Data.xlsx; Extended Properties='Excel 12.0 XML;HDR=YES;ReadOnly=False'

The provider name is shown below:

System.Data.OleDb

With the above steps, you will be able to make excel as a database and get the connection in a DatabaseConnection variable.

If you want to retrieve any values from the database then follow the steps shown below:

Step4: Drag and Drop Execute Query activity from the actions panel to the designer panel as shown below:

The required parameters are shown below:

The SQL query is shown below:

Select *From [Sheet1$] where Date='03/04/20'

This query will provide us the rows which are matching with the date =’03/04/20′ in Sheet1 of excel.

If your data is on a different sheet change the sheet name in the query.

The output of the above query is shown below:

Happy Learning!