You can connect Excel file data sources in your corporate network to native SharePoint lists in the cloud using the Cloud Connector add-on and an installed OLEDB Excel driver with the following connection configuration file:
<?xml version="1.0" encoding="utf-8" ?>
<connection
interval="60" version="1.2" enabled="true"
>
<dataEntities>
<dataEntity name="ExcelSource" type="source" provider="System.Data.OleDb"
connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\ProgramData\Layer2 Cloud Connector\Sources\Customers.xlsx; Extended properties='Excel 12.0 Xml;HDR=YES';"
selectStatement="Select * from [Sheet1$]"
primaryKey="CustomerID">
</dataEntity>
<dataEntity name="SharePointDestination"
type="destination"
provider="Layer2.SharePoint.Provider"
connectionString="Data Source=https://mydomain.sharepoint.com/sites/cloudconnector/; Initial Catalog=Excel; Authentication=Office365; User Id=myname@mydomain.onmicrosoft.com; Password=***;"
selectStatement="">
</dataEntity>
</dataEntities>
<fieldMappings autoMapping="false">
<fieldMapping>
<field entity="ExcelSource" name="CustomerID"/>
<field entity="SharePointDestination" name="CustomerID" />
</fieldMapping>
<fieldMapping>
<field entity="ExcelSource" name="Name"/>
<field entity="SharePointDestination" name="Title" />
</fieldMapping>
<fieldMapping>
<field entity="ExcelSource" name="Contact"/>
<field entity="SharePointDestination" name="Contact" />
</fieldMapping>
</fieldMappings>
</connection>
Please note:
- With Microsoft Office 2010 installed no additional driver required.
- The driver must be 64-Bit on 64-Bit operating system. Otherwise "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" comes up.
- If required, you will find a driver here.
- [Sheet1$] is the Excel page, that contains the data. In German use [Tabelle1$]. Please take a look at your Excel page label to adapt.
- You have to set a primary key to connect and update, CustomerID (an Excel column name) in this sample. The driver not returns the primary key automatically.
- You have to map this primary key to any column in the SharePoint list (CustomerID in this case).
- You can assign any column in Excel to the Title column in the SharePoint list, the "Name" column in this sample.
- The update interval for the cloud data is set to 60 minutes in this sample.
Fig.: The Data Source is an Excel Tablel with primary key "CustomerID".
Fig.: The Data Destination is a native SharePoint list in the Office 365 / SharePoint Online cloud or any other SharePoint 2010 installation.