How To Use Excel As Database - In UiPath

Are you curious about connecting excel as a database in UiPath?

Let’s see how to do that!!!

***For .XLS

ConnectionString : “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ExcelPath & “;Extended Properties=’Excel 8.0 Xml;HDR=YES;IMEX=1′;”

ProviderName :”System.Data.OleDb”

***For .XLSX

ConnectionString : “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ExcelPath & “;Extended Properties=’Excel 12.0 Xml;HDR=YES;IMEX=1′;”

ProviderName :”System.Data.OleDb”

Note:
ExcelPath is the Worksheet path i.e “C:\Desktop\UiPath\ExcelAsDatabase_Example\SampleData.xlsx”

Example

Implementation using UiPath :

Let’s quickly implement a process which takes a sample excel file and displays the result obtained from the SQL query.

Sample Excel File Data

Step 1:
Drag “Connect” activity into the designer panel and supply the connection string as shown below.

Connection String: “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ExcelPath & “;Extended Properties=’Excel 12.0 Xml;HDR=YES;IMEX=1′;”

Where, ExcelPath is your variable of string type containing the Excel File Path.

Provider: “System.Data.OleDb”

Step 2:
Drag “Execute Query” activity into the designer panel and supply the connection string variable created in the above step, then pass the SQL query as shown below.

SQL: “select * from [Sheet1$] where Name=’Sharath'”

Note: We are applying the filter to take out the rows with the name “Sharath” in them.

Step 3:
Drag “Output DataTable” activity into the designer panel to convert DataTable to string datatype(For the purpose of displaying the resultant DataTable)