Publishing Page Import

This article covers how to import publishing pages to SharePoint.

This can help you if you need to bulk create pages in SharePoint and you have the data stored in a database or spreadsheet or if you need to migrate pages from another content management system such as WordPress.

yourpages

Scenario

We are going to assume that you have your page data in a database. For this example we are going to use MySQL and the WordPress schema. As you will see in the coming steps this could just as easily be any OleDB or ODBC data source and any schema.

Import Tool

We will use the Import for SharePoint to import the pages.

The Source

Column

The source data must contain a column with the HTML mark-up in it.

In our example content this is called ‘PageContent’. Inside it looks a bit like this.

Select Statement

Now from the database source we need to ‘Select’ the data that will create our pages. Since we are working with WordPress in this example the data is in wp_posts as we see below.

This select statement is also, cleverly, giving us the destination page name and setting up the page to be automatically published.

Import Configuration File

This is the file that tells Import for SharePoint how to create the pages in SharePoint.

The schema is fully explained in the documentation but the important bits for this exercise are explained here;

DestinationItemType

So we want to create publishing pages (We could alternatively create wiki pages, modern SharePoint pages, site pages or blog posts) but lets stick to the most common (publishing pages) for now.

<DestinationItemType>PublishingPage</DestinationItemType>

PageLayoutASPXName

So if your source select statement does not have a column of this name then “ArticleLeft.aspx” will be used. If you want to use another page layout then ensure you select statement returns a column of this name containing the name of your desired page layout.

<PageLayoutASPXName>PageLayoutASPXName</PageLayoutASPXName>

ImportMapping

This bit maps your HTML data (in the column PageContent) to the SharePoint page content (field Page Content).

<ImportMapping xsi:type=”ImportMapping_String”>
<DestinationField>Page Content</DestinationField>
<SourceColumn>PageContent</SourceColumn>
</ImportMapping>

Execution

Ok so rather than re-invent the wheel we’ll let you read the documentation installed with Import for SharePoint on this one.

Result

Ok so originally in WordPress the page looked like this.

source

And now in out of the box SharePoint it looks like this.

result

Great, but seems a bit simplistic

Ok so we have shown how to import publishing pages into SharePoint.

Realistically a project is always going to be more complicated than that.

So lets talk about real life….

Targeting Branded SharePoint

Page Layout

So the destination is likely to be branded? That’s no problem we’ve already talked about PageLayoutASPXName and custom branded SharePoint really just means using a different page layout.

Content Type Fields

But the destination page has extra fields, like managed meta data “Tags”, a Byline, an Article Date? Again no problem you just need more of these ImportMappings to map data from your source into those additional SharePoint fields.

<ImportMapping xsi:type=”ImportMapping_String”>
<DestinationField>Title</DestinationField>
<SourceColumn>post_title</SourceColumn>
</ImportMapping>

Data Manipulation

So what if the source data is not in the exact format that SharePoint needs?

No problem this manipulation can be done in SQL as shown below.

WordPress was never going to contain a column giving us a file name like “MyPage.Aspx” so we create one on the fly using concat here.

If (when?) your manipulations get too complex for inclusion in the SQL statement (on the fly) you can directly manipulate the source table, just make sure you take precautions if the source data is used by anything else (like working from a copy).

So what does this get used for?

We have seen this approach used for the following;

  • Legacy Content Management System (CMS) migration.
  • Bulk creation of pages from Excel
  • Scan to Mark-Up / Republishing – Loading data that has been scanned and OCR’d into pages.
  • WordPress to SharePoint Migration
  • Drupal to SharePoint Migration
  • Joomla to SharePoint Migration
  • Custom Intranet to SharePoint Migration

Great, Makes more sense now but I’m still an bit unsure

No problem just get in touch.

Retired Documentation

Table of Contents

Table of Contents. 4

1        Getting Started. 7

2        Installation. 8

2.1       Import
for SharePoint 8

2.2       OleDB
Data Source. 8

2.2.1       Excel OleDB Data Source. 8

3        Examples. 10

3.1       Import
from a file share or local folder to a SharePoint Document Library. 10

3.1.1       Overview.. 10

3.1.2       The Source. 10

3.1.3       The Configuration. 11

3.1.4       The Destination. 11

3.1.5       The Execution. 11

3.1.6       The Result 12

3.2       Import
from an Excel Source into a SharePoint List 13

3.2.1       Overview.. 13

3.2.2       The Source. 13

3.2.3       The Configuration. 14

3.2.4       The Destination. 17

3.2.5       The Execution. 17

3.2.6       The Result 18

3.3       Create
Folders from an Excel Source in a SharePoint Library adding meta data  19

3.3.1       Overview.. 19

3.3.2       The Source. 19

3.3.3       The Configuration. 20

3.3.4       The Destination. 23

3.3.5       The
Execution. 24

3.3.6       The
Result 24

3.4       Import
Documents from an Excel Source into a SharePoint Library. 25

3.4.1       Overview.. 25

3.4.2       The Source. 25

3.4.3       The Configuration. 26

3.4.4       The Destination. 29

3.4.5       The
Execution. 29

3.4.6       The
Result 30

3.5       Create
Document Sets from an Excel Source in SharePoint Library. 31

3.5.1       Overview.. 31

3.5.2       The Source. 31

3.5.3       The Configuration. 32

3.5.4       The Destination. 34

3.5.5       The Execution. 36

3.5.6       The Result 37

3.6       File
System Migration – Staff Records. 38

3.6.1       Scenario. 38

3.6.2       Why
Migrate. 39

3.6.3       Design
and Implement the Destination. 40

3.6.4       Catalogue
the File Share. 40

3.6.5       Prepare
the Excel Spreadsheets. 42

3.6.6       Get
User Input 43

3.6.7       Preparation
Complete. 44

3.7       Import
from / Migrate Legacy Document Management Systems. 45

3.7.1       The Source. 45

3.7.2       The
Configuration. 50

3.7.4       The
Destination. 53

3.7.5       The
Execution. 55

3.7.6       The
Result 56

3.8       Import
Documents from an Excel Source into OneDrive for Business. 57

3.8.1       Overview.. 57

3.8.2       The Source. 57

3.8.3       The Configuration. 58

3.8.4       The
Destination. 60

3.8.5       The
Result 60

3.9       Import
Publishing Pages into SharePoint 61

3.9.1       Overview.. 61

3.9.2       The Source. 61

3.9.3       The Configuration. 62

3.9.4       The Destination. 65

3.9.5       The Execution. 65

3.9.6       The Result 66

3.10         Import
Wiki (Site) Pages into SharePoint 67

3.10.1         Overview.. 67

3.10.2         The Source. 68

3.10.3         The Configuration. 69

3.10.4         The Destination. 71

3.10.5         The Execution. 71

3.10.6         The Result 72

4        Source
Configuration. 73

4.1       SourceDataSetType. 73

4.1.1       OLEDbTable. 73

4.1.2       OLEDbSelect 74

4.1.3       ODBCSelect 75

4.1.4       FileSystemFiles. 76

4.1.5       ExcelXLSXWorksheet 76

4.2       ConnectionString. 77

5        Destination
Configuration. 78

5.1       AuthenticationSettings. 78

5.2       DestinationItemSettings. 79

5.2.1       DestinationItemType. 79

5.2.2       ItemExistsBehaviour. 79

5.2.3       ImportMappings. 81

5.3       DestinationExecutionSettings. 85

5.3.1       PerItemImportThrottle. 85

5.4       SourceColumns. 86

5.4.1       SourceFileNameAndPath. 86

5.4.2       ContentType. 86

5.4.3       DestinationSubFolder. 87

5.4.4       DestinationFileName. 87

5.4.5       Publish. 88

5.4.6       PublishComment 90

5.4.7       CheckInComment 91

6        Troubleshooting. 93

6.1       Introduction. 93

6.2       Enable
logging. 93

6.2.1       To user interface. 93

6.2.2       To file / event log. 93

6.3       Getting
Support 95

 

 

 



1             
Getting Started

 

To import documents or data into SharePoint with Import for
SharePoint you need to do the following;

 

·        
Take the existing example XML file that is closest to your needs.

·        
Edit that XML file to meet your exact requirements (See
Examples).

·        
Load it into import for SharePoint

·        
Configure authentication using the UI.

·        
Start the import

 



 

 

2             
Installation

2.1           
Import for SharePoint

 

Run the setup.exe that you downloaded.

 

Install the 64bit version if you want to use 64bit data
sources.

 

Install the 32bit version if you want to use 32bit data
sources.

 

You can install using the supplied in installer or by
running the run from here version.

 

2.2           
OleDB Data Source

 

You will need to ensure that you have the correct 32bit or 64bit
OleDB provider for Import for SharePoint installed for the source that you wish
to access.

 

