Page History

Microsoft Excel Connections

Using ODBC

This method offers a standardized and widely supported method for connecting various applications to databases, ensuring compatibility across diverse data sources. To connect FactoryStudio See the sections below to learn how to connect the platform with an Excel file , first, 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 based on your version of Microsoft Excel:

For Microsoft Office 365:

Navigate to Formulas → Define Name → Define Name...

Image Removed

For Microsoft Office 2007:

in different Microsoft Excel 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. Choose Define Name
4. Name the selection (for example ItemsTable) and the Excel file is ready to be used. 

Image Added

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.

2. Name the selection (for example ItemsTable) and the Excel file is ready to be used. 

Image Modified

Microsoft Office 2003

In Microsoft Excel, go to Insert → Name → Define.... 

Image Removed

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. Choose Define.
4. Name the selection (for example ItemsTable) and the Excel file is ready to be used. 

Image Added

Using ODBC Microsoft Excel Driver

The ODBC Microsoft Excel Driver provides a dedicated driver for Excel, ensuring optimal performance and seamless integration with the Excel file format. To connect using this method, follow these the steps :

• • In the datasets namespace, choose the DBs tab and create a new provider by clicking 'New item...'.
  • Select the "ODBC Data Provider" in the "Provider" data field. 
  • For the "Database" field, choose Microsoft Excel Database.
  • Click Ok.

Image Removed

• • A new row is created in the data grid, click the ConnectionString column.
  • In the pop-up window, enter the path and the filename in the "Dbq" field.
  • Click the Test button to ensure that the connection is OK.

Image Removed

below.

1. 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 Microsof Excel Database in the Database field. 
6. Click OK
   Image Added
   
7. In the data grid click the Connection String column of the newly created row.
8. Enter the path and filename in the Dbq field.
   Image Added

Use the Test button to ensure the connection is correctly configured.

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 these the steps :below.

Image Removed

1. Access your computer's Control Panel and

1. search for ODBC Data Source Administrator.

1. In the "ODBC Data Source Administrator" window, click Add.

2. Select

1. Microsoft Excel Driver (*.xls)

1. .

2. Click Select Workbook and

1. choose the Excel file that was previously created.

2. Name the Data Source,

1. For example excelDatasource (For Write access, uncheck the

1. Read Only checkbox).

1. 
   Image Modified

1. Now back in the platform, access Datasets → DBs.
2. When creating a new DB choose ODBC Data Provider as ProviderODBC using DSN.
3. Click Ok
   Image Added
   
4. In the data grid click the Connection String column of the newly created row.
5. Enter the DSN (Data Source Name) in the DSN field

Image Added

Image Removed

Using OleDB

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

below.

1. When creating a new DB choose ODBC Data Provider as Provider.
2. Choose the

1. Microsoft Excel Database

1. .

2. Click OK.

1. In the data grid click the Connection String column of the

1. newly created row.
2. Enter the path and the name of the Excel (.xls) file in the

1. Data Source field. 
   

Image Removed

1. Image Added

In this section...