Overview

This guide will cover various methods for connecting the platform with Excel files, including using:

  • ODBC (Open Database Connectivity)
  • ODBC Microsoft Excel Driver
  • Configuring ODBC with a Data Source Name (DSN)
  • Utilizing OleDB

Each technique offers unique advantages and capabilities. Thus, you can choose the one most suitable approach for your specific needs.

On this page:

Using ODBC

This option offers a standardized and widely supported method for connecting various applications to databases. As a result, it ensurs compatibility across diverse data sources. In the following sections you find guides to connect to different Microsoft Office versions.

Microsoft Office 365

To connect to a datasheet in Office 365, follow the steps below:

  1. Select the the cells to use as data.
  2. Access Formulas → Define Name.
  3. Select Define Name.
  4. Define a name for the selection. The Excel file will be ready to use. 

Microsoft Office 2007

To connect to a datasheet in Office 2007, follow the steps below:

  1. Select the the cells to use as data.
  2. Right click the selection and choose Name a Range.
  3. Define a name for the selection. The Excel file will be ready to use.


Microsoft Office 2003

To connect to a datasheet in Office 2003, follow the steps below:

  1. Select the the cells to use as data.
  2. Access Insert → Name.
  3. Select Define.
  4. Define a name for the selection. The Excel file will be ready to use.



Using ODBC With A DSN

Using ODBC with a DSN allows for centralized management and easier maintenance of data source connections, simplifying the configuration process. To connect using this method, follow the steps below.

  1. Access your computer's Control Panel and search for ODBC Data Source Administrator.
  2. In the "ODBC Data Source Administrator" window, click Add.
  3. Select Microsoft Excel Driver (*.xls).
  4. Click Select Workbook and choose the Excel file that was previously created.
  5. Name the Data Source, For example excelDatasource (For Write access, uncheck the Read Only checkbox).
    ODBC Data Source Administrator options
  6. Now back in the platform, access Datasets → DBs.
  7. When creating a new DB choose ODBC Data Provider as ProviderODBC using DSN.
  8. Click Ok

  9. In the data grid click the Connection String column of the newly created row.
  10. Enter the DSN (Data Source Name) in the DSN field


Using OleDB

OleDB enables higher abstraction and flexibility when connecting to data sources, supporting a more comprehensive range of data access technologies and file formats. To connect using this method, follow the steps below.

  1. When creating a new DB choose ODBC Data Provider as Provider.
  2. Choose the Microsoft Excel Database.
  3. Click OK.
  4. In the data grid click the Connection String column of the newly created row.
  5. Enter the path and the name of the Excel (.xls) file in the Data Source field. 

Using ODBC Microsoft Excel Driver

The ODBC Microsoft Excel Driver provides a dedicated driver for Excel to ensure optimal performance and seamless integration. To connect using this method, follow the steps below:

  1. In the platform, access Datasets → DBs.
  2. Click the plus icon.
  3. Fill in the Name field
  4. In the Provider field, select Odbc Data Provider.
  5. Choose Microsoft Excel Database in the Database field. 
  6. Click OK

  7. After returning to the data grid, click the Connection String column related to the newly created row.
  8. Enter the path to the file in the Dbq field. Use the Test button to ensure the connection is correctly configured.


In this section...