How To Use xlslx File As A Database In UiPath And Perform SQL Queries?

News Knowledge Base
,
1

How to use xlslx file as a Database in UiPath and perform SQL queries?

Issue Description: It is required to use an Excel file as a Database in UiPath Studio in order to run queries for our data records.

Assuming there is an *.xlsx file with these records as presented below.


How to use the above Excel file as a Database in UiPath?

Resolution:

  1. Create a new process with Modern Design Experience enabled.

  1. In Manage Packages add the UiPath.Database.Activities[1.6.1] dependency -> Save (this version was the latest one at the creation time of this tutorial).

  1. From the Activities panel search for Connect to Database activity and drag it to your sequence.

  1. Click on the Configure Connection button and we would see the below window as shown.

  1. Click on the ‘Connection Wizard’ button and Choose the Data Source as and Data Provider as .NET Framework Data Provider for ODBC.

  1. Under Data Source Specification, Choose the Use Connection String check box and click on the Build... button.

  1. Select the tab Machine Data Source as shown in the below window.

  1. Now, click on the New... button.

  1. In the Create New Data Source window select User Data Source (Applies to this machine only) and then click Next.

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

As it is Excel, look for Microsoft Excel Driver (*.xls,*.xlsx,*xlsm,*xlsb) and Click Next.

  1. Finally, click the Finish button.

  1. Now, do ODBC Microsoft Excel Setup and for that, provide a Data Source Name of your choice, Description is optional, press on Select Workbook... from the specified location where the file is placed and click on the OK button in the end.

  1. Now click on the Test Connection button to test the connection. If the test connection succeeded, then click on OK button.

Observe, the Connection string will be generated and you can press now on the OK button.

  1. Save the connection in an output variable as a variable named for example ExcelDatabaseConnection.

  1. Above all the steps are used to connect Excel as a database, now you will execute the query from the Excel file, for which you will use now the Run query activity.

  • Here, click on Configure Connection... button and pass the existing connection variable i.e ExcelDatabaseConnection and then click the OK button.

  • Now, click on the Edit Query... button and write the query to extract the needed results from the Excel file.

Query example: "select Name,Email,Country from [EmployeeDetails$] where Email='john.cage@compay.com'"

  • Store the Output of Execute Query in the output variable as out_ExcelDT.

  1. Now convert the Datatable (out_ExcelDT) into a string value to display the results we extracted from the Excel file.
  • Use Output Data Table activity to achieve the needed results.

image.png

  • Create a new variable that will store the Text string value i.e. out_strExcelDT

image.png

  1. Finally use a Log Message activity to display the result in the Output console.

image.png


Results:

image.png


====

While trying to update a record in the Excel file using the Run command activity, the below error is thrown,

RemoteException wrapping System.Data.Odbc.OdbcException: ERROR [HY000] [Microsoft][ODBC Excel Driver] Operation must use an updateable query.
at UiPath.Database.Activities.ExecuteNonQuery.d__43.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at UiPath.Database.Activities.AsyncTaskCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)



Example:

Query: "UPDATE [EmployeeDetails$] SET Email = 'john.cage@company.com' WHERE Email='john.cage@compay.com'"

image.png


Results of the workflow execution:

image.png


Root Cause of the above error:


Error Resolution in order to update the record in the Excel file:

In this case, add an extra parameter (;ReadOnly=False) to the Connection string value. After performing this change, make sure to save project in order to apply it.

Example:

image.png


Results of this fix:

image.png


====


How to insert a record into the Excel file database?

Use a Run query activity and use this query: "INSERT INTO [EmployeeDetails$] (Name,Email,Country) VALUES ('Alexandru Bobita', 'alexandru.bobita@company.com', 'RO');"

image.png


Before:

image.png


After:

image.png


Note: If required to insert multiple records, run this activity multiple times with the needed VALUES in the query. The new records will be appended below the last record in the Table (spreadsheet).

====

Why is this error thrown when trying to Delete a record from an Excel database file?

Query: "DELETE FROM [EmployeeDetails$] WHERE Email = 'ian.morris@company.com'"

"RemoteException wrapping System.Data.Odbc.OdbcException: ERROR [HY000] [Microsoft][ODBC Excel Driver] Deleting data in a linked table is not supported by this ISAM.
at UiPath.Database.Activities.ExecuteNonQuery.d__43.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at UiPath.Database.Activities.AsyncTaskCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)"



Delete operation
There is more restriction in deleting Excel data than data from a relational data source. In a relational database, "row" has no meaning or existence apart from "record"; in an Excel worksheet, this is not true. Delete values in fields (cells).

However, it is not possible to:

  • Delete an entire record at once or you receive the following error message: Deleting data in a linked table is not supported by this ISAM. Delete only a record by blanking out the contents of each individual field.
  • Delete the value in a cell containing an Excel formula or receive the following error message: Operation is not allowed in this context.
  • Not possible to delete the empty spreadsheet row(s) in which the deleted data was located, and recordset will continue to display empty records corresponding to these empty rows.
Read the following Article Office Spacing Using ADO to Query an Excel Spreadsheet .


Is there a possibility to delete the record in this situation?

It is not possible to delete rows using OLEDB with Excel. The alternative solution is to UPDATE the whole row using the Run command activity, setting field='' for every single field of the row.

  • Query example: "UPDATE [EmployeeDetails$] SET Name='',Email='',Country='' WHERE Email = 'ian.morris@company.com'"

Result:

image.png


Afterward, to get ride-off those empty rows, simply use something like WHERE Email IS NOT NULL in your SELECT clauses.

  • Query example: "SELECT Name,Email,Country from [EmployeeDetails$] WHERE Email IS NOT NULL"


Results:

image.png


====

How to solve this encountered error?

"Connect to database: ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data.\r\nERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed\r\nERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data."

Root Cause: It is already opened exclusively by another user, or you need permission to view and write its data.

Resolution: At first, close the Excel file that should be used by the workflow process. After that, interact with it through the automation process.

====

Why does the UiPath Studio process not respond after choosing the Edit Connection Settings, and how to solve this issue?

image.png


This is a known limitation of this database activity. In order to not receive this behavior, configure the Database Connection and do not leave this window open for too much time without any activity in it.

If the Studio is not responding, go to Task Manager -> Processes and close all the UiPath Studio processes. Open a new UiPath Studio instance in order to continue the database workflow development.