Creating SharePoint Lists/ Libraries with Data using PowerShell

In any SharePoint development/migration/moving data across sites, we come across this typical requirement.

Creating SharePoint Lists/ Libraries

A typical power user solution can be to save the list as a template and restore the template.

This has a lot of limitations, including:

  • Cannot save content properly when data crosses threshold for a list;
  • Cannot save content or restore content when metadata or lookup references are used;
  • Is not a scalable solution where there are probabilities of lots of edits/updates going ahead.

Another typical custom solution is using code to create the initial list. You can choose to create the list through a deployment.

Some limitations of this approach can be:

  • Every time data needs to be added/edited, code needs to be changed;
  • Development knowledge is needed, but deployment can be hectic with permission issues and challenges.

An alternative approach that has gained popularity in this context is the use of PowerShell. Data for the list is generally stored in an xml, which is easily readable and editable.

In this section we will look into the approach of using PowerShell.

PowerShell Solution

Let us follow the best practice defined in this regard.

  1. We will create an xml to keep the data
  2. We will create a power shell to read the xml values, create the list, and add the values

Defining the Xml

Let us define a very simple list, name it as test list, and we will add two columns that takes text as input.

We will not focus on how to add different types of columns, as they can be easily derived using SharePoint manager or they can be found in the various blogs on the internet.

Let us create an xml and name it listProvision.xml. The below mentioned values goes into the xml, and are self-explanatory.


  
    

   TestList
          
          TestList
          GenericList
          This list is used for storing test list users
                      
            
            
            
                        
                  
    
   
  

Now let us create another xml that stores data for the list. Ideally we should create separate xml to create schema and data, to make sure scalability and extensibility can be achieved when we handle multiple lists.
Let us name this xml as listdata.xml.


  
    
        
          
            
              
                
		  


                
		  


                        
          
              
    
    

Now let us create the power shell to use these 2 xml to create the list and update the list columns with the mentioned data. This script will take care of creating lists, with any column types based on listProvision.xml. All you have to do is make sure this script points to the xml file properly

