Populating a SharePoint List using PowerShell

Article
06/06/2013

Summary: PowerShell can be used to automate tedious and time consuming administrative tasks. In this blog post I show how to use PowerShell to take the contents of a CSV file and populate a SharePoint list.

I’ve been doing some application analysis for one of my customers recently. As part of this analysis the customer had a list of applications, you know the application name, vendor, version and install count type of list (lists that are important during the planning for an OS Upgrade, when its important to consider things like application compatibility). The problem was simple - the customer had its inventory information extracted to a CSV. However, in a CSV file it wasn't very discoverable and useable on the network, and collaboration meant emailing the list around. They were using SharePoint for collaboration, so I suggested loading the data into SharePoint.

So, I needed to populate a SharePoint list from a CSV file. There were a lot of applications in the list, and manually entering the data into SharePoint would have taken hours/days. Problem right? Nope! PowerShell to the rescue! A short while after the decision to use SharePoint had been made I had a PowerShell Script to populate the data automatically into SharePoint. Running the script and populating the list took seconds.

I know that a lot of my customers are starting to see PowerShell as a critical skill for administrators to have, so I thought I’d write this blog to give a breakdown of how I achieved these results.

To understand what we are doing its probably useful to see the SharePoint list (although the could really be any list you wanted – its just a generic SharePoint list). I started with an empty list which looked like this:

As you can see its just a standard list. My list had the columns Application Name, Application Vendor, Application Vendor and Install Count (I know, who’d have thought it given these were the columns I wanted to load from the CSV right?). These names will be important when we get to adding items to the list.

The CSV file is a text file containing four columns – Application Name, Application Vendor, Application Version and Install Count. Before I go any further its important to say that the data in this sample list is just generic data from my lab – it’s not customer data. The data looks like this:

Application Name,Application Vendor,Application Version,Install Count
Microsoft Office Professional Plus 2013,Microsoft,15.0.4420.117,2
Microsoft Silverlight,Microsoft,5.1.1411.0,2
Microsoft Application Virtualization (App-V) Client Service Pack 1,Microsoft,5.0.114.0,2

PowerShell to the Rescue

To use SharePoint commands in PowerShell, you will first need to load the SharePoint PowerShell snapin (at this point its worthwhile pointing out that unless you want to configure PowerShell remoting then you’ll need to run this script on one of SharePoint servers – remoting will probably be a topic for another post one day). To load the PowerShell SharePoint snapin we first need to check whether its already loaded, and if not load it. The following code will load the SharePoint PowerShell snapin:

# Setup the correct modules for SharePoint Manipulation
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin Microsoft.SharePoint.PowerShell
}
$host.Runspace.ThreadOptions = "ReuseThread"

The above code checks to see if the Microsoft.SharePoint.Powershell PowerShell snapin is loaded using the Get-PSSnapin command, and if it isn't it is loaded using the Add-PsSnapin. The last line of the code, sets up efficient memory/thread usage.

It is important to note that the account that connects to SharePoint must have the SharePoint_Shell_Access role (see https://technet.microsoft.com/en-us/library/ff607596.aspx) for this to work.

After the SharePoint PowerShell snapin is loaded we can then open the connection to the SharePoint and to list that we want. The code for that looks like this:

#Open SharePoint List
$SPServer="https://LAB-SPS1"
$SPAppList="/Lists/Application List/"
$spWeb = Get-SPWeb $SPServer
$spData = $spWeb.GetList($SPAppList)

In the above code we connect to SharePoint using the Get-SPWeb command, and then .GetList to load the required SharePoint list (in my example a list called Application List) into an array called $spData.

Next we need to load the inventory data from the CSV file. PowerShell makes reading CSV files a no-brainer – PowerShell has theImport-CSV command. To code to load the CSV file looks like this:

$InvFile="appinvent.csv"
# Get Data from Inventory CSV File
$FileExists = (Test-Path $InvFile -PathType Leaf)
if ($FileExists) {
"Loading $InvFile for processing..."
$tblData = import-csv $InvFile
} else {
"$InvFile not found - stopping import!"
exit
}

The code snippet above checks to see if the file appinvent.csv exists (if it doesn't it errors out and stops – I mean hey if the file doesn't exist, there isn't much to load into SharePoint is there?). If the file exists the Import-CSV command is called to load the entire CSV into a variable called $tblData. Import-CSV takes the first line of data and turns the information into the column headings. In my example $tblData ends up being an array with four columns with the columns named Application Name, Application Vendor, Application Version and Install Count. If your CSV file doesn't have a header row, just add –Header ColumnName1,ColumnName2, ColumnName3 to the Import-CSV command.

From here it’s a matter of looping through the data contained in $tblData, loading each line into the SharePoint list. The code for that looks like this:

# Loop through Applications add each one to SharePoint
"Uploading data to SharePoint...."
foreach ($row in $tblData) {
"Adding entry for "+$row."Application Name".ToString()
$spItem = $spData.AddItem()
$spItem["Application Name"] = $row."Application Name".ToString()
$spItem["Application Vendor"] = $row."Application Vendor".ToString()
$spItem["Application Version"] = $row."Application Version".ToString()
$spItem["Install Count"] = $row."Install Count".ToString()
$spItem.Update()
}

In the above code the $spItem = $spData.AddItem() PowerShell code is the same as pressing the “Add Item” button in a SharePoint list. The next four lines populate the columns in SharePoint from the CSV. After populating the columns, $spItem.Update() is used to commit the information to SharePoint – its the equivalent to clicking Save on a New Item dialog box.

Finally, we need to close our connection to SharePoint, which can be done by the following code:

"Upload Complete"
$spWeb.Dispose()

So there it is … a PowerShell script to take a CSV file and upload it to SharePoint. After running on that data the SharePoint list now looks like this:

As you can see all the CSV data is loaded. Here is the complete code for the PowerShell script:

#--------------------------------------------------------------------
# Name: Load CSV into SharePoint List
# NOTE: No warranty is expressed or implied by this code – use it at your
# own risk. If it doesn't work or breaks anything you are on your own
#--------------------------------------------------------------------

#Open SharePoint List
$SPServer=https://LAB-SPS1
$SPAppList="/Lists/Application List/"
$spWeb = Get-SPWeb $SPServer
$spData = $spWeb.GetList($SPAppList)

$InvFile="appinvent.csv"
# Get Data from Inventory CSV File
$FileExists = (Test-Path $InvFile -PathType Leaf)
if ($FileExists) {
"Loading $InvFile for processing..."
$tblData = Import-CSV $InvFile
} else {
"$InvFile not found - stopping import!"
exit
}

# Loop through Applications add each one to SharePoint

"Uploading data to SharePoint...."

foreach ($row in $tblData)
{
"Adding entry for "+$row."Application Name".ToString()
$spItem = $spData.AddItem()
$spItem["Application Name"] = $row."Application Name".ToString()
$spItem["Application Vendor"] = $row."Application Vendor".ToString()
$spItem["Application Version"] = $row."Application Version".ToString()
$spItem["Install Count"] = $row."Install Count".ToString()
$spItem.Update()
}

"---------------"
"Upload Complete"

$spWeb.Dispose()

Populating a SharePoint List using PowerShell

PowerShell to the Rescue

Additional resources