This step-by-step guide describes how to setup the ODBC connector in order to work with Microsoft Excel.
Prerequisites
In order to use the ODBC connector with Excel it is necessary to download the latest ODBC Office version. The latest version can be found under https://www.microsoft.com/en-US/download/details.aspx?id=13255.
On the page, click on the Download button. The following menu will be shown:
Select AccessDatabaseEngine_X64.exe and click on the Next button. After downloading the file, install the AccessDatabaseEngine in order to get the latest drivers.
Open the Start menu and search for ODBC. Select the ODBC Data Sources (64-bit).
In order to create a new ODBC User data source for usage with the ODBC connector click on the Add... button while in the User DSN tab.
From the list of drivers, select the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) and click on the Finish button.
Enter a Data Source Name and ensure that Excel 12.0 is selected. Click on Select Workbook... in order to select the target Excel file.
In the Select Workbook dialog, go to the directory where the target file is located and select the file from the field located below the Database Name field. Click on the OK button. After returning to the ODBC Microsoft Excel Setup dialog, click on the OK button.
The newly created data source will now be available for selection. Select the data source and click on the Add... button to finish the setup.
Setup of the ODBC Connector in Data Hub
In the Tasks section of Data Hub, click on the + Add button. The Add task dialog will be opened.
In the General tab, enter a name for the Task into the NAME field and a name for the dataset into the DATASET NAME field. Select ODBC as CONNECTOR type and switch to the Configuration tab.
In the Configuration tab, enter the query that is to be executed on the data source. One sheet of an Excel file represents a table. In order to target a sheet, the following syntax needs to be used: [NameOfTheSheet$]. After entering the query, go to the Connection tab.
Enter the full connection string into the CONNECTION STRING field. There are two ways to create the ODBC connection string.
1. Set the DSN name to the name that was defined in the ODBC Driver view.
Example:
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DSN=Excel ODBC DSN
2. Use the path to the Excel file as connection string.
Example:
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBO=C:\Users\Administrator\Documents\ODBC\Example.xlsx
After entering the connectin string, switch to the Agent settings tab.
Enter the agent that will be used into the AGENT tab and click on the Add button.