Layer2 Cloud Connector  V5 for Office 365 and SharePoint

​The Cloud Connector for Microsoft Office 365 and SharePoint connects almost any on-premise data source, e.g. ODBC, OLEDB, OData, Microsoft .NET based providers, Files (Excel, XML, CSV), SQL databases like SQL Server, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Navision, SAP and many more directly to native SharePoint lists and libraries in the Microsoft Cloud - or any other Microsoft SharePoint on-premise installation - in just minutes without any programming. For any questions please use the FAQ Web Form or contact [email protected] directly.

How to sync local text / CSV data with native SharePoint lists?

Integrating external text / CSV data sources (e.g. exports from LOB applications) with native SharePoint lists (e.g. news, contacts, tasks or custom lists) in Microsoft Office 365, SharePoint Online or SharePoint on-premise can close many gaps using Microsoft SharePoint. Exporting text / CSV files on a regular base is helpful in some cases:

  • a direct connection to any LOB system via ODBC/OLEDB/ ODATA etc. is technically not possible,
  • it causes possible issues in terms of security or availability, 
  • generally available data providers are very expensive and of high complexity, e.g. needs programming (for example for SAP).

Please note: It's not only about displaying external data in SharePoint - it's about full integration with search, lookups, change notifications, workflows, enterprise metadata etc. Also it is not about bulk import.

These FAQ gives a step-by-step example how to do this.

Prepare your external text / CSV data source to integrate with Office 365

Let's take a sample text / CSV file. Think of it like an export from your LOB application, e.g. ERP, CRM, or something like this.

Office-365-SharePoint-Online-CSV-Integration-DataSource.png 
Fig.: The screen shows a local sample text / CSV -based document to connect with a native SharePoint list.


Please note:

  • Almost any LOB system should be able to expose data in a format like this using scripts.
  • The first line contains the field names in this sample. That could be helpful, but is not a requirement.
  • Data types are not known at this point. You can declare specific data types later on or work with standard conversions, the Cloud Connector provides automatically.
  • The data source should contain one or more fields that are unique (together) and can be used as a primary key to savely update items in the SharePoint list.
  • Generally the data file can be provided on a file share as well (not local computer). Please check access rights carefull in this case.

Setup your text / CSV to SharePoint connection

​Declare Data source as ODBC source:

You have to use ODBC to declare your local data source as follows:

Office-365-SharePoint-Online-CSV-ODBC-1.png

 

Fig: Create your text / CSV data source in ODBC Administrator as a System DSN

 

Please note:

  • Use 64-Bit ODBC Manager in case your Cloud Conenctor is 64-Bit (or 32-Bit ODBC Manager in case your Cloud Connector is 32-Bit)
  • Use the installed Microsoft Access Text Driver (or any other text driver)
  • Use System DSN (not User DSN)

 

Office-365-SharePoint-Online-CSV-ODBC-2.png 

 Fig.: Selecting options for text / CSV file processing

 

Please note:

  • You can optionally enable column name header, delimiter character, character format, data types, if required.

Setup Data Source:

 

Office-365-SharePoint-Online-CSV-Integration-Source.png
 Fig.: Refer to the ODBC DSN in the Cloud Connector Connection Manager.

 

Please note:

  • Select the ODBC Data Provider
  • Enter the DSN in the conenction string
  • Select your data in the data query
  • Enter a primary key (or combination of unique fields, e.g. Field1; Field2)
  • Verify all steps and take care about possibe error messages

Create Data Destination:

 

Office-365-SharePoint-Online-CSV-Integration-List.png 

 Fig: Create your SharePoint List manually and add columns required to keep the data

 
Please note:
  • You have to create your list manually in SharePoint
  • You can use all list types, e.g. News, tasks etc. - a Custom List in these sample.
  • Please add columns manually as required. Take care about data types.

Setup Data Destination:

 
Office-365-SharePoint-Online-CSV-Integration-Entity22.png

 Fig.: Connect to the SharePoint list using the Layer2 Data Provider for SharePoint.

 
Please note:
  • Select the Layer2 Data Provider for SharePoint (CSOM) to connect to the data destination
  • See documentation for connection string options.
  • No primary key information must be given (SharePoint item ID is used automatically).
  • Verify all options and take care about error messages

Setup Mapping Options:

 

Office-365-SharePoint-Online-CSV-Integration-Mapping.png
 

Fig.: Setup field / column mapping between data source and destination.

 
Please note:
  • You can use the Auto Mapping feature in case fields and columns are created with the same name.
  • Be careful about data types. Simple conversions are provided automatically.
  • Map some field to the SharePoint Title column as required by SharePoint

Verify the mapping and take care about warnings or error messages.

Run the connection:

You can now run the connection on-demand using the "Run Now" button in the Connection Manager and take a look at possible warnings or error messages in the log file. Also take a look at the result page in SharePoint.

 