2.2.1         
Excel OleDB Data Source

 

 

 

Try

 

https://www.microsoft.com/en-gb/download/details.aspx?id=13255

 

or

 

https://www.google.co.uk/#q=excel+64+bit+oledb+

 

 

 

 

 

 



 

3             
Examples

 

You will find the matching excel
spreadsheets and XML configuration files for most of these examples in the
directory into which you installed Import for SharePoint.

 

3.1           
Import from a file share or local folder to a SharePoint Document
Library

 

3.1.1         
Overview

 

Take an existing folder and import the files and folders
within in to SharePoint.  For a more professional result (such as control over
destination content types) please refer the other examples on file system
migration.

 

3.1.2         
The Source

 

A local path;

 

e.g. c:\Import

 

Or a unc path

 

e.g. \\myserver\mystuff



 

 

3.1.3         
The Configuration

 

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <SourceDataSetType>FileSystemFiles</SourceDataSetType>

    <FileSystemFilesDataSetSettings>

      <Path>c:\Import</Path>

    </FileSystemFilesDataSetSettings>

</Source>

  <Destination>

    <AuthenticationSettings>

      <AuthenticationType>Current</AuthenticationType>

      <domain />

      <username />

    </AuthenticationSettings>

    <DestinationItemSettings>

      <DestinationItemType>Document</DestinationItemType>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

    </DestinationItemSettings>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/sites/SPImportHelper</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/sites/SPImportHelper/Shared
Documents
</DestinationFolderUrlRelative>

      <DestinationServerUrl>http://productdev</DestinationServerUrl>

      <DestinationListName>Shared
Documents
</DestinationListName>

    </DestinationListSettings>

    <SourceColumns>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>

 

3.1.4         
The Destination

 

A document library

 

3.1.5         
The Execution

 

 

 

3.1.6         
The Result

 

The files and folders underneath your source path will be
imported to SharePoint.



3.1.7         
 

 

 

 

 

3.2           
Import from an Excel Source into a SharePoint List

3.2.1         
Overview

 

Import list items from an Excel spreadsheet source into a
SharePoint Online (Office 365) list.

 

3.2.2         
The Source

 

The source is an XLSX

 

Columns define the title, description, content type and
control the import.

 

The worksheet is called “Jobs”.                                                                                         

 

 

 



 

3.2.3         
The Configuration

 

Please note that you can swap out the source section to
access the spreadsheet without using oledb – See ExcelXLSXWorksheet.

 

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <!– The
source used is OLEDbSelect meaning that Import for SharePoint expected to run a
SQL select statement against an OleDB data source.  OleDB select will ignore
columns like importstatus
–>

    <SourceDataSetType>OLEDbSelect</SourceDataSetType>

    <!– The
Connection string uses a microsoft provider to access on Excel spreadsheet
–>

    <OleDbSourceDataSetSettings>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\JobDescriptions.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbTableSourceDataSetSettings />

    <!– The
select statement will get all the jobs from the jobs worksheet
–>

    <OleDbSelectSourceDataSetSettings>

      <SelectStatement>select
* from [jobs$]
</SelectStatement>

    </OleDbSelectSourceDataSetSettings>

  </Source>

  <Destination>

    <!– The
authentication type is Office365 i.e. online cloud sharepoint, you can load the
configuration file to Import for SharePoint to enter and save the credentials
in encrypted format
–>

    <AuthenticationSettings>

      <AuthenticationType>Office365</AuthenticationType>

      <domain />

      <username></username>

      <encryptedpassed></encryptedpassed>

    </AuthenticationSettings>

    <DestinationItemSettings>

      <!– The
destination you are tell Import for SharePoint to make is an item as opposed to
a File or Folder
–>

      <DestinationItemType>Item</DestinationItemType>

      <!– If
the item already exists then overwrite it
–>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

      <!– Map
data from your source into SharePoint fields
–>

      <ImportMappings>

        <!– Map
title to title assuming that it is a string
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Title</DestinationField>

          <SourceColumn>Title</SourceColumn>

        </ImportMapping>

        <!– Map
job description to job description assuming that it is a string
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Job
Description
</DestinationField>

          <SourceColumn>Job
Description
</SourceColumn>

        </ImportMapping>

      </ImportMappings>

    </DestinationItemSettings>

    <!– Tell
Import for SharePoint exactly where the list is
–>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/sites/SPImportHelper</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/sites/SPImportHelper/Lists/Items</DestinationFolderUrlRelative>

      <DestinationServerUrl>https://company.sharepoint.com</DestinationServerUrl>

      <DestinationListName>Items</DestinationListName>

    </DestinationListSettings>

    <!– Tell
Import for SharePoint about the source you importing from
–>

    <SourceColumns>

      <!– The
column in the source which contains the full path of the file being imported. 
The value entered here is ignored unless DestinationItemType is Document
–>

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

      <!– The
column in the source the value of which matches the content type in sharepoint
to set on the item.  If you are not using content types on the destination list
you can enter an OOTB SharePoint content type such as Item, Document, Folder
–>

      <ContentType>ContentType</ContentType>

      <!– The
column in the source (if application) that contains the subfolder path to
import to
–>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <!– The
column in the source which contains the destination file name. The value
entered here is ignored unless DestinationItemType is Document
–>

      <DestinationFileName>DestinationFileName</DestinationFileName>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>



3.2.4         
The Destination

 

 

3.2.5         
The Execution

 



 

 

3.2.6         
The Result

 

 

 



 

 

3.3           
Create Folders from an Excel Source in a SharePoint Library adding meta
data

3.3.1         
Overview

 

Create a folder structure suitable for staff records.

 

Set meta title on the created folders including lookup
fields and managed meta data.

 

3.3.2         
The Source

 

 



 

3.3.3         
The Configuration

 

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <!– The
source used is OLEDbSelect meaning that Import for SharePoint expected to run a
SQL select statement against an OleDB data source.  OleDB select will ignore
columns like importstatus
–>

    <SourceDataSetType>OLEDbSelect</SourceDataSetType>

    <OleDbSourceDataSetSettings>

      <!– The
Connection string uses a microsoft provider to access on Excel spreadsheet
–>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\StaffFolders.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbSelectSourceDataSetSettings>

      <!– The
select statement will get all the directories from the directories worksheet
–>

      <SelectStatement>select
* from [directories$]
</SelectStatement>

    </OleDbSelectSourceDataSetSettings>

  </Source>

  <Destination>

    <!– The
authentication type is Office365 i.e. online cloud sharepoint, you can load the
configuration file to Import for SharePoint to enter and save the credentials
in encrypted format
–>

    <AuthenticationSettings>

      <AuthenticationType>Office365</AuthenticationType>

      <domain />

      <username></username>

      <encryptedpassed></encryptedpassed>

    </AuthenticationSettings>

    <DestinationItemSettings>

      <!– The
DestinationItemType you are telling Import for SharePoint to make is a Folder
as opposed to an Item or Document
–>

      <DestinationItemType>Folder</DestinationItemType>

      <!– If
the item already exists then overwrite it
–>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

      <!– Map
data from your source into SharePoint fields
–>

      <ImportMappings>

        <!– Map
title to title assuming that it is a string
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Title</DestinationField>

          <SourceColumn>Title</SourceColumn>

        </ImportMapping>

        <!– Map
Employee Number to EmployeeNumber without conversion.  In this instance this
works because both are numeric fields
–>

        <ImportMapping xsi:type="ImportMapping_Native">

          <DestinationField>EmployeeNumber</DestinationField>

          <SourceColumn>Employee
Number
</SourceColumn>

        </ImportMapping>

        <!– Map
Employee Name to Employee Name as ManagedMetaDataAutoAdd.  In this instance the
value in the spreadsheet is created as a managed meta data term and then the
folder tagged with it
–>

        <ImportMapping xsi:type="ImportMapping_ManagedMetaDataAutoAdd">

          <DestinationField>Employee
Name
</DestinationField>

          <SourceColumn>Employee
Name
</SourceColumn>

        </ImportMapping>

        <!– Map
Date of Leaving to DateOfLeaving converting from a string date.  In this
instance the string is assumed to be in UK date format
–>       

        <ImportMapping xsi:type="ImportMapping_DateTimeFromString">

          <DestinationField>DateOfLeaving</DestinationField>

          <SourceColumn>Date
of Leaving
</SourceColumn>

                    <!–
Refer to DateTime.ParseExact on MSDN for more information on ConversionMask
(format) and culture (iformat provider)
–>

                    <ConversionMask>dd/MM/yyyy
hh:mm:ss
</ConversionMask>

                    <Culture>en-GB</Culture>

        </ImportMapping>

        <!– Map
Job Title to Job assuming that job is a look up field and the value of the
column job title matches an entry in it.
–>

        <ImportMapping xsi:type="ImportMapping_Lookup">

                    <!– This
