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"