Blog
SharePoint - How to export and import lists and maintain lookup relationships
April 3. 2021
A common requirement is to export and import sets of SharePoint lists, and to maintain the lookup data. This post describes how to carry out this task.
App builders frequently use SharePoint as a database. It's a
popular choice because users with Microsoft 365 licenses can use
SharePoint without any additional licensing.
A common requirement is to move objects between different sites, or different organisations. The previous post here described how to carry out this task with the help of the PnP Framework and Provisioning Engine.
http://powerappsguide.com/blog/post/moving-sharepoint-sites-and-lists
In this post, we cover a more specific and common requirement. That is, how to export and import sets of SharePoint lists, and to maintain the lookup data. We'll look at how to use a PowerShell script to export a set of lists. This will produce a script that we can use the import the lists into a target SharePoint site.
This post also highlights the structure of the script. We can use this as a basis to manually create a script to create SharePoint lists. This can provide a quick way to build SharePoint lists, compared to building lists and defining fields manually through the SharePoint interface.
http://powerappsguide.com/blog/post/moving-sharepoint-sites-and-lists
In this post, we cover a more specific and common requirement. That is, how to export and import sets of SharePoint lists, and to maintain the lookup data. We'll look at how to use a PowerShell script to export a set of lists. This will produce a script that we can use the import the lists into a target SharePoint site.
This post also highlights the structure of the script. We can use this as a basis to manually create a script to create SharePoint lists. This can provide a quick way to build SharePoint lists, compared to building lists and defining fields manually through the SharePoint interface.
Exporting parent and child lists with data
To export a set of SharePoint lists, we use the PnP Framework and Provisioning Engine, as outlined in my previous post.
http://powerappsguide.com/blog/post/moving-sharepoint-sites-and-lists
Once we carry out the prerequisite steps, we can use the following PowerShell commands to export a set of lists.
#1 - Connect to the source SharePoint site
Connect-PnPOnline -Url "https://sourceSite.sharepoint.com/sites/sourceSite"
#2 - Export the source lists
Export-PnPListToSiteTemplate -Out "C:\PowerApps\SharePoint\myLists.xml"
-List "PropertyType", "Property"
#3 - Export the list data for the specified lists
Add-PnPDataRowsToSiteTemplate -Path "C:\PowerApps\SharePoint\myLists.xml" -List "PropertyType"
Add-PnPDataRowsToSiteTemplate -Path "C:\PowerApps\SharePoint\myLists.xml" -List "Property"
This example exports a set of lists. The first list is called "PropertyType". This list stores property type descriptions such as "House", "Apartment", etc. The second list is called "Property". This list stores the address and other details that relate to the property. This list contains a lookup column that references the "PropertyType" list.
The first cmdlet initiates the connection to the source
SharePoint list. We would replace the URL with our source SharePoint
address. PowerShell will open a dialog that prompts us to login using
our Microsoft 365 account.
The next cmdlet
(Export-PnPListToSiteTemplate) is the one that exports the lists. The 'Out' parameter specifies the destination on our local machine where we want to save the
output template file. The List parameter specifies one or more list definitions to export. We separate the list names with commas.
Finally, we can
optionally call the Add-PnPDataRowsToSiteTemplate cmdlet to specify the
list data that we want to export.
The documentation for the Export-PnPListToSiteTemplate and Add-PnPDataRowsToSiteTemplate cmdlets are here:
https://pnp.github.io/powershell/cmdlets/Add-PnPDataRowsToSiteTemplate.htmlCreating a script that defines lists with lookup data
The script above produces an XML template file in the location "C:\PowerApps\SharePoint\myLists.xml". The code beneath shows a simplified version of this file. Here, we can see the definition of the two lists, including the field name definitions. We can also see the script that defines the data rows in these two lists.
A notable point about the definition of the "PropertyType" list is that there are no extra columns, apart from the default title column. For this reason, the "PropertyType" list definition does not include any field elements.
Importing our lists and lookup data to a destination SharePoint Site
A notable point about the definition of the "PropertyType" list is that there are no extra columns, apart from the default title column. For this reason, the "PropertyType" list definition does not include any field elements.
<?xml version="1.0"?>
<pnp:Provisioning xmlns:pnp="http://schemas.dev.office.com/PnP/2020/02/ProvisioningSchema">
<pnp:Preferences Generator="PnP.Framework, Version=1.3.0.0, Culture=neutral, PublicKeyToken=null" />
<pnp:Templates ID="CONTAINER-TEMPLATE-01EDEBA5B3F74C1F80B99885325E0AE9">
<pnp:ProvisioningTemplate ID="TEMPLATE-01EDEBA5B3F74C1F80B99885325E0AE9"
Version="1" BaseSiteTemplate="GROUP#0" Scope="RootSite">
<pnp:Lists>
<pnp:ListInstance Title="PropertyType"
Url="Lists/PropertyType"
TemplateType="100" EnableFolderCreation="false">
<pnp:ContentTypeBindings>
<pnp:ContentTypeBinding ContentTypeID="0x01" Default="true" />
</pnp:ContentTypeBindings>
<pnp:Views>
<View Name="AllItems" DefaultView="TRUE" Type="HTML"
DisplayName="All Items"
Level="1" BaseViewID="1" ContentTypeID="0x">
<Query />
<ViewFields>
<FieldRef Name="LinkTitle" />
</ViewFields>
<RowLimit Paged="TRUE">30</RowLimit>
</View>
</pnp:Views>
<pnp:DataRows UpdateBehavior="Overwrite">
<pnp:DataRow>
<pnp:DataValue FieldName="Title">House</pnp:DataValue>
<pnp:DataValue FieldName="Order">100</pnp:DataValue>
<pnp:DataValue FieldName="MetaInfo" />
<pnp:DataValue FieldName="FileLeafRef">1_.000</pnp:DataValue>
</pnp:DataRow>
<pnp:DataRow>
<pnp:DataValue FieldName="Title">Apartment</pnp:DataValue>
<pnp:DataValue FieldName="Order">200</pnp:DataValue>
<pnp:DataValue FieldName="MetaInfo" />
<pnp:DataValue FieldName="FileLeafRef">2_.000</pnp:DataValue>
</pnp:DataRow>
</pnp:DataRows>
</pnp:ListInstance>
<pnp:ListInstance Title="Property"
Url="Lists/Property"
TemplateType="100" EnableFolderCreation="false">
<pnp:ContentTypeBindings>
<pnp:ContentTypeBinding ContentTypeID="0x01" Default="true" />
</pnp:ContentTypeBindings>
<pnp:Views>
<View Name="AllItems" DefaultView="TRUE" Type="HTML"
DisplayName="All Items"
Level="1" BaseViewID="1" ContentTypeID="0x">
<Query />
<ViewFields>
<FieldRef Name="LinkTitle" />
<FieldRef Name="Address1" />
<FieldRef Name="PropertyType" />
</ViewFields>
<RowLimit Paged="TRUE">30</RowLimit>
</View>
</pnp:Views>
<pnp:Fields>
<Field Type="Text" DisplayName="Address1" Name="Address1"
Required="FALSE" EnforceUniqueValues="FALSE" Indexed="FALSE"
MaxLength="255"
ID="{47e21ea5-6f82-473b-94ef-fe352a650b9c}"
/>
<Field Type="Lookup" DisplayName="PropertyType" Required="FALSE"
EnforceUniqueValues="FALSE"
List="{listid:PropertyType}" ShowField="Title"
RelationshipDeleteBehavior="None"
ID="{7e976666-c19c-40e9-8da2-8444bf7f5ae4}"
/>
</pnp:Fields>
<pnp:DataRows UpdateBehavior="Overwrite">
<pnp:DataRow>
<pnp:DataValue FieldName="Title">House1</pnp:DataValue>
<pnp:DataValue FieldName="Order">100</pnp:DataValue>
<pnp:DataValue FieldName="MetaInfo" />
<pnp:DataValue FieldName="Address1">10 Highstreet</pnp:DataValue>
<pnp:DataValue FieldName="PropertyType">1</pnp:DataValue>
<pnp:DataValue FieldName="FileLeafRef">1_.000</pnp:DataValue>
</pnp:DataRow>
</pnp:DataRows>
</pnp:ListInstance>
</pnp:Lists>
</pnp:ProvisioningTemplate>
</pnp:Templates>
</pnp:Provisioning>
Importing our lists and lookup data to a destination SharePoint Site
To
import the SharePoint lists and data onto our target site, we save the script to a location on our computer. ("C:\PowerApps\SharePoint\listsToImport.xml" in this example)
Next, we open PowerShell and connect to the target site by calling the Connect-PnPOnline cmdlet. We can then install our script by calling the Invoke-PnPSiteTemplate cmdlet.
Here's a summary of the commands:
When this process completes, we can navigate to our destination SharePoint site and view the lists and records that we've added.#1 - Connect to the destination SharePoint site
Connect-PnPOnline -Url "https://destinationSite.sharepoint.com/sites/destinationSite"
#2 - Import the items from the template file
Invoke-PnPSiteTemplate -Path "C:\PowerApps\SharePoint\listsToImport.xml"
Conclusion
A common requirement is to export and import sets of SharePoint lists with lookup data. This post highlighted how to accomplish this by calling PowerShell commands that utilise the PnP Framework and Provisioning Engine.
- Categories:
- sharepoint
Previous
Related posts
- SharePoint - Deleting the last record from a SharePoint list - how NOT to do this!
- Walkthrough - An beginners guide to building an app that stores images in SharePoint
- SharePoint - What to do when there's a mismatch between times in Power Apps and SharePoint
- SharePoint - Filtering lists by User() is now delegable
- SharePoint - How to fix the "skip to main content" error message on integrated forms, or to fix forms where records don't save
- SharePoint - Use this trick to perform a 'contains' type search in a more delegable way
- Sharepoint - Filtering records by yes/no columns bug - now fixed!
- Configuration - How to set the SharePoint address of a data source with a variable
- Data - How to make a copy of a record
- SharePoint - how to fix list threshold errors when working with very large lists
- Data - How to move SharePoint sites, lists, and data
- Code - Can we hack SharePoint ID columns to be delegable using the greater than/less than operators?
- SharePoint - How to filter records by the current user
- SharePoint - Beware of numeric calculated columns!