is the field of type lookup.
–>

          <DestinationField>Job</DestinationField>

          <SourceColumn>Job
Title
</SourceColumn>

                    <!– This
is the list that contains the lookup values
–>

                    <LookupListTitle>Items</LookupListTitle>

                    <!– This
is the field in the lookup list the value of which matches the value in the
source column
–>

                    <LookupFieldInternalName>Title</LookupFieldInternalName>

                    <!– This
is the CAML value type Text,Number,DateTime,Guid,MultiChoice,Lookup for the
field on in the lookup list that matches the value specified in the source
column
–>

                    <LookupFieldCAMLType>Text</LookupFieldCAMLType>

        </ImportMapping>

      </ImportMappings>

    </DestinationItemSettings>

    <!– Tell
Import for SharePoint exactly where the destination list or library is
–>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/sites/SPImportHelper</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/sites/SPImportHelper/Docs</DestinationFolderUrlRelative>

      <DestinationServerUrl>https://company.sharepoint.com</DestinationServerUrl>

      <DestinationListName>Docs</DestinationListName>

    </DestinationListSettings>

    <!– Tell
Import for SharePoint about the source you importing from
–>

    <SourceColumns>

      <!– The
column in the source which contains the full path of the file being imported. 
The value entered here is ignored unless DestinationItemType is Document
–>

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

      <!– The
column in the source the value of which matches the content type in sharepoint
to set on the item.  If you are not using content types on the destination list
you can enter an OOTB SharePoint content type such as Item, Document, Folder
–>

      <ContentType>ContentType</ContentType>

      <!– The
column in the source (if applicable) that contains the subfolder path (If any)
to import to
–>

      <!– This
could be, for example, "Staff" or "Staff/Managers" or
"Staff/Managers/Retired".  The folder must already exist and can be
created using Import for SharePoint
–>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <!– The
column in the source which contains the destination name. The value entered
here is ignored unless DestinationItemType is Document or Folder
–>

      <DestinationFileName>DestinationFolderName</DestinationFileName>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>

 



3.3.4         
The Destination

3.3.4.1           
Library

 

 

3.3.4.2           
Content Type

 

 

 



 

 

 

 

3.3.5         
The Execution

 

 

3.3.6         
The Result

 

Folders have been created and meta data fields
have been set.

 

 



 

 

3.4           
Import Documents from an Excel Source into a SharePoint Library

 

3.4.1         
Overview

 

An Excel spreadsheet contains a list of documents, the path
to each document and associated meta data.

 

Import for SharePoint imports the meta data from the
spreadsheet and the associated document into a document library.

 

3.4.2         
The Source

 

 



 

3.4.3         
The Configuration

Please note that you can swap out the source section to
access the spreadsheet without using oledb – See ExcelXLSXWorksheet.

 

 

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <!– The
source used is OLEDbSelect meaning that Import for SharePoint expected to run a
SQL select statement against an OleDB data source.  OleDB select will ignore
columns like importstatus
–>

    <SourceDataSetType>OLEDbSelect</SourceDataSetType>

    <OleDbSourceDataSetSettings>

      <!– The
Connection string uses a microsoft provider to access an Excel spreadsheet
–>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\StaffDocuments.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbSelectSourceDataSetSettings>

      <!– The
select statement will get all the documents from the files worksheet
–>

      <SelectStatement>select
* from [files$]
</SelectStatement>

    </OleDbSelectSourceDataSetSettings>

  </Source>

  <Destination>

    <!– The
authentication type is Office365 i.e. online cloud sharepoint, you can load the
configuration file to Import for SharePoint to enter and save the credentials
in encrypted format
–>

    <AuthenticationSettings>

      <AuthenticationType>Office365</AuthenticationType>

      <domain />

      <username></username>

      <encryptedpassed></encryptedpassed>

    </AuthenticationSettings>

    <DestinationItemSettings>

      <!– The
DestinationItemType you are telling Import for SharePoint to make is a Document
as opposed to an Item or Folder
–>

      <DestinationItemType>Document</DestinationItemType>

      <!– If
the item already exists then overwrite it
–>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

      <!– Map
data from your source into SharePoint fields
–>

      <ImportMappings>

        <!– Map
title to title assuming that it is a string
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Title</DestinationField>

          <SourceColumn>Title</SourceColumn>

        </ImportMapping>

        <!– Map
Employee Number to EmployeeNumber
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>EmployeeNumber</DestinationField>

          <SourceColumn>Employee
Number
</SourceColumn>

        </ImportMapping>

      </ImportMappings>

    </DestinationItemSettings>

    <!– Tell
Import for SharePoint exactly where the destination list or library is
–>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/sites/SPImportHelper</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/sites/SPImportHelper/Docs</DestinationFolderUrlRelative>

      <DestinationServerUrl>https://company.sharepoint.com</DestinationServerUrl>

      <DestinationListName>Docs</DestinationListName>

    </DestinationListSettings>

    <!– Tell
Import for SharePoint about the source you importing from
–>

    <SourceColumns>

      <!– The
column in the source which contains the full path of the file being imported. 
The value entered here is ignored unless DestinationItemType is Document
–>

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

      <!– The
column in the source the value of which matches the content type in sharepoint
to set on the item.  If you are not using content types on the destination list
you can enter an OOTB SharePoint content type such as Item, Document, Folder
–>

      <ContentType>ContentType</ContentType>

      <!– The
column in the source (if applicable) that contains the subfolder path (If any)
to import to
–>

      <!– This
could be, for example, "Staff" or "Staff/Bob Smith" or
"Staff/1001".  The folder must already exist and can be created using
Import for SharePoint
–>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <!– The
column in the source which contains the destination name. The value entered
here is ignored unless DestinationItemType is Document or Folder
–>

      <DestinationFileName>DestinationFileName</DestinationFileName>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>



3.4.4         
The Destination

 

 

 

3.4.5         
The Execution

 



 

 

3.4.6         
The Result

 

The staff documents are all in the correct
folders.

 

 



 

 

3.5           
Create Document Sets from an Excel Source in SharePoint Library

 

3.5.1         
Overview

 

Create a document set for each employee for their yearly
review.

3.5.2         
The Source

 

ContentType

Description

Title

Employee Number

Employee Name

DestinationFolderName

DestinationSubDirectories

Staff Document Set

All documents for Adams, David  Yearly Staff Appraisal

Adams, David  Yearly Staff Appraisal

1002

Adams, David

1002

Appraisals

Staff Document Set

All documents for Andrews, Robert  Yearly Staff Appraisal

Andrews, Robert  Yearly Staff Appraisal

1001

Andrews, Robert

1001

Appraisals

Staff Document Set

All documents for Brown, Jane  Yearly Staff Appraisal

Brown, Jane  Yearly Staff Appraisal

1003

Brown, Jane

1003

Appraisals

Staff Document Set

All documents for Burton, Sarah  Yearly Staff Appraisal

Burton, Sarah  Yearly Staff Appraisal

1004

Burton, Sarah

1004

Appraisals

Staff Document Set

All documents for Kumar, Sam  Yearly Staff Appraisal

Kumar, Sam  Yearly Staff Appraisal

1005

Kumar, Sam

1005

Appraisals

 



 

3.5.3         
The Configuration

Please note that you can swap out the source section to
access the spreadsheet without using oledb – See ExcelXLSXWorksheet.

 

 

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <SourceDataSetType>OLEDbSelect</SourceDataSetType>

    <OleDbSourceDataSetSettings>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\StaffDocumentSets.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbTableSourceDataSetSettings />

    <OleDbSelectSourceDataSetSettings>

      <SelectStatement>select
* from [directories$]
</SelectStatement>

    </OleDbSelectSourceDataSetSettings>

  </Source>

  <Destination>

    <AuthenticationSettings>

      <AuthenticationType>Office365</AuthenticationType>

      <domain />

      <username></username>

      <encryptedpassed></encryptedpassed>

    </AuthenticationSettings>

    <DestinationItemSettings>

      <!–
Document sets can be created in the same way as folders
–>

      <DestinationItemType>Folder</DestinationItemType>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

      <ImportMappings>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Title</DestinationField>

          <SourceColumn>Title</SourceColumn>

        </ImportMapping>

        <!– Each
document set has a description field.  Since there are multiple description
fields in SharePoint we will use the internal name
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>DocumentSetDescription</DestinationField>

          <SourceColumn>Description</SourceColumn>

        </ImportMapping>

        <ImportMapping xsi:type="ImportMapping_Native">

          <DestinationField>EmployeeNumber</DestinationField>

          <SourceColumn>Employee
Number
</SourceColumn>

        </ImportMapping>

        <ImportMapping xsi:type="ImportMapping_ManagedMetaDataCSOM">

          <DestinationField>Employee
