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 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 excel file you will see the path in Workbook; User need to click on options highlighted below
- Untick "Read Only" highlighted below to make sure that Insert/Update/Delete operations can be performed in Excel sheet. Press OK after untick.
- Press OK in 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 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.