Insert, Read or Update data in Excel Files

Introduction

Users can perform operations to Read/Insert/Update data in files through Leapwork with the help of the "Database" block. However, there are certain pre-requisites for using a Database block.

For pre-requisites, see article: Accessing Excel via ODBC driver in Leapwork.

The two input fields viz. Connection & Query are the mandatory fields and should be populated as below.

Connection

The "Data Source Name" of configured ODBC connection on the computer where the test case will run.

Query

The query statement will be executed on the excel file. It has a special syntax which is an ODBC-compliant SQL statement. The dynamic fields can be specified and used as Tokens while creating query statements.

Please refer to the below statements for different kinds of queries.

INSERT

INSERT INTO [Sheet1$] (Username, Password) VALUES ('Leapwork', 'Password')

Note: To insert data, the Excel sheet should have columns with the same name as specified in the query. e.g. Username and Password.

READ

SELECT * FROM [Sheet1$]

UPDATE

UPDATE [Sheet1$] SET [Password] = 'NewPassword' WHERE [Username] = 'Leapwork'

DELETE

Note: When trying to use the DELETE query via Database block, the error logged in Leapwork is: “ Deleting data in a linked table is not supported by this ISAM”.

“You are more restricted 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. You can delete values in fields (cells). See article http://support.microsoft.com/kb/257819.

A workaround would require using the UPDATE sql query to blank each cell with the Value NULL/Empty Text that met a certain condition.

For any clarification, contact our Priority Support.