Name
</DestinationField>

          <SourceColumn>Employee
Name
</SourceColumn>

        </ImportMapping>

      </ImportMappings>

    </DestinationItemSettings>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/sites/SPImportHelper</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/sites/SPImportHelper/Docs</DestinationFolderUrlRelative>

      <DestinationServerUrl>https://company.sharepoint.com</DestinationServerUrl>

      <DestinationListName>Docs</DestinationListName>

    </DestinationListSettings>

    <SourceColumns>

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

      <ContentType>ContentType</ContentType>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <DestinationFileName>DestinationFolderName</DestinationFileName>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>



 

3.5.4         
The Destination

 

3.5.4.1           
Content Type

 

A document set content type.

 



 

 

Which deploys some standard documents.

 


 

 

3.5.5         
The Execution

 

 



 

 

3.5.6         
The Result

 

The document sets are created.

 

 

Each document set auto-populates

 


 

3.6           
File System Migration – Staff Records

 

3.6.1         
Scenario

 

Our theoretical scenario
mirrors what is so often encountered when dealing with file shares.

We have a basic file
structure.  At the outer nodes the files are stored.  The “meta data”
is inferred by the location of the file in this structure as shown below.

Scenario_HR

The file share is the only
data source.  There is no Staff Database.  If there was then this
might be handled differently with that database providing some of the data.

The example has you have now
seen is for some Staff Records.  Such records need to be stored in a
compliant manner and retention scheduling is key to ensure that we retain only
the correct records for each staff member.  The requirement in this post
is vastly simplified in comparison to most Staff Record scenarios but it serves
to illustrate the concepts very well.



 

 

3.6.2         
Why Migrate

 

Be clear on why you are migrating
the data into SharePoint before you start the migration process.  You may
need to design the migration process to ensure that the desired
benefits are achieved.

In our scenario the key
drivers are;

·        
Compliance
– Specifically data retention scheduling.

·        
Efficiency
– Consolidation into SharePoint.

·        
Efficiency
– Ease of use.

·        
Efficiency
– Process automation.

 



 

 

 

3.6.3         
Design and Implement the Destination

 

Before you execute
migration you need to have a destination to migrate into.

This is key for two reasons;

·        
Migrating
a live file share which is being updated is harder to manage.

·        
Until you
have designed and implemented the destination you won’t necessarily know how to
define the import sources, in this example the two Excel spreadsheets.

For our scenario we have
implemented;

·        
A record
centre – /sites/Staff/Records.

·        
A record
library – Records

·        
A top
level folder “Staff”

·        
A content
type for each staff folder “Staff Folder” which has a date of leaving field and
retention action set from that date.

·        
A content
type for each staff record “Staff Record” which has an employee number field.

·        
A content
type for each disciplinary record “Staff Disciplinary Record” which has an
Disciplinary Date and retention action set from that date because these records
are kept for a shorter period of time.

It is sometimes useful to
catalogue the file shares as part of this design process.  This will give
you an insight into the scenarios that your destination will need to cope
with.  The file shares can then be re-catalogued for migration at a later
date.

3.6.4         
Catalogue the File Share

 

To catalogue the file share
we can use a PowerShell script.

You can run the script either
from a PC as a user with access to the file share OR from the server
hosting the file share.  The script below will audit both the files in the
file share and the directories, each to separate CSV files.

Function
Audit-File($file)

{

    Write-Host
"Auditing: " $file.FullName;

 

    $file |
add-member -name "Owner" -membertype noteproperty -value (get-acl
$_.fullname).owner;

    $file |
Add-Member -Name "Action" -MemberType NoteProperty -Value
"Copy";

 

    return $file

}

 

Function
Audit-Files($source, $destination)

{

 

    Get-ChildItem
-Recurse $source |  ?{-not $_.PSIsContainer} | ForEach-Object {Audit-File $_} |
Sort-Object fullname | Select
FullName,CreationTime,LastWriteTime,Length,Owner,BaseName,Name,Extension,Action,ContentType,Title,Meta1,Meta2,DestinationFileName,DestinationWebUrl,DestinationLibraryName,DestinationSubDirectories
| Export-Csv -Force -NoTypeInformation $destination

   

 

}

 

Function
Audit-Directory($folder)

{

    Write-Host
"Auditing: " $folder.FullName;

 

    $folder |
add-member -name "Owner" -membertype noteproperty -value (get-acl
$_.fullname).owner;

    $folder |
Add-Member -Name "Files" -MemberType NoteProperty -Value
($_.GetFiles().Count).ToString();

    $folder |
Add-Member -Name "Directories" -MemberType NoteProperty -Value
($_.GetDirectories().Count).ToString();

    $folder |
Add-Member -Name "Action" -MemberType NoteProperty -Value
"Create";

 

    return $folder;

}

 

Function
Audit-Directories($source, $destination)

{

 

    Get-ChildItem
-Recurse $source |  ?{$_.PSIsContainer} | ForEach-Object {Audit-Directory $_} |
Sort-Object fullname | Select FullName,CreationTime,LastWriteTime,Owner,Files,Directories,Action,ContentType,Title,Meta1,Meta2,DestinationFileName,DestinationWebUrl,DestinationLibraryName,DestinationSubDirectories
| Export-Csv -Force -NoTypeInformation $destination

   

 

}

 

Audit-Files "\\vmware-host\Shared
Folders\TestData\Staff\" "\\vmware-host\Shared
Folders\TestData\files.csv"

Audit-Directories
"\\vmware-host\Shared Folders\TestData\Staff\"
\\vmware-host\Shared
Folders\TestData\directories.csv

 

The
CSV files will contain the basic information that is available from the file
system.

 

Tip: Try and use UNC paths
instead of mapped drive letters when cataloguing file shares.

If you are unsure how to
execute PowerShell scripts then pop “how to execute a powershell script” into
your favourite search engine.

 

3.6.5         
Prepare the Excel Spreadsheets

 

The CSV files can be imported
to Excel and turned into a spreadsheet.

This will enable us to
automate the population of meta data that is so important to the success of
migration projects

3.6.5.1           
Folders

In Excel we can easily
populate some extra columns (shown here in green).

Here we are going to create
an import source which will create a folder of content type “Staff
Folder” for each staff member.  This is going to have the Date
of Leaving (which will drive retention schedules) and the staff name
as meta data.  The employee name and the employee number will be
extracted from the folder name from the file system using an Excel formula.

Excel_HR_Folders

3.6.5.2           
Files

In Excel we can easily
populate some extra columns shown in green.

Here we are going to create
an import source which will import all of the files for the Staff.  The
employee number is extracted from the folder name which will tell us destination
folder and the set some meta data against each document.  This is
extracted from the folder path.

 

http://www.ensentia.co.uk/wp-content/uploads/2016/03/Excel_HR_Files-2.jpg

3.6.6         
Get User Input

One of the strengths of Excel
is that most users will have skills in using it.

What this enables us to do is
use it to capture from the user base any additional data and permit the
users to generally cleanse the data.

Tip: Give the users some
guidance notes on how the Excel spreadsheets should be completed.

3.6.6.1           
Folders

Here the user has completed
the date of leaving field which in turn will allow SharePoint to manage the
retention schedule accordingly.

Excel_HR_DateOffLeaving

3.6.6.2           
Files

Here the user has spotted
that one file is a disciplinary document.  They have therefore changed the
disciplinary date and content type accordingly.

Excel_HR_DiscDate

3.6.7         
Preparation Complete

Once preparation is complete
you should have a set of Excel spreadsheets.

This should be double checked
and quality controlled before you commence the migration process but the core
work is done.

You can now create the folder
structure in SharePoint –
Create Folders from an Excel Source in a SharePoint Library adding
meta data
.

You can then import the
documents into that folder structure –
Import Documents from an Excel Source into a SharePoint Library,

 

 

 

 

 

 



 

 

3.7           
Import from / Migrate Legacy Document Management Systems

3.7.1         
The Source

3.7.1.1           
Introduction

 

Most document management systems are built to the same basic
architecture.

 

The meta data is stored in a database – typically SQL or
Oracle.

 

The documents themselves are stored on file server(s).

 

With a bit of work to decode the schema it is frequently
possible to perform a migration directly into SharePoint using Import for
SharePoint.

 

This section works through an example for a document
management system (DMS) with a very simple schema used for storing shipping
contracts.

 

The same approach will work for more complex schemas as such
as OpenText LiveLink.

 

Some DM systems require you to access the documents via an
API.  Talk to the Import for SharePoint developers in this instance for
assistance.



 

 

3.7.1.2           
Schema

 

The DMS has two tables “Documents” and “Versions”.

 

We can look at those tables.

 

use DMSystem

select top 10

ID,

TITLE,

field10,

field16

from Documents

 

 

select top 10

OBJECT_ID,