Office-365-SharePoint-Online-CSV-Integration-10.png

 Fig.: SharePoint result page of the data destination - a native SharePoint list, automatically synced with data source.

  
Please note:
  • After first insert, please run connection again. There should be no changes with this run, if no source data was modified.
  • Selected items only are transferred with first insert with respect to the data query.
  • Changed items are transferred only on next updates. That means, you can setup change notifications per-email or workflows in the cloud without any restrictions.
  • You can add additional "SharePoint only" data, e.g. attachments or managed metadata. It will be kept while items are updated.
  • You can do anything you can generally do with a SharePoint list - without any restrictions (e.g. search).
  • Don't forget to setup the background update (via Windows service), to keep data current in the Connection Manager connection settings, e.g. with scheduled updates every 10 minute, per hour or daily, if required.

Next step: Download a FREE shareware edition of the connector

​For a complete Layer2 Data Provider for CSOM specification see here. You can download a free shareware edition of the Layer2 Cloud Connector for Office 365 and SharePoint after registration here.

Share this site on Xing
Share this site on Google+
Share this site on LinkedIn

Bringing the cloud a little closer: Very easy setup and a good selection of connector types available to integrate on-premise data and cloud based Sharepoint. Allows for some very interesting capabilities. Support is also quick to respond.

Contact
Frank Daske

Frank Daske

Business Development Manager
+49 (0) 40 28 41 12 - 30
[email protected]

Please note that the 011 exit code must be dialed first for all international calls made from the USA, Canada and Australia.

Related News

Layer2 now connects 100+ commonly used IT systems with Twitter via its Layer2 Cloud Connector

The Layer2 Cloud Connector is now able to connect Twitter to integrate data to a wide range of IT backend systems including Office 365, SharePoint, SQL, ERP/CRM.

New Microsoft Teams Connectors: Layer2 adds 100+ typically used backend systems

​The Layer2 Cloud Connector is now able to connect to Microsoft Teams to quickly integrate more than 100 IT backend systems including local SQL, ERP/CRM.
Related Downloads
PDF Image

Cloud Connector Instruction

Download this step-by-step instruction to connect and sync your corporate or cloud data sources bi-directional to any native SharePoint 2010/2013/2016 list or library, including Microsoft Office 365.
PDF Image

Feasibility Study: Connecting local SQL Server to SharePoint Online

The study compares different approaches for SharePoint Online data integration regarding performance, features, security and investment.
PDF Image

Flyer: Layer2 Partner Program

Partner with us as a software distributer, reseller or SharePoint, Office 365 or Azure service provider. We are offering a free Partner Program with up to 30% Discount, free NFR licenses etc. Please download for more.
PDF Image

Layer2 Case Study: Cloud Connector helps ChemWerth to integrate the Microsoft Office 365 Cloud

"The Cloud Connector’s interface is easy to understand and use. Anyone familiar with utilities in the Microsoft stack will be productive immediately. Our recommendation: The Layer2 Cloud Connector is a no brainer.”
NoDoc Image

Layer2 Cloud Connector PAD File

This XML-based PAD file offers a Layer2 Cloud Connector product description for resellers to download and publish.
PDF Image

Office 365: How to sync with MS Dynamics CRM

You can sync any CRM data with native lists in SharePoint on-prem or online to make it available offline with Outlook.
PDF Image

Office 365: How to sync with SAP?

You can connect SharePoint and Office 365 with SAP via NetWeaver and OData.
PDF Image

Office 365: How to sync your local file share?

When you are moving to the cloud, there are a lot of reasons to keep your local file system. But you don't have to miss the benefits of the cloud.
PDF Image

Product Flyer: Cloud Connector for Microsoft SharePoint & Office 365

Flyer with features, benefits & pricing info for download as PDF.
PDF Image

Product Flyer: SharePoint Online Local File Server Sync

You can centrally migrate, synchronize, or backup your local file server department shares and home drives very flexible with SharePoint Online to close known gaps and overcome limitations of the Microsoft OneDrive for Business client.
PDF Image

Solutions Flyer: Data Integration with SharePoint, Office 365 and 100+

FileMaker can be integrated with 100+ other sources codeless using the Layer2 Cloud Connector.
Related Links

Layer2 Cloud Connector Community Group @ LinkedIn

​Are you already @ LinkedIn? Please join the Cloud Connector Community Group to share your experience.

Microsoft Partner Story Book for Office 365 about Cloud Connector

​Know more about a Layer2 Cloud Connector customer reference at the Microsoft Office web site.

Purchase Layer2 Cloud Connector via ComponentSource

​ComponentSource has offices in the USA, UK and Japan supporting over 125,000+ Customers, in over 180 Countries. They are also an approved Federal Government supplier through the GSA Schedule.

 

cs-award-top-100-publisher-2014-15-large.gif

Sign Up for Layer2 Product Newsletter

We keep you up-to-date about new Layer2 product releases, added features and fixed issues.