In a project I’ve been working on recently there was a requirement to create a large number of SharePoint sites based on different custom site templates. Obviously there was no way I was going to create them all manually so I pulled together a handy little PowerShell script that reads the site name, url and template to be used from an Excel spreadsheet and then creates the sites using the New-SPWeb command. The Script is below:
function New-SPWebFromExcel { # Read in list of sites from Excel $DataTable = New-Object “System.Data.DataTable” $OleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter “Select * from [Sheet1$]“,"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$($args);Extended Properties=”"Excel 12.0 Xml;HDR=YES”";” $RowsReturned = $OleDbAdapter.Fill($DataTable) ForEach ($DataRec in $DataTable) { $SiteTitle = [string]$($DataRec.SiteName) $SiteUrl = [string]$($DataRec.SiteUrl) $WebTemplate = [string]$($DataRec.SiteTemplate) $LangId = "1033" Write-Host "Creating an SPWeb at $($SiteUrl)" New-SPWeb -Url $SiteUrl ` -Language $LangId ` -Template $WebTemplate ` -Name $SiteTitle } } |
The Excel file is very simple, 3 columns called SiteName, SiteUrl and SiteTemplate. Add all the sites you want created, with the URL’s being fully qualified (i.e. http://localhost/sitename) and the appropriate site template name specified. All you then have to do is call the function from your PowerShell script like so:
New-SPWebFromExcel "C:\temp\SiteList.xlsx" |
Painless SharePoint Site creation!
Note: to get the name of the site template you want to use, you can get a handy list of all site templates and their names by running this PowerShell command:
Get-SPWebTemplate | Sort-Object "Name" |