FilePath

from versions

 

ID

TITLE

field10

field16

1042

Contract Cost Agreement

Shipping

FALKLAND I

1043

Contract Cost Agreement

Shipping

ST HELENA

1045

Contract Cost Agreement

Shipping

ASCENSION

1047

Contract Cost Agreement

Shipping

BAHAMAS

1052

Contract Cost Agreement

Shipping

GHANA

1056

Contract Cost Agreement

Shipping

GAMBIA

1086

Contract Cost Agreement

Shipping

UGANDA

1088

Contract Cost Agreement

Shipping

UGANDA

1089

Contract Cost Agreement

Shipping

ZIMBABWE

1090

Contract Cost Agreement

Shipping

ZIMBABWE

 

OBJECT_ID

FilePath

124895

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

124896

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

124904

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

125003

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

125134

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

125135

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

125137

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

125138

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

125139

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

125172

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

 

Ok so this is an easy one to work out.

 

The ID relates the two tables.

 

The Documents table stores the meta data and the Versions
table stores the pointers to the files.



 

 

 

3.7.1.3           
Create an Import Source

 

Run a SQL command to create a single
ImportSource table that contains all the information that Import for SharePoint
needs to process the Import. 

 

select TITLE,filename,field10,field16,FilePath,

‘Contracts’ as
DestinationSubDirectories
,

‘Contract’ as
ContentType
,

Versions.ID as ImportKey,

CAST(‘Import’ as varchar(255)) as
ImportStatus
,

CAST(null as varchar(255)) as Exception

into ImportSource

from Documents,Versions

where documents.ID = versions.Object_ID

order by Versions.ID

 

We can have a look at our new table.

 

select top 10 * from ImportSource

 

TITLE

filename

field10

field16

FilePath

DestinationSubDirectories

ContentType

ImportKey

ImportStatus

Exception

Contract Cost Agreement

00000040.TIF

Shipping

FALKLAND I

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1033

Import

NULL

Contract Cost Agreement

00000041.TIF

Shipping

ST HELENA

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1034

Import

NULL

Contract Cost Agreement

00000042.TIF

Shipping

ASCENSION

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1036

Import

NULL

Contract Cost Agreement

00000043.TIF

Shipping

BAHAMAS

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1038

Import

NULL

Contract Cost Agreement

0000003E.TIF

Shipping

GHANA

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1043

Import

NULL

Contract Cost Agreement

00000031.TIF

Shipping

GAMBIA

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1046

Import

NULL

Contract Cost Agreement

00000052.TIF

Shipping

UGANDA

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1073

Import

NULL

Contract Cost Agreement

00000053.TIF

Shipping

UGANDA

\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1075

Import

NULL

Contract Cost Agreement

00000054.TIF

Shipping

ZIMBABWE

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1076

Import

NULL

Contract Cost Agreement

00000055.TIF

Shipping

ZIMBABWE

\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\SourceCode\Examples\Test.pdf

Contracts

Contract

1077

Import

NULL

 



3.7.2         
The Configuration

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <!– The
source used is OLEDbTable
–>

    <SourceDataSetType>OLEDbTable</SourceDataSetType>

    <!– The
Connection string uses a SQL Server data set
–>

    <OleDbSourceDataSetSettings>

      <ConnectionString>Provider=SQLNCLI10;Server=127.0.0.1\SharePoint;Database=DMSystem;Trusted_Connection=yes;</ConnectionString>

    </OleDbSourceDataSetSettings>

    <!– We
will use the ImportSource table
–>

    <OleDbTableSourceDataSetSettings>

      <TableName>ImportSource</TableName>

    </OleDbTableSourceDataSetSettings>

  </Source>

  <Destination>

    <!– The
authentication type is current because we are using SharePoint Server
On-Premises and running the import as a user with sufficient permissions
–>

    <AuthenticationSettings>

      <AuthenticationType>Current</AuthenticationType>

      <domain />

      <username></username>

      <encryptedpassed></encryptedpassed>

    </AuthenticationSettings>

    <DestinationItemSettings>

      <!– The
DestinationItemType you are telling Import for SharePoint to make is an item as
opposed to a File or Folder
–>

      <DestinationItemType>Document</DestinationItemType>

      <!– If
the item already exists then overwrite it
–>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

      <!– Map
data from your source into SharePoint fields
–>

      <ImportMappings>

        <!– Map
title to title assuming that it is a string
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Title</DestinationField>

          <SourceColumn>Title</SourceColumn>

        </ImportMapping>

        <!–
Field10 is the contract type
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>ContractType</DestinationField>

          <SourceColumn>Field10</SourceColumn>

        </ImportMapping>

        <!–
Field16 is the country
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Country</DestinationField>

          <SourceColumn>Field16</SourceColumn>

        </ImportMapping>

      </ImportMappings>

    </DestinationItemSettings>

    <!– Tell
Import for SharePoint exactly where the destination list or library is
–>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/sites/SPImportHelper</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/sites/SPImportHelper/Docs</DestinationFolderUrlRelative>

      <DestinationServerUrl>http://productdev</DestinationServerUrl>

      <DestinationListName>Docs</DestinationListName>

    </DestinationListSettings>

    <!– Tell
Import for SharePoint about the source you importing from
–>

    <SourceColumns>

      <!– The
column in the source which contains the full path of the file being imported. 
The value entered here is ignored unless DestinationItemType is Document
–>

      <SourceFileNameAndPath>filepath</SourceFileNameAndPath>

      <!– The
column in the source the value of which matches the content type in sharepoint
to set on the item.  If you are not using content types on the destination list
you can enter an OOTB SharePoint content type such as Item, Document, Folder
–>

      <ContentType>ContentType</ContentType>

      <!– The
column in the source (if applicable) that contains the subfolder path (If any)
to import to
–>

      <!– This
could be, for example, "Staff" or "Staff/Managers" or
"Staff/Managers/Retired".  The folder must already exist and can be
created using Import for SharePoint
–>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <!– The
column in the source which contains the destination name. The value entered
here is ignored unless DestinationItemType is Document or Folder
–>

      <DestinationFileName>filename</DestinationFileName>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>

 





 

 

3.7.4         
The Destination

3.7.4.1           
Content Type

 



 

 

3.7.4.2           
Library / Folder

 

 



 

 

3.7.5         
The Execution

 

Import for SharePoint will show progress as
files are imported.

 



 

 

3.7.6         
The Result

 

The files are imported into SharePoint /
SharePoint online.



 

 

 

3.8           
Import Documents from an Excel Source into OneDrive for Business

3.8.1         
Overview

 

An Excel spreadsheet contains a list of documents, the path
to each document and associated meta data.

 

Import for SharePoint imports the meta data from the
spreadsheet and the associated document into OneDrive for Business.

 

3.8.2         
The Source

 

FullName

ContentType

Title

DestinationFileName

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test1

Test1.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test2

Test2.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test3

Test3.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test4

Test4.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test5

Test5.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test6

Test6.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test7

Test7.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test8

Test8.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test9

Test9.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test10

Test10.pdf

\\vmware-host\Shared Folders\TestData\Generic\Test.pdf

Document

Test11

Test11.pdf



 

3.8.3         
The Configuration

Please note that you can swap out the source section to
access the spreadsheet without using oledb – See ExcelXLSXWorksheet.

 

 

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <SourceDataSetType>OLEDbSelect</SourceDataSetType>

    <OleDbSourceDataSetSettings>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\OneDrive.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbTableSourceDataSetSettings />

    <OleDbSelectSourceDataSetSettings>

      <SelectStatement>select
* from [files$]
</SelectStatement>

    </OleDbSelectSourceDataSetSettings>

  </Source>

  <Destination>

    <AuthenticationSettings>

      <AuthenticationType>Office365</AuthenticationType>

      <domain />

      <username>me@company.onmicrosoft.com</username>

      <encryptedpassed> /encryptedpassed>

    </AuthenticationSettings>

    <DestinationItemSettings>

      <DestinationItemType>Document</DestinationItemType>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

      <ImportMappings>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Title</DestinationField>

          <SourceColumn>Title</SourceColumn>

        </ImportMapping>

      </ImportMappings>

    </DestinationItemSettings>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/personal/me_company_onmicrosoft_com</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/personal/david_company_onmicrosoft_com/Documents</DestinationFolderUrlRelative>

      <DestinationServerUrl>https://company-my.sharepoint.com</DestinationServerUrl>

      <DestinationListName>Documents</DestinationListName>

    </DestinationListSettings>

    <SourceColumns>

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

      <ContentType>ContentType</ContentType>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <DestinationFileName>DestinationFileName</DestinationFileName>

      <Publish>Publish</Publish>

      <CheckInComment>CheckInComment</CheckInComment>

      <PublishComment>PublishComment</PublishComment>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>



