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

[system]

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

Issue Description

To run queries for the data records, UiPath Studio requires using an Excel file as a database.

In this example, there is an *.xlsx file with these records as presented below.

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

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

2. 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).

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

4. Click on the Configure Connection button to see the below window.

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

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

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

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

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

10. 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.

11. Finally, click the Finish button.

12. 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 at the end.

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

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

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

15. All the above steps are used to connect Excel as a database; now execute the query from the Excel file by using the Run query activity.

• Here, click on the 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.

16. Convert the Datatable (out_ExcelDT) into a string value to display the results we extracted from the Excel file.

• Use the Output Data Table activity to achieve the needed results.

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

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

Results:

Error Message: "ERROR [HY000] [Microsoft][ODBC Excel Driver] Operation must use an updateable query"

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'"

Results of the workflow execution:

Root Cause

Excel is read-only by default: http://support.microsoft.com/kb/257819

Resolution

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 the project in order to apply it.

Example:

Results of this fix:

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');"

Before:

After:

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? - "ERROR [HY000] [Microsoft][ODBC Excel Driver] Deleting data in a linked table is not supported by this ISAM"

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:

Afterward, to eliminate 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:

How to solve "ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)' "

"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

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?

This is a known limitation of this database activity. To avoid 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.