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:
The "Data Source Name" of configured ODBC connection on the computer where the test case will run.
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 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.
SELECT * FROM [Sheet1$]
UPDATE [Sheet1$] SET [Password] = 'NewPassword' WHERE [Username] = 'Leaptest'
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.