3.8.4         
The Destination

 

User’s OneDrive for business “Files”.

3.8.5         
The Result

 

 



 

 

3.9           
Import Publishing Pages into SharePoint

3.9.1         
Overview

 

This process is most usually executed to;

 

·        
Bulk create publishing pages in SharePoint

·        
Or

·        
To migrate existing pages into SharePoint from other sources such
as WordPress, Drupal or any other CMS.

 

The process is demonstrated here from Excel but using an
OleDB source to a CMS system database you can migrate directly.  In such
migrations, this is done to move the HTML content.  You would define a separate
import process for resources such as images / downloads etc. and also carry out
any manipulation of the HTML before import.

 

Refer to the example on Legacy Document Management system
migration to see how you can use the OleDBTable data source type to import from
a SQL Database or similar, including, writing back migration status to the
source data.

 

3.9.2         
The Source

 

We are using the OOTB articleright.aspx page layout and
please note the HTML content in the Page Content column.

 

Note that we have chosen not to publish Page2.aspx

 

 



 

3.9.3         
The Configuration

Please note that you can swap out the source section to
access the spreadsheet without using oledb – See ExcelXLSXWorksheet.

 

 

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <SourceDataSetType>OLEDbSelect</SourceDataSetType>

    <OleDbSourceDataSetSettings>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\PublishingPage.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbTableSourceDataSetSettings />

    <OleDbSelectSourceDataSetSettings>

      <SelectStatement>select
* from [files$]
</SelectStatement>

    </OleDbSelectSourceDataSetSettings>

  </Source>

  <Destination>

    <AuthenticationSettings>

      <AuthenticationType>Office365</AuthenticationType>

      <domain />

      <username>me@mycompany.onmicrosoft.com</username>

      <encryptedpassed></encryptedpassed>

    </AuthenticationSettings>

    <DestinationItemSettings>

      <!–
Destination item type PublishingPage tells the import to create publishing
pages using the page layout name in the column as set by PageLayoutASPXName 
–>

      <DestinationItemType>PublishingPage</DestinationItemType>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

      <ImportMappings>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Title</DestinationField>

          <SourceColumn>Title</SourceColumn>

        </ImportMapping>

        <!– We
can map to byline or any field for that matter
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Byline</DestinationField>

          <SourceColumn>Byline</SourceColumn>

        </ImportMapping>

        <!– The
mapping to page content is one of the most import since it will map the HTML in
that column into your new page
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Page
Content
</DestinationField>

          <SourceColumn>Page
Content
</SourceColumn>

        </ImportMapping>

      </ImportMappings>

    </DestinationItemSettings>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/sites/SPImportHelper</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/sites/SPImportHelper/Pages</DestinationFolderUrlRelative>

      <DestinationServerUrl>https://mycompany.sharepoint.com</DestinationServerUrl>

      <DestinationListName>Pages</DestinationListName>

    </DestinationListSettings>

    <SourceColumns>

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

      <!–
Content Type is ignored for publishing pages since it is set by the page layout
–>

      <ContentType>ContentType</ContentType>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <DestinationFileName>DestinationFileName</DestinationFileName>

      <!– If
you want to publish your pages then create this column in your data source and
set to something that equates to boolean true for each page that you want to
publish 
–>

      <Publish>Publish</Publish>

      <!– The
name of the column in the source data set which contains a string to use as the
check in comment for the page
–>

      <CheckInComment>CheckInComment</CheckInComment>

      <!– The
name of the column in the source data set which contains a string to use as the
publish comment for the page
–>

      <PublishComment>PublishComment</PublishComment>

      <!– The
name of the column in the source data set which contains the name of the page
layout to use e.g. MyPageLayout.aspx
–>

      <PageLayoutASPXName>PageLayoutASPXName</PageLayoutASPXName>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>



 

3.9.4         
The Destination

 

The destination is going to be the “Pages” library in the
site created by the publishing feature.

 

 

3.9.5         
The Execution

 

 

3.9.6         
The Result

 

Two new pages have been created with the correct page layout
and desired approval status.

 

If we look at one of the pages it has the desired HTML
content, Title and By Line.

 

 



 

3.10        
Import Wiki (Site) Pages into SharePoint

3.10.1      Overview

 

This process is most usually executed to;

 

·        
Bulk create Wiki (Site) pages in SharePoint

·        
Or

·        
To migrate existing pages into SharePoint from other sources such
as WordPress, Drupal or any other CMS.

 

The process is demonstrated here from Excel but using an
OleDB source to a CMS system database you can migrate directly.  In such
migrations, this is done to move the HTML content.  You would define a separate
import process for resources such as images / downloads etc. and also carry out
any manipulation of the HTML before import.

 

Refer to the example on Legacy Document Management system
migration to see how you can use the OleDBTable data source type to import from
a SQL Database or similar, including, writing back migration status to the
source data.

 

Please note that if you want to import pages into an
Enterprise Wiki created using the site template of that name, then these are
actually rather confusingly Publishing Pages and so please refer to the
appropriate section.



 

 

3.10.2      The
Source

 

Note that the WikiField column contains HTML for the page.

 



 

3.10.3      The
Configuration

 

Please note that you can swap out the source section to
access the spreadsheet without using oledb – See ExcelXLSXWorksheet.

 

 

<?xml version="1.0" encoding="utf-8"?>

<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Source>

    <SourceDataSetType>OLEDbSelect</SourceDataSetType>

    <OleDbSourceDataSetSettings>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\WikiPage.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbTableSourceDataSetSettings />

    <OleDbSelectSourceDataSetSettings>

      <SelectStatement>select
* from [files$]
</SelectStatement>

    </OleDbSelectSourceDataSetSettings>

  </Source>

  <Destination>

    <AuthenticationSettings>

      <AuthenticationType>Office365</AuthenticationType>

      <domain />

      <username>me@mycompany.onmicrosoft.com</username>

      <encryptedpassed></encryptedpassed>

    </AuthenticationSettings>

    <DestinationItemSettings>

      <DestinationItemType>WikiPage</DestinationItemType>

      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>

      <ImportMappings>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>Title</DestinationField>

          <SourceColumn>Title</SourceColumn>

        </ImportMapping>

        <!– This
is the important mapping – the source column WikiField provides the HTML
content for the page
–>

        <ImportMapping xsi:type="ImportMapping_String">

          <DestinationField>WikiField</DestinationField>

          <SourceColumn>WikiField</SourceColumn>

        </ImportMapping>

      </ImportMappings>

    </DestinationItemSettings>

    <DestinationListSettings>

      <DestinationWebUrlRelative>/sites/SPImportHelper</DestinationWebUrlRelative>

      <DestinationFolderUrlRelative>/sites/SPImportHelper/SitePages</DestinationFolderUrlRelative>

      <DestinationServerUrl>https://mycompany.sharepoint.com</DestinationServerUrl>

      <!– Wiki
pages normally go into the Site Pages library
–>

      <DestinationListName>Site
Pages
</DestinationListName>

    </DestinationListSettings>

    <SourceColumns>

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

      <!–
Ignored
–>

      <ContentType>ContentType</ContentType>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <!– The
name of the column in the source data set which contains the name of the page
to create e.g. My Page.aspx
–>

      <DestinationFileName>DestinationFileName</DestinationFileName>

      <Publish>Publish</Publish>

      <CheckInComment>CheckInComment</CheckInComment>

      <PublishComment>PublishComment</PublishComment>

    </SourceColumns>

  </Destination>

</DataSetImportSettings>

 

 



 

3.10.4      The
Destination

 

The destination is going to be the “Site Pages” library.

 

 

 

3.10.5      The
Execution

 

 

3.10.6      The
Result

 

Two new pages have been created with the correct Title.

 

 

 

If we look at one of the pages it has the desired HTML
content, you will note that the displayed title is the same as the destination
file name and note the “Title” field which is normal for this type of page in
SharePoint.

 

 

 

 

 

 

 

 



 

4             
Source Configuration

 

The Source section of the XML configuration
file defines the source to Import from.

.

4.1           
SourceDataSetType

 

Defines how to get the source data set.

 

4.1.1         
OLEDbTable

 

You can define a data source as being a table using OLEDbTable. 
This allows each row to be updated after import as successful or otherwise
along with anyException data.  This is useful for large imports.

 

Below the table that we are telling Import for SharePoint to
use is a worksheet “Clients” in an excel spreadsheet.

 

  <Source>

    <SourceDataSetType>OLEDbTable</SourceDataSetType>

    <OleDbSourceDataSetSettings>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared Folders\projects\ClientFolders.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbTableSourceDataSetSettings>

      <TableName>clients$</TableName>

    </OleDbTableSourceDataSetSettings>

  </Source>

 

 

 

The table must contain columns ImportKey,
ImportStatus, Exception.

 

ImportKey must be unique.

 