# ===================================================================================
# FUNC: List Provision 
# DESC: This will provision the list
# ===================================================================================
function ProvisionList([String]$ConfigFileName = "")
{
	# Check that the config file exists.
	if (-not $(Test-Path -Path $configFileName -Type Leaf))
	{
		Write-Error -message ("Configuration file '" + $configFileName + "' does not exist.")
	}

	$configXml = [xml]$(get-content $configFileName)
	if( $? -eq $false ) 
	{
		Write-Host "Could not read config file. Exiting ..." -ForegroundColor Red`
	}
	$Error.Clear()

	$webApplication = $Configuration.Configuration.Farm.WebApplications.WebApplication | Where-Object {$_.name -eq $configXml.Webapplication.name}
	if($webApplication -ne $null)
	{
		foreach ($site in $configxml.Webapplication.Sites.Site)
		{
			$siteConfiguration = $webApplication.Sites.Site | Where-Object {$_.Title -eq $site.Title}
			$url = $siteConfiguration.Path
            $siteCollection = Get-SPSite | Where-Object {$_.Url -eq $url}
			$RootWeb = $siteCollection.RootWeb

	    	try
		    {
				Write-Host "Site Collection: $url" -ForegroundColor Green
               
                foreach ($list in $site.Lists.List)
                {
					$webRelativeUrl = $list.Web
                    $listWeburl = $url + $webRelativeUrl

                    Write-Host "Provisioning List at:  $listWeburl" -ForegroundColor Green                    

                    Write-Host "Site Url : $listWeburl" -ForegroundColor Green

					$myTestWeb = Get-SPWeb -identity $listWeburl   #Get web object
		
                    $ListName = $list.Name #listName
					$ListTitle = $list.Title
                    $ListDescription = $list.Description #list description
                    $ListTemplateType =  $list.SPListTemplateType
                    $listTemplate = [Microsoft.SharePoint.SPListTemplateType]::$ListTemplateType  #GenericList template
                    
                    #build the list url
 
                    #$listUrl = $myTestWeb.ServerRelativeUrl + "/lists/" + $ListName;

                     
                    #we can't use getlist here as the method raises filenotfoundexception if the list url is not there
 
                    $myCustomList = $myTestWeb.Lists[$ListName]

                    
                    if($myCustomList -eq $null)
 
                    {
                      Write-Host "Adding list: $ListName" -ForegroundColor Green
					  
                      $lstId = $myTestWeb.Lists.Add($ListName,$ListDescription,$listTemplate)
 
                      $myCustomList = $myTestWeb.Lists[$ListName] # use getlist here as  the list already exists
                      
                      Write-Host "list created successfully: $ListName" -ForegroundColor Green
	                
                    }
                    else
                    {
						Write-Host "list already exists: $ListName" -ForegroundColor Green
	                }      
$myCustomList.Update()
                    $siteCollection.Dispose() 
                }

		    }
		    Catch
		    {
			    Write-Host "Error:Provisioning List." -ForegroundColor Red
			    Write-Host "Error Message : " $_.Exception.ToString()
			
		    }
		}
		Write-Host "Completed Provisioning List at: $listWeburl" -ForegroundColor Green
	}
	else
	{
		Write-Host "Error: Web Application not Found" -ForegroundColor Red
	}
}

Once done, we will use the below script to add rows of data to existing lists based on listData.xml values.

# ===================================================================================
# FUNC: List Data Population 
# DESC: This will Populate the list
# ===================================================================================
function PopulateListData([String]$ConfigFileName = "")
{
	# Check that the config file exists.
	if (-not $(Test-Path -Path $configFileName -Type Leaf))
	{
		Write-Error -message ("Configuration file '" + $configFileName + "' does not exist.") -Red
	}

	$configXml = [xml]$(get-content $configFileName)
	if( $? -eq $false ) 
	{
		Write-Host "Could not read config file. Exiting ..." -ForegroundColor Red`
	}
	$Error.Clear()

	$webApplication = $Configuration.Configuration.Farm.WebApplications.WebApplication | Where-Object {$_.name -eq $configXml.WebApplication.Name}
	if($webApplication -ne $null)
	{
		Write-Host "Web Application: $webApplication" -ForegroundColor Green
		foreach ($site in $configxml.WebApplication.Sites.Site)
		{
			$siteConfiguration = $webApplication.Sites.Site | Where-Object {$_.Title -eq $site.Title}
			$url = $siteConfiguration.Path
	    	Write-Host "Populate Site Data: $url" -ForegroundColor Green
			try
			{
			    foreach ($list in $site.Lists.List)
                {
					Write-Host "Poplate List Data: $url" -ForegroundColor Green
					$spweb = Get-SPWeb -identity $url   #Get web object
					
                    $listUrl = $url + $list.RelativeUrl #listName

					Write-Host "Checking if List Exists : $listUrl" -ForegroundColor Green
					
                    #build the list url
 
                    #we can't use getlist here as the method raises filenotfoundexception if the list url is not there
 
                    $myCustomList = $spweb.GetList($listUrl)

					if ($myCustomList -ne $null)
					{
						Write-Host "List Exists :" $myCustomList.Title -ForegroundColor Green

						foreach($item in $list.Items.Item)
						{
							Write-Host "Adding new item" -ForegroundColor Green

							[Microsoft.SharePoint.SPListItem]$spListItem = $myCustomList.Items.Add();
							Write-Host $spListItem.GetType().ToString();
							foreach($field in $item.Field)
							{
								Write-Host "Processing Field - " $field.Name -ForegroundColor Green
								if ($myCustomList.Fields.ContainsField($field.Name))
								{
									Write-Host "Field Exists in List- " $field.Name -ForegroundColor Green
									$type = $field.Type
									if ($type -eq "Taxonomy")
									{
										Write-Host "Processing Taxonomy Field - " $field.Name -ForegroundColor Green
										$session = new-object Microsoft.SharePoint.Taxonomy.TaxonomySession($spweb.Site)

										$termstore = $session.TermStores[$field.MMSName]
										$group = $termstore.Groups[$field.Group];
										$termSet = $group.TermSets[$field.Termset];
										
										Write-Host "Printing Terms" -ForegroundColor Green
										[string]$labelGuidPairFieldValues = "";
										$termCount = 0;
										foreach($xmlTerm in $field.Terms.Term)
										{
											Write-Host "Searching for MMS Term:" $xmlTerm.Name " in "  $field.Group " - " $field.Termset -ForegroundColor Green
											$found = $false;
											
											foreach($term in $termSet.Terms)
											{
												#Write-Host "XML Config Term:" $xmlTerm.Name -ForegroundColor Green
												if ($xmlTerm.Name -eq $term.Name)
												{
													Write-Host "Match Found" -ForegroundColor Green
													$found = $true;
													$labelGuidPairFieldValues = $labelGuidPairFieldValues + $term.Name + "|" + $term.Id + ";";
													$termCount = $termCount + 1;
													break;
												}
											}

											if ($found -eq $false)
											{
												Write-Host "Term Does not exist" -ForegroundColor Yellow
											}												
										}  

										Write-Host $labelGuidPairFieldValues -ForegroundColor Green
										if ($termCount -eq 1)
										{
											SetTaxonomyFieldValue -listItem $spListItem -fieldName $field.Name -labelGuidPairFieldValue $labelGuidPairFieldValues											
										}	
										elseif ($termCount -gt 1)
										{
											#Write-Host "Updating for SetTaxonomyFieldMultiValue: " $termCount -ForegroundColor Red
											#[Microsoft.SharePoint.SPListItem]$listItem, [string]$fieldName, [string]$labelGuidPairFieldValues)
											SetTaxonomyFieldMultiValue -listItem $spListItem -fieldName $field.Name -labelGuidPairFieldValues $labelGuidPairFieldValues
										}
																			
									}
									else
									{
										$spListItem[$field.Name] = $field.Value;
									}
								}
							}

							$spListItem.Update();
							Write-Host "List Item Added"  -ForegroundColor Green
						}

					}
					else
					{
						Write-Host "Skipping - List NOT FOUND :"  -ForegroundColor Yellow
					}
				}	

				Write-Host "Success: Populate List Data." -ForegroundColor Green
			}
			Catch
			{
				Write-Host "Error:Populate List Data." -ForegroundColor Red
				Write-Host "Error Message : " $_.Exception.ToString()
			}
		}
		Write-Host "Completed Populate List Data: $url" -ForegroundColor Green
	}
	else
	{
		Write-Host "Error: Web Application not Found" -ForegroundColor Red
	}
}

Questions?

Email [email protected] with any questions you have pertaining to this course.

New CPE Accredited Courses Now Available for Dynamics AX, GP, and NAVEARN CREDITS TODAY