Import/Export/Update of Sharepoint lists

As there is no native Adapter for Sharepoint in FlowHeater at present, we wish to provide a short explanation about how you can gain access to Sharepoint Portal Server lists by using the FlowHeater OleDB Adapter. The three scenarios of import, export and update are briefly described.

Fundamentals

In order to access Sharepoint Portal Server lists we need to provide the FlowHeater OleDB Adapter with a connection string similar to the following:

The parameters that need your attention here are DATABASE and LIST. These require adapting to you own circumstances. For the DATABASE parameter this is quite simple, just amend this to the ROOT URL of your Sharepoint Portal Server. What is more difficult is finding the correct ID for the LIST parameter. Unfortunately, unless you have Administrator access to the Sharepoint Server, you are barred from this.

Firstly, login to the Sharepoint Server with Administrator rights. Then navigate to the list you want to import, export or update. Next, select the menu option Settings (see illustration) and from the dropdown "List Settings". Finally, you will find the LIST ID in the address line of your web browser.

e.g. http://sharepoint.your-url.local/ … List=%7B07D6C21F%2D0687%2D4E74%2DA098%2DF56EC904A207%7D

Make a copy of this value and retain this for the OleDB connection string for list with the following changes: replace the initial %7B with a { character, the %7D at the end with a } and all intervening occurrences of %2D with a – character (hyphen). The resulting string {07D6C21F-0687-4E74-A098-F56EC904A207} is known as a GUID.

Note: The GUID differs for each list.

Export of Sharepoint lists to CSV data

This is the simplest case. Create a new Definition with an OleDB Adapter on the READ side and the TextFile Adapter on the WRITE side. Open the Configurator of the OleDB Adapter and enter the connection string derived above as the OleDB data source.

Now switch to the tab "Fields / data types". Unfortunately, you cannot select a table here, because the OleDB Adapter does not support reading of table names across a connection to a Sharepoint Server.

Enter into the SQL textbox the SQL command “select * from list” by hand. It does not matter which Sharepoint list you want to export, the SQL command is always the same. Next you can click the "Read Schema" button to input the schema information of the Sharepoint list. The remainder of the CSV export is identical to usual FlowHeater database exports.

Import Adapter Properties

CSV data import to Sharepoint lists

This is bit more complicated. Similar to the procedure for exports, create a new Definition, but this time select the TextFile Adapter on the READ side and the OleDB Adapter on the WRITE side. Enter the connection string derived above into the OleDB Adapter as the OleDB data source. However, if you now switch to the "Fields / data types" tab, you will not be able to enter anything into the SQL textbox, because this field is read-only for the WRITE side. In order to be able to enter a Sharepoint list (i.e. “select * from list”), you first have to close the Configurator. Now go to the OleDB Adapter properties that are shown bottom right in the Designer (for the WRITE side), see illustration. Look for the SQL parameter and enter for this solely the value “LIST” without the “select * from” part. Next reopen the Configurator of the OleDB Adapter, switch to the “Fields / data types” tab and now click on the “Read Schema” button to obtain the field information from the Sharepoint list. Now you can proceed to import data into this Sharepoint list using familiar FlowHeater techniques.

Updating data in Sharepoint lists

Now it gets even more complicated. Although largely the same procedure as above for “CSV data import to Sharepoint lists” the problem is that reading the schema information from a Sharepoint list does not transfer details of the Primary Key. It is necessary to select this information yourself. Then go to the Configurator tab “Fields / data types” to select the ID field and check the option Primary Key. This will cause the OleDB Adapter to execute SQL statements for the Sharepoint list like the following:

Update list set field = ‘new value 1’ where id = 1
Update list set field = ‘new value 2’ where id = 2
…

Warning: If you inadvertently specify the wrong field here as the Primary Key, more records than intended could potentially be amended. In the worst case, data may be overwritten and thereby lost. You are therefore strongly advised to make a backup first.

Limitations

The import/update of documents (FlowHeater data type RAW) in Sharepoint lists is regrettably not possible using the FlowHeater OleDB Adapter.