In the example below only the first 10 rows
will be considered for import since only they have the importstatus “Import”.

 

 

After you have run the import the
spreadsheet will be updated.

 

 

 

You can then correct the causes of the
Exception, change the ImportStatus back to Import and re-run.

4.1.2         
OLEDbSelect

 

You can define a data source as simply the results of a
select statement using OLEDbSelect.

 

 

  <Source>

    <!– The
source used is OLEDbSelect meaning that Import for SharePoint expected to run a
SQL select statement against an OleDB data source.  OleDB select will ignore
columns like importstatus
–>

    <SourceDataSetType>OLEDbSelect</SourceDataSetType>

    <!– The
Connection string uses a microsoft provider to access an Excel spreadsheet
–>

    <OleDbSourceDataSetSettings>

      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=\\vmware-host\Shared
Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\JobDescriptions.xlsx;Extended
Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
</ConnectionString>

    </OleDbSourceDataSetSettings>

    <OleDbTableSourceDataSetSettings />

    <!– The
select statement will get all the jobs from the jobs worksheet
–>

    <OleDbSelectSourceDataSetSettings>

      <SelectStatement>select
* from [jobs$]
</SelectStatement>

    </OleDbSelectSourceDataSetSettings>

  </Source>

 

 

4.1.3         
ODBCSelect

 

You can define a data source as simply the results of a
select statement using ODBCSelect.

 

  <Source>

    <!– The
source used is ODBCSelect meaning that Import for SharePoint expects to run a
SQL select statement against an ODBC data source.  This will ignore columns
like importstatus
–>

    <SourceDataSetType>ODBCSelect</SourceDataSetType>

    <ODBCSourceDataSetSettings>

      <!– So a
system DSN has been created called WordPress, this has in fact been pointed to
a MySQL database hosting WordPress
–>

      <ConnectionString>DSN=WordPress;Uid=root;Pwd=password;</ConnectionString>

    </ODBCSourceDataSetSettings>

    <ODBCTableSourceDataSetSettings />

    <ODBCSelectSourceDataSetSettings>

      <!– The
select statement to run to obtain the rows for import.  This one actually gets
all wordpress blog posts
–>

      <SelectStatement>select
*, concat(post_name,’.aspx’) as DestinationFileName,
replace(post_content,’\n’,’
&lt;br/&gt;‘) as
PageContent, ‘True’ as Publish from wp_posts where post_type = ‘post’ and
post_status = ‘publish’
</SelectStatement>

    </ODBCSelectSourceDataSetSettings>

  </Source>



 

 

4.1.4         
FileSystemFiles

 

You can define a data set as the contents
of local directory or UNC path.

 

  <Source>

    <SourceDataSetType>FileSystemFiles</SourceDataSetType>

    <FileSystemFilesDataSetSettings>

      <Path>c:\Import</Path>

    </FileSystemFilesDataSetSettings>

</Source>

 

4.1.5         
ExcelXLSXWorksheet

 

You can access Excel XLSX files directory
without using oledb.

 

This can be a lot easier than installing
the OleDB drivers for Excel.

 

  <Source>

   
<SourceDataSetType>ExcelXLSXWorksheet</SourceDataSetType>

    <ExcelDataSetSettings>

     
<ExcelFileFullPath>c:\temp\StaffFolders.xlsx</ExcelFileFullPath>

     
<WorkSheetName>directories</WorkSheetName>

    </ExcelDataSetSettings>

  </Source>

 

 



4.1.6         
 

 

 

 

4.2           
ConnectionString

 

This is any valid connection string.

 

A google of “connection string examples” will give us
example connection strings for 100’s of different providers.

 

The connection string must match the source data type.   For
example if using OleDB you need any OleDB connection string, similarly an ODBC
connection string if you are using ODBC.

 

 

Import for SharePoint is most commonly used with Excel,
Microsoft SQL Server and MySQL.



 

 

5             
Destination Configuration

 

The Source section of the XML configuration
file defines the destination location, authentication, data mapping etc.

 

5.1           
AuthenticationSettings

 

This tells Import for SharePoint how to authenticate to
SharePoint.

 

Since credentials are encrypted the easiest way to configure
authentication is from the Import for SharePoint user interface –
authentication tab.

 

 

Once entered you can save the configuration file from the
Import tab if required.

 

 



 

 

5.2           
DestinationItemSettings

 

5.2.1         
DestinationItemType

 

This tells Import for SharePoint the base type – Item,
Document, Folder etc – so Import for SharePoint knows the basic action to
execute.

 

The content type to set on the item is defined elsewhere.

 

5.2.2         
ItemExistsBehaviour

 

This setting controls what happens when a file already
exists.

 

As such it also governs how you may import multiple
versions.  This can be useful when migrating from legacy systems which had
version control and where you wish to re-produce that in SharePoint.

 

The effective that it has is determined by what versioning
setting a SharePoint document library has.

 

The table below shows the files we are uploading – each
source is targeted to the same destination TestA.txt.

 

FullName

ContentType

Title

DestinationFileName

Version1.txt

Document

Version 1

TestA.txt

Version2.txt

Document

Version 2

TestA.txt

Version3.txt

Document

Version 3

TestA.txt

Version4.txt

Document

Version 4

TestA.txt

 

The table below shows the SharePoint setting will determine
the end result.



 

 

 

ItemExistsBehaviour

Library

Effect

Overwrite

Major Versions

Overwrite

Minor Version

Overwrite

No Versioning

Single version –
Version 4

DoNotOverwrite

Major Versions

DoNotOverwrite

Minor Version

DoNotOverwrite

No Versioning

Single version –
Version 1

 

 

5.2.3         
ImportMappings

 

5.2.3.1           
Default Mappings

 

When the destination item type is document the Created and
Modified properties in SharePoint will be set to that of the source document. 
This can be overridden with a custom mapping to get the data from the data set
if you require.

 

5.2.3.2           
Custom Mappings

 

Using ImportMappings allows you to set column / field values
on the destination items.

 

Mapping

Usage

String

 Take the string equivalent of SourceColumn and set
DestinationField to that value.

 

       <ImportMapping
xsi:type="ImportMapping_String">

         
<DestinationField>Title</DestinationField>

         
<SourceColumn>Title</SourceColumn>

       
</ImportMapping>

 

DateTimeFromString

Take the string value of
SourceColumn, convert it to DateTime using the specified culture and format. 
Set DestinationField to that value.

 

<ImportMapping
xsi:type="ImportMapping_DateTimeFromString">

<DestinationField>DateOfLeaving</DestinationField>

<SourceColumn>Date of
Leaving</SourceColumn>

<ConversionMask>dd/MM/yyyy
hh:mm:ss</ConversionMask>

<Culture>en-GB</Culture>

</ImportMapping>

 

<!– Refer to
DateTime.ParseExact on MSDN for more information on ConversionMask (format)
and culture (iformat provider)–>

Lookup

Take the value of
SourceColumn.  Find that value in the LookUpList.  Set DestinationField to
the id of that item.

 

<ImportMapping
xsi:type="ImportMapping_Lookup">

<!– This is the field of
type lookup.–>

<DestinationField>Job</DestinationField>

<SourceColumn>Job
Title</SourceColumn>

<!– This is the list
that contains the lookup values –>

<LookupListTitle>Items</LookupListTitle>

<!– This is the field in
the lookup list the value of which

matches the value in the
source column–>

<LookupFieldInternalName>Title</LookupFieldInternalName>

<!– This is the CAML
value type Text,Number,DateTime,Guid,MultiChoice,Lookup

for the field on in the
lookup list that matches the value specified in the

source column –>

<LookupFieldCAMLType>Text</LookupFieldCAMLType>

</ImportMapping>

ManagedMetaDataAutoAdd

Find the term set which
DestinationField is linked to.  Find a term in that set matching the value in
SourceColumn.  Set DestinationField to that term id. 

 

If the term does not exist Import
for SharePoint will try and create it.

 

“Andrews, Robert” would
become.

 

 

If the term set is nested
use, for example, a source value of “Managers\Andrews, Robert” or
“UK\Staff\Managers\ Andrews, Robert”

 

 

<ImportMapping xsi:type="ImportMapping_ManagedMetaDataAutoAdd">

<DestinationField>Employee
Name</DestinationField>

<SourceColumn>Employee
Name</SourceColumn>

</ImportMapping>

 

Multi-value fields should
be separated by a semi colon.

 

E.g. “SQL; SharePoint”

 

Would become;

 

 

May not work for SharePoint
Online.

 

 

ManagedMetaDataCSOM

Find the term set which
DestinationField is linked to.  Find a term in that set matching the value in
SourceColumn.  Set DestinationField to that term id.  Supports Single and Multi-value
fields.

 

Multi-value fields should
be separated by a semi colon.

 

