Excel Integration with SharePoint, Office 365 and 150+

Excel data can be integrated and synchronized codeless with various other data sources, on-premise or in the cloud, using the Layer2 Cloud Connector. Please take a look here for supported systems and applications.​​​​​

​To connect to Microsoft Office Excel the data source entity must be configured as follows in the Layer2 Cloud Connector Connection Manager:

Fig.: Sample configuration to sync Excel data codelss with various other data sources.

Excel Specific Configuration Settings

​
• Please select the OleDb Data Provider from the list of available data providers as data source. Refer to Microsoft.ACE.OLEDB.12.0 object later in connection string.
• A valid connection string looks like this: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFile.xlsx; Extended properties='Excel 12.0 Xml;HDR=YES';
• You need to have the Microsoft Access Database Engine 2010 Redistributable installed. The driver must fit to the Layer2 Cloud Connector installation (32-/64-bit architecture).
• A data query can look like this: select * from [products$]  where products is the Excel Tab.
• Please verify the select statement. Use the data preview to check out the result of your query.
• Primary Key: Include a field with unique values in your query and use it as a "primary key" (unique column value) for synchronization.
• Excel content from Microsoft SharePoint or Office 365 libraries can also be read - but with a very different method (will be documented soon). Please consult [email protected] to know more.

Microsoft Excel Integration - Known Issues and Workarounds

• File shares are generally supported, e.g. H:\myfile.xlxs or \\myserver\myshare\myfile.xlxs. Please check access right in this case. You will need write-rights as Excel lock-files are created.
• Note that the Microsoft data provider is read-only. No write-operations supported.
• In some cases the Microsoft Data Provider (MDAC) delivers wrong data types or cuts content. This is a known issue. The value that determines how many rows should be inspected to automatically derive the datatype from, is set in the registry. One way to avoid this issue is to change the registry value or to add a dummy-line at the top with appropriate data types and long enough data in all text fields. 

Step-by-Step Intros For Excel Integration Scenarios

​You can integrate and sync with various sources listed here. More information about specific scenarios:

• How to connect and sync Excel with SharePoint and Office 365

Can't find what you are looking for? Please contact [email protected] for specific requirements. 

Ready to go next steps?