Users can Read, Insert or Update data in Excel through Leapwork using ODBC driver. Below are the steps to configure ODBC Driver for Excel Database.
Note: Please note that Leapwork Studio, ODBC Driver, and Microsoft Excel all 3 software should run under the same bit version. i.e if you have 64-bit MS Office installed, please install ODBC Driver and Leapwork also of the 64-bit version. And if MS Office is 32-bit, install the 32-bit version of ODBC Driver and Leapwork.
ODBC driver with 64-bit version must be installed on your system (please install AccessDatabaseEngine_X64.exe from here).
- Open 64-bit ODBC application in your system
- Click on Add button (Make sure you choose System DSN, in case you want to run ODBC via agent/schedule)
- Select "Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)" (as shown below) and click Finish.
- Enter a logical name for ODBC data source,
- "Data Source Name" is used in the database block as "Connection" while accessing the database from Leapwork,
- Enter a meaningful description.
- Click Select Workbook after filling above details
Select Directory first then select the Excel file
- After selecting the excel file you will see the path in Workbook; User need to click on the options highlighted below
- Untick "Read Only" highlighted below to make sure that Insert/Update/Delete operations can be performed in an Excel sheet. Press OK after untick.
- Press OK in the ODBC screen to finalize the settings.
Note: If you choose this option then "Connection" in Leapwork database block should be defined as “ExcelDataSourceName” (as defined in step no 4 above. Please remember there should be No spaces in, before, and after connection string).
Note: Please refer to the article Insert, Read or Update data in Excel Files for understanding query syntax and building block usage.
For any clarification, please contact our Priority Support.