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
UiPath Excel as Database 1.PNG

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”

UiPath Excel as Database 2UiPath Excel as Database 3

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.

UiPath Excel as Database 4UiPath Excel as Database 5

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

UiPath Excel as Database 6UiPath Excel as Database 7

Step 4:
Drag “Message Box” activity to display the final DataTable.

UiPath Excel as Database 8.PNG

Step 5:
Finally, run the process 🙂

UiPath Excel as Database 9.PNG

Note: If you have any trouble running the project, please try to install “AccessDatabaseEngine.exe 64bit or 32bit as per the requirement”

Click here to download the Source Code…

Hope it has helped you… 

Advertisements

Author: Sharath Raju

Founder@www.ExcelCult.com

Leave a Reply