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:
Table of Contents |
---|
|
Using ODBC
This option offers a standardized and widely supported method for connecting various applications to databases. As a result, it ensures 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:
- Select the the cells to use as data.
- Access Formulas → Define Name.
- Select Define Name.
- 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:
- Select the the cells to use as data.
- Right
Accessing Microsoft Excel
You can connect to Excel databases using an ODBC driver, an ODBC DSN, or OleDB.
Using ODBC
Select and name a range of rows and columns in the worksheet. This will allow the software to read the information as a table. Choose one of the following naming processes for your version of Microsoft Excel:
For Microsoft Office 2007:
Right-- click the selection and choose Name a Range.
- Define a name for the selection. The Excel file will be ready to use.
For
Microsoft Office 2003
In Microsoft Excel, go to Insert → Name → Define....
Name the selection (e.g., "itemsTable"). The Excel file is ready to be used.
There are several methods for communicating using ODBC:
Using ODBC Microsoft Excel Driver
- In the datasets namespace, choose the DBs tab and create a new provider by clicking Create new.
- Select the "Odbc Data Provider" in the "Provider" data field.
- For the "Database" field, choose Microsoft Excel Database.
- Click Ok.
To connect to a datasheet in Office 2003, follow the steps below:
- Select the the cells to use as data.
- Access Insert → Name.
- Select Define.
- Define a name for the selection. The Excel file will be ready to use.
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:
- In the platform, access Datasets → DBs.
- Click the plus icon.
- Fill in the Name field
- In the Provider field, select Odbc Data Provider.
- Choose Microsoft Excel Database in the Database field.
- Click OK
- After returning to
A new row is created in
- the data grid, click the
- Connection String column related to the newly created row.
- Enter the path to the file in the Dbq field. Use the Test button to ensure the connection is correctly configured.
ODBC With A DSN
Note |
---|
"Test" is optional. |
Using ODBC with a DSN
Go toallows for centralized management and easier maintenance of data source connections, simplifying the configuration process. To connect using this method, follow the steps below.
- Access your computer's Control Panel and
- search for ODBC Data Source Administrator.
- In the
- ODBC Data Source Administrator
- window, click Add.
- Select
- Microsoft Excel Driver (*.xls)
- .
- Click Select Workbook and
- choose the Excel file that was previously created.
- Name the Data Source
- .
- For Write access, uncheck the
- Read Only checkbox.
- Back into the
- platform, access Datasets → DBs.
- Create a new DB choosing ODBC Data Provider as Provider. For Database,select ODBC using DSN.
- Click OK.
- After returning to the data grid, click the Connection String column related to the newly created row.
- Enter the DSN (Data Source Name) in the DSN field.
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:
- Access Datasets → DBs and click the plus icon.
- Select ODBC Data Provider as Provider and choose the Microsoft Excel Database as Database.
- Click OK.
- After returning to the data grid, click the Connection String column related to the newly created row.
- Enter
Using OleDB
- the path and the name of the Excel (.xls) file to be used as the database in the
- Data Source field.
In this section...
Page Tree | ||||
---|---|---|---|---|
|