Reading and Writing SharePoint Lists with FME

Introduction

FME’s Microsoft SharePoint List Reader/Writer enables FME users to manage Lists hosted on their SharePoint. A List is a structure for storing data. There are different types of pre-defined SharePoint Lists, also known as Apps. Each List is represented as a feature type in FME.

If you are unsure whether you should be using the SharePoint Reader/Writer or the SharePoint Online Connector, please refer to Getting Started with Microsoft SharePoint.

The FME SharePoint reader supports such Lists as Announcements, Calendars, Contacts, Document Libraries, Picture Libraries, and Surveys. Libraries act in a similar way to a file system, storing documents and files. However, the SharePoint reader only reads the metadata of the files, not the files themselves.

The FME SharePoint writer supports creating and deleting Lists, plus creating, updating, and deleting items in a List. The FME mechanisms used are very similar to those of a database format. However, of all the different list types it currently supports only the "Custom List".

In this tutorial we will walk through how to connect to a List on SharePoint and update the existing List with changes from an external CSV. 

Step-by-step Instructions

Download and unzip the SharepointListDemo.zip to find sample data and complete workspace to follow along for this Tutorial.

You can authenticate to access your SharePoint List with the following methods: Web Connection (OAuth 2.0), Basic, NTML, Kerberos. In this tutorial we will be using a Web Connection created from the Microsoft SharePoint Online (Template).
 

1. Add the SharePoint List Reader

Add the SharePoint List Reader to your Canvas, in the Dataset parameter enter the domain of your SharePoint site (for example, https://safesoftware.sharepoint.com). Click Parameters. 



Select your desired Authentication Method. In this tutorial we are using the Microsoft SharePoint Online (Template) Web Service configured to Safe Software’s SharePoint. You can choose either Basic, Web Connection, NTLM, and Kerberos. 

Under Lists, navigate to the List you want to read from your existing SharePoint Lists. Click OK to add the Reader to your Canvas. 


 

2. Add the updated dataset

Drag the UpdatedSampleList.csv onto your canvas. Click OK to add this to your Canvas. 



 

3. Detect changes made to the dataset

Add the ChangeDetector transformer to your Canvas. Learn more about the ChangeDetector here . Connect the SampleList from the SharePoint List Reader to the Original Port. Then connect the UpdatedSampleList from the CSV Reader to the Revised Port. Open up the Change Detector Parameters. 



Set the Update Detection Key Attribute to be Property_ID, and the Selected Attributes to be Claim_Status. Click OK to exit out. Run the Transformer. You should see that we have 6 features that have been updated.
 

4. Write the Changes to SharePoint

Add the SharePoint List Writer. Enter the domain of your SharePoint site. Click OK. Select the Feature Type to align with the SharePoint Reader. Click OK again to add the Writer to your Canvas. 



Connect the Updated, Inserted, Deleted, ports from the ChangeDetector to the SharePoint Writer. Double Click the Writer to open up the Feature Type window, Set the Feature Operation to fme_db_operation. Set the List Handling to Use Existing. Click OK to apply changes. 

Your Canvas should look like this. 



Run the writer to update your List on SharePoint. Feel free to make changes to the UpdatedSampleList.csv to see how the ChangeDetector deals with other operations ie, Inserted, Deleted.