If you are working with an on-premise installation of Enterprise Studio and you do not have the Excel application available on your computer, you can import and export Excel worksheets using an ADOdb connection. To facilitate the data exchange between Enterprise Studio and Excel workbooks, the Microsoft Access Database Engine 2016 Redistributable ODBC driver needs to be installed. You can download it from the Microsoft website. After you have installed the driver, you should be able to import and export Excel worksheets.
If you do have Excel installed, but still prefer to use an ADOdb connection, you also need to activate the Force use of ADOdb property of the Excel connection after installation of the driver. Do this as follows:
- In the model browser, select the Excel connection and then go to the connection's Properties window.
- Select the property Force use of ADOdb, and then click the red cross next to the property, or press Enter. If a check mark appears, the property is activated:
If you are working with Enterprise Studio Online (the hosted solution), an ADOdb connection is already automatically used for the Excel import and export.
The use of ADOdb comes with some limitations. Please take note of them before you start:
- Cell A1 of an imported worksheet must contain some (dummy) contents.
- The title of a worksheet must not contain spaces.
- The contents of the top cell of all (used) columns must be unique, meaning they need unique column names.
- The number of columns should not change when exporting to the same Excel file.
- The connection property Feedback import cannot be used.
- The number of columns is limited to 255 (in theory). In practice 100 columns seems to be the maximum. This is especially relevant for cross-relation tables: the size of the population that is mapped onto the "column dimension" should not exceed (around) 100.
- When exporting, text values are limited to 255 characters. For example, texts from a documentation field cannot contain more than 255 characters. If this limit is exceeded, a warning is shown and the text will be truncated to 255 characters.
In some cases the installation of the ODBC driver fails because of conflicts with Office versions installed. The 32 bit installer refuses because there are 64 bit Office components installed, and the 64 bit installer refuses because there are 32 bit Office components installed.
A solution that seems to work is to remove the 32 bit version of the "Office 16 Click-To-Run Extensibility Component" by performing the following procedure:
- Go to Start > Run (or Winkey+R).
- Type installer (that opens the %windir%installer folder), make sure all files are visible in Windows (Folder Settings).
- Add the column Subject (and make it at least 400 pixels wide).
- Right-click the column headers, click More, then find Subject.
- Sort on the column Subject, and scroll down until you locate the name mentioned in your error screen ("Office 15 Click-to-Run Extensibility Component").
- Right-click the MSI and choose uninstall.
Then try your installation again.
(Instructions are from the following website: https://www.tecklyfe.com/fix-for-microsoft-office-setup-error-please-uninstall-all-32-bit-office-programs-office-15-click-to-run-extensibility-component/)