Insert, Read or Update data in Excel Files

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

For pre-requisites, please refer 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 that will be executed on the excel file. It has a special syntax which is ODBC-compliant SQL statement. The dynamic fields can be specified and used as Tokens while creating query statements.

Please refer below statements for different kind of queries.

INSERT

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

Note: To insert data, 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] = 'Leaptest'

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