Accessing Excel via ODBC driver in Leapwork

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.

Prerequisites:

ODBC driver with 64-bit version must be installed on your system (please install AccessDatabaseEngine_X64.exe from here).

Configure ODBC

1. Open 64-bit ODBC application in your system
   
2. Click on Add button (Make sure you choose System DSN, in case you want to run ODBC via agent/schedule)
   
3. Select "Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)" (as shown below) and click Finish.
    
4. Enter a logical name for ODBC data source,
   1. "Data Source Name" is used in the database block as "Connection" while accessing the database from Leapwork,
   2. Enter a meaningful description.
   
5. Click Select Workbook after filling above details
    Select Directory first then select the Excel file 
   
6. After selecting the excel file you will see the path in Workbook; User need to click on the options highlighted below
   
7. Untick "Read Only" highlighted below to make sure that Insert/Update/Delete operations can be performed in an Excel sheet. Press OK after untick.
8. 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.