Corporate data consists of files, databases, data residing in line of business applications and other legacy applications. Often, you need a subset of such data to get copied to your SharePoint list, enabling your employees at various levels of the organization to share and update data. The next step would be to synchronize these changes back into the original external data source. What you need to accomplish this is a bridge – a SharePoint connector, which connects and synchronizes SharePoint Lists in on-premise and Office 365 (Cloud), with your organization’s data residing elsewhere. Such an external bidirectional SharePoint connector can also help overcome the challenges that you may otherwise face with SharePoint’s native Business Connectivity Services.
Vyapin External Data Connector for SharePoint provides such bi-directional connectivity enabling you to import subsets of data from disconnected systems, with SharePoint lists and libraries, whether on-premise or the Cloud. This bypasses the need for users to directly access such data in the original source. Its ability to connect bidirectionally to different external systems – SQL, MySQL, Oracle, MS Access, ODBC clients, XLSX, CSV as well as from other SharePoint lists, helps to funnel data directly from and to SharePoint lists.
Importing external data into SharePoint
Let’s say your employee details need to be updated quickly. What better way than to extract only the essential fields (say, name, contact, rank and department), share them, update them and then merge the changes back into the external source. These details can be part of any ODBC compliant database or from a CSV or XLS file.
The External Data Connector allows you to choose from several different external databases as shown below. For example, if you store your employee data in SQL Tables then you can select the SQL server option.
Select the SharePoint Site where you want to import this subset of data for sharing with your users.
Once selected, you can import the data to the preferred List. And if you don’t have that List yet, you can create one during the import. Here’s how it’s done.
Note: To ensure that records deleted at the external data source are synchronized in the destination SharePoint List, you need to check the option – “Automatically synchronize items in destination, if it doesn’t exist in the source”.
If you need to extract a subset of data from a large database, then the Query Builder helps you filter and extract that subset. Here’s a sample shown below.
If you need to update the metadata along with the imported data, then you can map the columns so that they too are synchronized between the external source and the SharePoint Lists. Indicating a reference column is useful in completing this task. Check the screenshot below.
Once a proper task name is assigned the task can be run immediately or scheduled to run at the appointed time. Scheduling it to run multiple times can help you synchronize the changes made in the source to be updated to the destination SharePoint List. Check the screenshot below.
The data is imported to the SharePoint List and is ready to be shared and updated by users in SharePoint. The screenshot below shows employee details added to a List “EDCExternalList” in SharePoint.
Synchronizing the SharePoint changes back into the external data source
Once users have updated the details in the Items its ready to be synchronized with its source in the external data server. Apart from the changes to the Item, you would also like the updated metadata to be synced back into the source. Begin this synchronization process by selecting the appropriate List where the imported Items were added.
Next, match this to the external data base from where this sub-set was imported, which in this example would be the Table from which the records where extracted.
You may also map the appropriate columns from the SharePoint List to the external data base columns to update the changes back into the source. Here’s how it looks.
Assign a unique name to this task. You can run it immediately or schedule it to run at the appointed periods thus synchronizing the changes back into the source.
The records will be updated in the source. You can confirm this with the task summary for the selected task. Here’s a sample view.
The External Data Connector for SharePoint connects SharePoint with the external sources from where you need to import data. Download this SharePoint connector today for a trial from – https://www.vyapinsoftware.com/download/edc
