Are you curious about connecting Excel as a database in UiPath.
Let’s see how to do that.
Drag the connect activity in the main sequence workflow.
Click on ‘Configure Connection’ button and we would see the below window as shown.
Click on ‘Connection Wizard’ button and Choose the Data Source as < other> & Data Provider as .NET Framework Data Provider for ODBC & click ok button.
Once you click on OK button, then set the properties on the Connection string window.
Under Data Source Specification, Choose the ‘Use Connection String’ icon & Click on Build button.
Select the tab ‘Machine Data Source’ as shown in the below window.
Now, click on New button.
Once you click on New button, then we would see the below image, ignore the image by clicking on Ok button.
Once you click on ok button, then on below window click Next button.
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.
Finally ,we click on Finish button.
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.
Now click on ‘Test Connection’ button to test the connection. If the test connection succeeded , then click on ok button.
We will save the connection in the output variable as ‘ConnectionString’
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.
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.
“select Name from [Sheet1$] where Name=’John’”
Excel file detail-
Store the Output of Execute Query in the output variable as DTOutput.
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.
Finally,we will use the Message box to display the result.