Introduction
This document provides an in-depth guide on how to establish connections between your FactoryStudio project and Microsoft Excel files. Excel is a widely used spreadsheet application, and integrating data from Excel files can greatly enhance the flexibility and efficiency of your project. The ability to connect to Excel files allows for seamless data exchange and collaboration between your project and various data sources.
This guide will cover various methods for connecting FactoryStudio with Excel files, including the use of ODBC (Open Database Connectivity), the ODBC Microsoft Excel Driver, configuring ODBC with a Data Source Name (DSN), and utilizing OleDB. Each of these techniques offers unique advantages and capabilities, ensuring that you can find the most suitable approach for your specific needs.
On this page:
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 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...
For Microsoft Office 2007:
Right-click the selection and choose Name a Range....
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.
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 steps:
- 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.
- 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.
"Test" is optional.
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 steps:
- Go to your computer's Control Panel and select Administrative Tools.
- Double-click Data Sources (ODBC).
- In the "ODBC Data Source Administrator" window, click Add. You are prompted to select a driver.
- Select "Microsoft Excel Driver (*.xls)".
- Click Select Workbook and select the name of the Excel file that was previously created.
- Name the Data Source, (e.g., "excelDatasource").
- For Write access, uncheck the "ReadOnly" checkbox.
- In the Datasets namespace, choose the DBs tab and create a new provider by clicking create new.
- Under the "Odbc Data Provider" options, choose "ODBC using DSN". Then, click OK.
- Click the ConnectionString column of the new row. Then, enter the DSN in the "DSN" field.
Using OleDB
OleDB enables a higher level of abstraction and flexibility when connecting to data sources, supporting a wider range of data access technologies and file formats.
To connect using this method, follow these steps:
- In the Datasets namespace, choose the DBs tab.
- Select the "OleDb data provider" option in the combo-box and create a new provider by clicking create new.
- Choose the "Microsoft Excel Database". Then, click OK.
- Click the ConnectionString column of the new row, then enter the path and the name of the Excel (.xls) file in the "DataSource" field.
In this section...