Insert, Read or Update data in Excel Files

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, please refer to the 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”. 

The reason why you can not delete rows in an excel file via sql:

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

You can read the following Article http://support.microsoft.com/kb/257819

So 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, please contact our Priority Support.