How to use Excel as a Database in UiPath

Are you curious about connecting Excel as a database in UiPath.

Let’s see how to do that.
Step 1-

Drag the connect activity in the main sequence workflow.
image

Step 2:-

Click on ‘Configure Connection’ button and we would see the below window as shown.
image

Step 3:-

Click on ‘Connection Wizard’ button and Choose the Data Source as < other> & Data Provider as .NET Framework Data Provider for ODBC & click ok button.

image

Once you click on OK button, then set the properties on the Connection string window.

Step 4:-

Under Data Source Specification, Choose the ‘Use Connection String’ icon & Click on Build button.
image

Step 5:-

Select the tab ‘Machine Data Source’ as shown in the below window.
image

Step 6:-

Now, click on New button.
image

Step 7:-

Once you click on New button, then we would see the below image, ignore the image by clicking on Ok button.

image

Step 8:-

Once you click on ok button, then on below window click Next button.

image

Step 9:-

Now, select a driver for which you want to set up a data source.

As we are currently dealing with Excel, so we will look excel drivers and Click Next.
image

Step 10:-

Finally ,we click on Finish button.

Step 11:-

Now, we will do ODBC Microsoft Excel setup and for that we have to provide Data Source Name of your choice , description is optional and select the workbook from the specify location where the file is placed and click on ok button.

image
image

Step 12:-

Now click on ‘Test Connection’ button to test the connection. If the test connection succeeded , then click on ok button.

image

Step 13:-

We will save the connection in the output variable as ‘ConnectionString’

image

Step 14:-

Above all the steps are used to connect Excel as database , now we will execute the query from excel , for which we will use ‘Execute Query’ activity.

image

here, we will click on Configure connection button and pass the existing connection variable i.e ‘ConnectionString’.

Now, click on Edit Query button and write the query to extract the result from excel.

image

Query-

“select Name from [Sheet1$] where Name=’John’”

Excel file detail-
image

Store the Output of Execute Query in the output variable as DTOutput.

image

Step 15:-

Now we will convert the Datatable (DTOutput) into string to display the results we extracted from excel.

We will use Output datatable activity to achieve the result.

image

image

Step 16:-

Finally,we will use the Message box to display the result.

6 Likes

Hey @Nitya1

If you can attach the xaml file as well please will be helpful.

Thanks
#nK

Yes, sure I will do that.

for Step 4, refer this ss in UiPath latest version, click on Build button:

image

Hey @Rohit_More

You mean Build or Test connection please ?

Thanks
#nK

Hello @Nithinkrishna,

In the new version of UiPath studio, option is changed as per attached screenshot which you explained in step 4.

Regards,
Rohit

I built the code in the UiPath version 19.10 .