During the last few months I have been asked a few times to help think of a way to edit list values for lists that contain a lot of data. I immediately thought of PowerShell. This is why I want to show you what you can do with PowerShell.
Used set up for this demo
I have created a test environment for this demonstration. I will be using the following:
– http://portal.contoso.com (SPWeb URL)
– Demo List (Custom List). This list has 1 extra added column: Information (Multiple lines of text)
To work with a list, you will have to start off by retrieving the list in PowerShell. For demonstration purposes, I will try to make it as easy and clear as possible:
#Add SharePoint PowerShell Snapin which adds SharePoint specific cmdlets
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
#Variables that we are going to use for list editing
$webURL = <a href="http://portal.contoso.com">http://portal.contoso.com</a>
$listName = "Demo List"
#Get the SPWeb object and save it to a variable
$web = Get-SPWeb $webURL
#Get the SPList object to retrieve the "Demo List"
$list = $web.Lists[$listName]
These are the basics. With this, we can start to add items to this list or modify existing items.
Add items to the Demo List
So we got the list in our $list variable, now what?
To add an item to this list, we will need to use the Items.Add() method.
#Create a new item
$newItem = $list.Items.Add()
#Add properties to this list item
$newItem["Title"] = "My first item!"
$newItem["Information"] = "This is a multiple lines of text field `nSo I can use multiple lines"
#Update the object so it gets saved to the list
Here is the output:
The `n part in: $newItem[“Information”] = “This is a multiple lines of text field `nSo I can use multiple lines”, is a New line. This works if you use the “Plain text” type, but not when you use “Rich text” or “Enhanced rich text”.
That was pretty easy right?
Add attachments to items
In the comments section, Nino asked if it was possible to add attachments for each new item created. Here is the way to do this.
$filebytes = [System.IO.File]::ReadAllBytes("D:\dummyfile.txt")
Edit existing list items
Now let’s say you want to edit the Information for this “first item”. We can do this with PowerShell too. Of course, if you would only change 1 item, you should do it by using the GUI, but what happens if you have to do the same thing for 5000+ items?
#Get all items in this list and save them to a variable
$items = $list.items
#Go through all items
foreach($item in $items)
#If the "Title" column value equals "My first item!" do something with it
if($item["Title"] -eq "My first item!")
#Change the value of the "Title" column
$item["Title"] = "My first edited item!"
#Update the item
After running this part, we get the following result:
Hooray! It worked.
Now imagine all the things you can do with PowerShell and huge lists!
You could add column information for every item, or have a .csv or xml file which contains the items that should be changed. The possibilities are endless!