The mapping will find the
term (e.g. SQL) anywhere in a nested term set and will set the item to the
value of the first matching term it finds. 

 

Will not work on SharePoint
2010.

 

Title

Employee Number

Employee Name

Skills

1002

1002

Adams, David

SharePoint

1001

1001

Andrews, Robert

SharePoint;SQL

 

Would become;

 

 

<ImportMapping xsi:type="ImportMapping_ManagedMetaDataCSOM">

          <DestinationField>Skills</DestinationField>

          <SourceColumn>Skills</SourceColumn>

</ImportMapping>

 

Native

Get the value of
SourceColumn and set destination with no conversion.  This assumes that the
source and destination have the same data type, e.g.both Numeric or both
DateTime or that they are close enough that .Net can automatically cast the
value, e.g. Integer to Numeric.

 

<ImportMapping
xsi:type="ImportMapping_Native">

<DestinationField>EmployeeNumber</DestinationField>

<SourceColumn>Employee
Number</SourceColumn>

</ImportMapping>

 

User

Get the value of
SourceColumn, get the user object for that person in SharePoint.  Set
DestinationField (which must be a person field of course) to that value.

 

The value of Source could
be “Bob Smith” or his email eg
Bob.Smith@company.onmicrosoft.com should also work.

 

<ImportMapping
xsi:type="ImportMapping_User">

<DestinationField>Client
Manager</DestinationField>

<SourceColumn>ClientManager</SourceColumn>

</ImportMapping>

 

 

 



 

 

 

5.3           
DestinationExecutionSettings

5.3.1         
PerItemImportThrottle

 

Sets a wait period in milliseconds that will be observed
after the import of each item.

 

The default is 0.

 

This can be useful when working with older implementations
to avoid triggering web request flood protection mechanisms for larger imports
or where you are seeing “The underlying connection was closed” midway through
the import.

 

For newer implementations returning the correct http status
when overloaded the import will throttle and retry automatically.

 

The setting can also be useful if you want to reduce the overhead
placed upon the farm as a result of an import being run.

 

The following example will wait 10 seconds after each item
is imported.

 

    <DestinationExecutionSettings>

      <PerItemImportThrottle>10000</PerItemImportThrottle>

    </DestinationExecutionSettings>

 

 

 

 

 

 

 



 

 

5.4           
SourceColumns

 

This section defines the columns that appear in the source
data that tell Import for SharePoint how to create the item in SharePoint.

 

In all instances the value of the setting is the name of the
column in the source data.

 

I.e.

 

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

 

Infers…

 

FullName

ContentType

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version1.txt

Document

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version2.txt

Document

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version3.txt

Document

 

The columns only need to be in your source data set if they
are Mandatory.  So for example SourceFileNameAndPath is mandatory for importing
documents but not folders or items.

 

5.4.1         
SourceFileNameAndPath

 

The name of the column in the source data which provides the
full path to the file being imported.

 

Mandatory for documents.

5.4.2         
ContentType

 

The name of the column in the source data which provides the
name of the content type to set the item to.

 

Mandatory.

5.4.3         
DestinationSubFolder

 

The name of the column in the source data which provides the
sub folder into which to import the item.

 

5.4.4         
DestinationFileName

 

The name of the column in the source data which provides the
destination file name / folder name of the item to create in SharePoint.  The
values in that column cannot include any characters which are invalid for
SharePoint such as ?!\ etc.

 

Mandatory for documents and folders.

 



 

 

5.4.5         
Publish

 

The name of the column in the source data which contains the
value “Yes” when the item is to be published.  This column is particularly
useful if you are trying to produce a specific version history in SharePoint.

 

To use the Publish column the destination library must have
minor versions enabled.

 

The ItemExistsBehaviour must be set to overwrite.

 

Working with this configuration:

 

    <SourceColumns>

      <SourceFileNameAndPath>FullName</SourceFileNameAndPath>

      <ContentType>ContentType</ContentType>

      <DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

      <DestinationFileName>DestinationFileName</DestinationFileName>

      <Publish>Publish</Publish>

    </SourceColumns>

 

And with this source data:

 

FullName

ContentType

Title

DestinationFileName

Publish

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version1.txt

Document

Version 1

TestA.txt

FALSE

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version2.txt

Document

Version 2

TestA.txt

TRUE

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version3.txt

Document

Version 3

TestA.txt

FALSE

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version4.txt

Document

Version 4

TestA.txt

TRUE

 

You will end up with this version history:

 

 

Where the publish column is forcing the item to be published
it becomes a major version.



 

 

5.4.6         
PublishComment

 

The name of the column in the source data which contains the
value to use as the PublishComment.

 

The following source data:

 

FullName

ContentType

Title

DestinationFileName

Publish

PublishComment

CheckInComment

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version1.txt

Document

Version 1

TestA.txt

FALSE

Publish comment for Version 1 of TestA.txt

Check in comment for Version 1 of TestA.txt

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version2.txt

Document

Version 2

TestA.txt

TRUE

Publish comment for Version 2 of TestA.txt

Check in comment for Version 2 of TestA.txt

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version3.txt

Document

Version 3

TestA.txt

FALSE

Publish comment for Version 3 of TestA.txt

Check in comment for Version 3 of TestA.txt

\\vmware-host\Shared Folders\TestData\Generic\Versions\Version4.txt

Document

Version 4

TestA.txt

TRUE

Publish comment for Version 4 of TestA.txt

Check in comment for Version 4 of TestA.txt

 



 

 

Will produce the following version history.

 

5.4.7         
CheckInComment

 

The name of the column in the source data which contains the
value to use as the Check InComment.

 

The following source data:

 

FullName

ContentType

Title

DestinationFileName

Publish

PublishComment

CheckInComment

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version1.txt

Document

Version 1

TestA.txt

FALSE

Publish comment for Version 1 of TestA.txt

Check in comment for Version 1 of TestA.txt

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version2.txt

Document

Version 2

TestA.txt

TRUE

Publish comment for Version 2 of TestA.txt

Check in comment for Version 2 of TestA.txt

\\vmware-host\Shared
Folders\TestData\Generic\Versions\Version3.txt

Document

Version 3

TestA.txt

FALSE

Publish comment for Version 3 of TestA.txt

Check in comment for Version 3 of TestA.txt

\\vmware-host\Shared Folders\TestData\Generic\Versions\Version4.txt

Document

Version 4

TestA.txt

TRUE

Publish comment for Version 4 of TestA.txt

Check in comment for Version 4 of TestA.txt

 

 

 

Will produce the following version history.

 

 



 

6             
Troubleshooting

 

6.1           
Introduction

This section provides some basic details on troubleshooting
an import.

 

6.2           
Enable logging

 

6.2.1         
To user interface

 

Go to the logging tab.

6.2.2         
To file / event log

 

You get enable logging to get more detailed information on
your import.

 

This is particular useful when, for example, import mappings
are not working because far more detail is logged here than is reported to the
console.

 

Logging is performed by .Net trace listeners and these may
be configured via the app configuration file e.g. difs.exe.config.

 

Section system diagnostics.

 

Below we have set autoflush to true and placed the log file
in the temp directory to enable us to get some extra logging. 

 

  <system.diagnostics>

    <!– By
setting autoflush false the listener will not be written to; this can get BIG. 
Set to true for troubleshooting
–>

    <trace autoflush="true"></trace>

    <sources>

      <source name="SPImportHelper">

        <listeners>

          <remove name="Default"/>

          <!– The
log file specified below will contain full details the of import.  The user
running the import must have write permission and autoflush must be set to
true.  If left in the default program files directory you must Run as Administrator
–>

          <add name="eventlog"

              type="System.Diagnostics.TextWriterTraceListener"

              initializeData="c:\temp\SPImportHelper.log">

            <!–

            <filter
type="System.Diagnostics.EventTypeFilter" initializeData="Information"/>

            –>

          </add>

        </listeners>

      </source>

    </sources>

  </system.diagnostics>

 

 

Refer to the following articles for information on how you
might use trace listeners.

 

https://msdn.microsoft.com/en-us/library/1txedc80(v=vs.110).aspx

 

https://msdn.microsoft.com/en-us/library/sk36c28t(v=vs.110).aspx

 



 

 

6.3           
Getting Support

 

If you are experiencing problems and you wish to log these
with development for assistance to resolve then please follow the steps below.

 

Try and reproduce the issue with a small number of items. 
For example 1,2 or 3 imported items.

 

Load you import configuration as normal.

 

Enable logging in the UI.

 

Run the import.

 

 

Send to support;

 

·        
The error log from the above (cut and paste it).

·        
A copy of the import configuration file.

·        
A copy of the source data in Excel format.

·        
Any list or library configuration details that may be relevant

 

Remove / obfuscate any sensitive information in the above
and make it obvious where you have done this.