Sunday, February 08, 2015

I constantly tear down and rebuild sites in my dev environment, and re-populating lists with test data is a royal pain.  One day, as CEO, I'll have "people" to take care of this sort of thing.  Alas, I have but my own wits to rely on for now.

The most concise and flexible solution I could find is using Powershell to load the data from a CSV file.  I usually create the list data in Excel, and Save As a .CSV file.  It's a huge time saver.  The first line of the file specifies the display names of the list fields to populate.

For example, this CSV data is for a Links list.  Note the URL field in all caps.  The display names are case sensitive.

Title,URL,Description
SharePointificate,http://sharepointificate.blogspot.com,A very cool blog
Funny Cat Videos,http://funnycatvideos.net,Just click it.
Angry Birds,https://www.angrybirds.com,U mad?

There's an auto-magic Import-CSV Powershell cmdlet that loads a CSV file into a list of objects.  The properties of each object correspond to the display names defined in the file's header line.

The links CSV above would produce a list of 3 objects.  Each object will have a .Title, .URL, and .Notes property.  The following code outputs "A very cool blog":

$data = Import-CSV links.csv
Write-Host $data[0].Notes

We can then iterate over each object's properties with .psobject.properties, and map their Name / Value to a new SPListItem.

The sample script below only handles field types where we can directly set the text value.  Some complex types like Taxonomy or Multichoice will require special logic.  To handle those cases, check $list.Fields[$csvField.Name].Type.  Refer to this Technet article for code samples for setting every type of SharePoint field.

param(
    [string]$WebUrl = $(Read-Host -prompt "Web Url"),
    [string]$ListName = $(Read-Host -prompt "List Name"),
    [string]$CsvPath = $(Read-Host -prompt "Path to CSV file")
)


# Load SP snapin if needed

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null) 
{
    Add-PSSnapin "Microsoft.SharePoint.Powershell"
}

$web = Get-SPWeb $WebUrl
$list = $web.Lists[$ListName]; 

# Load CSV into object

$csvItems = Import-Csv $CsvPath

# Iterate over items to import

foreach($csvItem in $csvItems)
{
    # Create new list item
    $newItem = $list.items.add();
 
    # Iterate over fields to import
    foreach($csvField in $csvItem.psobject.properties)
    {
        if(![string]::IsNullOrEmpty($csvField.Value))
        {
            # Set field value
            $newItem[$csvField.Name] = $csvField.Value;
        }
    }

    $newitem.update();
}