Populating a SharePoint List using PowerShell

Artikkeli
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()

Comments

Anonymous
January 01, 2003
@SirStevo: Answers to your questions as follows:

Absolutely. To do that you'd need to create a function to look through the list held in $SPData checking to see if the item existed (I have previously done this using a date column field) Then have an if statement such as: if fnEntryExist($keyfield) { #don't do AddItem } else {

do Additem stuff as it appears in the original item

} 2. The CSV I was referencing was in the same directory as the PS1 script - but its defined in the $InvFile variable, so could be anywhere such as $InvFile = "\servershareappinvent.csv" or enhance the script to ask the user for the name and location of the inventory file! Hope that helps.

Anonymous
January 01, 2003
Hi Steve, This post is going to be my saving grace. I have two questions though:

Could I use the same concept to update list columns for existing documents in a document library?
Where exactly is the CSV file stored that you are referencing? Im not a developer by trade, but I am having to resort to some custom code to complete a SharePoint Migration. Any help would be appreciated. Thanks, Steven

Anonymous
January 01, 2003
Martin, If I understand your question you are asking how can I take a field from a CSV (in your example called DocumentFileName and turn it into a clickable link in a field? The answer is simple - turn it into a UNC path! If the server that hosts the C:content directory is called SRV1, and C:content is shared as CONTENT$ for example, then all you would need to do is convert C:contentMicrosoftOfficeProfessionalPlus2013.pdf to \SRV1CONTENT$MicrosoftOfficeProfessionalPlus2013.pdf that will produce a field that you can then click. It is important to note that it won't open in SharePoint .... but instead be launched by your browser on your local machine (in the example above it will launch Acrobat Reader). Hope that helps. Steve
Anonymous
January 01, 2003
Hi, How can I link a data path to the actual data in the SharePoint list form the csv? For instance, let me edit your csv: DocumentFileName,Application Name,Application Vendor,Application Version,Install Count C:contentMicrosoftOfficeProfessionalPlus2013.pdf,Microsoft,15.0.4420.117,2 C:contentMicrosoftSilverlight.pdf,Microsoft,5.1.1411.0,2 C:contentMicrosoftApplicationVirtualization.pdf,Microsoft,5.0.114.0,2 The SharePoint list will contain the documents (application name .pdf) with the columns: Application Vendor,Application Version,Install Count. Is there a way that once I click the (application name .pdf) the list will look for the data path (C:contentMicrosoftOfficeProfessionalPlus2013.pdf) and open it in SharePoint?
Anonymous
June 10, 2013
Very interesting thanks.
Anonymous
October 15, 2013
so, I'm having an issue. I modified this code a little, but, it is practically the same. It is overwriting instead of adding an item. Any ideas? $SPServer = "http://mysite/" $SPApplist="/Lists/stupidlist/" $spweb = get-spWeb $SPServer $spData = $spweb.GetList($SPApplist) $spAdd = $spData.AddItem() $locations = get-content C:templocations.txt foreach ($location in $locations){ echo "adding item $location" $spAdd["Title"] = $location $spAdd.Update() } I just end up with one item, the last one in locations.txt Thank you! Devon Baldwin
Anonymous
October 15, 2013
Figured it out! $SPAdd needs to be inside the loop. Thanks for the excellent article!
Anonymous
November 24, 2013
The comment has been removed
Anonymous
April 09, 2014
Hi,

Can this importing of a csv file be run on a regular basis for example a windows task?

Thanks
Anonymous
July 08, 2014
This is really helpful and works like a charm at the first try. Thank you really much !
Anonymous
July 26, 2014
Many, many thanks for this complete explanation!
Anonymous
March 11, 2015
Hi there
I've a lot of lists in sharepoint and a lot of csv files containing data to put in the lists.
I want wo make it more flexible. So loop through the csv files, loop through the header fields and loop through the rows. Instead of making a script specially for each list/csv combination.

The name of the csv = the name of the list.
The name of the header fields are the names of the fields in the list.

Is this possible?
Anonymous
May 29, 2015
Hi, great post but I am struggling to understand the requirements to automate tasks like this against SharePoint if you A) are not a SharePoint Farm Admin
B) dont have SharePoint installed.

How do I as a consumer and Site collection administrator of a sharepoint site I own automate these types of activities on my own lists?

It seems I have to use the PS snap in which is only available if you have SharePoint installed, and I cannot use PSremoting because I am not part of the SharePoint implementation team so don't have access to do so.
Anonymous
June 30, 2015
Is it possible to populate the list from a .txt file instead of a csv file? Many thanks.
Anonymous
October 15, 2015
Hii Steve,

Thank you foe this code.

I have a query:

1. Can the changes made in the current excel will automatically reflect in SharePoint list ?? If not what is solution for that ????
Anonymous
January 26, 2016
The .GetList does not work for me. I get an error that says GetList : Method invocation failed because [System.Object[]] doesn't contain a
method named 'GetList'.

Jaa

Populating a SharePoint List using PowerShell

PowerShell to the Rescue

Comments

do Additem stuff as it appears in the original item

Lisäresursseja