How to copy SharePoint list items in Powershell

I had a requirement to split out a SharePoint list that had grown to over 200k items into smaller clone lists. Easy I thought, there must be hundreds of blogs online that will give step by step instructions to do just this so a quick internet search should tell me how to do it.

So I searched and I didn’t find any blogs that did quite what I wanted, so here is how I achieved it which I hope can help anybody out who is in the situation I was in;

Firsts I created an empty list template from the master list which has grown so big.

I then made several (in my case 28) lists with an obvious naming convention. (ProductList-Jan13, ProductList-Feb13, etc…)

I made these within the same site however these could be as far apart as different site collections as long as they are within the same farm and can be accessed by the same front end server (where later we will run the PowerShell to move the list items)

With the new framework created all that was left was to move the lists items, in my case I also created new input forms and site pages for each list but I won’t go into detail on that in this blog post.

To move data between lists there are five main ways; Content and structure move, Datasheet copy/paste, PowerShell, Workflow and import from excel.

I decided that the best way to move my data was PowerShell;

  • Content and Structure limits you to 1000 items at a time so for large amounts of items it can be a frustrating and timely method.
  • Datasheet copy and paste is great for small numbers of items but anything over few hundred and internet explorer usually crashes and then there’s the task of manually working out what items copied and which didn’t.
  • Workflows copy data well but you either need to manually kick these off or use PowerShell to start a workflow on all items. I’ve tried running a workflow on all items in a large list before (70k items) and it took 15+ hours before freezing the remaining items on “starting” so I wouldn’t recommend kicking off a workflow on all items in a large list.
  • Excel import can work well, like some of the other methods I found it doesn’t handle large amounts of data well. The reason I didn’t use it in this instance was because my lists had lookup, choice and calculated columns, importing from excel Sharepoint does its best guess at what types of columns each should be but usually falls short so there’s usually a lot of configuration to get the columns right when importing from excel. With 28 lists to create this would have driven me mad so I had to rule out an excel import.

I’m ok at PowerShell I’m not great hence why my first port of call is google when I need to use PowerShell, Usually I find a script close enough to what I want to achieve that I can do some small changes to get exactly what I want. In this case nothing I found was close enough so I had to write a script from scratch;

Before I get into the script I want to recommend using “Windows PowerShell ISE” it makes scripting PowerShell allot clearer and shows any errors before running the script. I now run all of my PowerShell through the ISA even if it’s a script I know works as it also gives start/stop buttons and a window to view what’s going on. If you don’t have PowerShell ISE on your programs menu there are plenty of blogs that will tell you the PowerShell commands to run to import and install it.

So onto what youre most likely here for, the script;

The first thing I had to do in PowerShell was load the Sharepoint PowerShell snap in (if I was using the Sharepoint management module I wouldn’t need to do this but as I want to use ISE I have to specifically load this snapin). The code to load the SharePoint snapin is

“if((Get-PSSnapin | Where {$_.Name -eq “Microsoft.SharePoint.PowerShell”}) -eq $null) {          

Add-PSSnapin Microsoft.SharePoint.PowerShell }”

This checks to see if it is already loaded and if not it will load it.

Next I had to load the Sharepoint site where the source list located;

“$web=get-spweb -identity https://www.sharepoint.com/site

Next to load the source lists located in this site;

“$lista=$web.Lists[“Products”]”

Next I need to load the destination list which is located in this site; (if I was moving between sites or site collections I would have to specify a second spweb here, changing the original $web to $weba and making a second $webb.)

“$listb=$web.Lists[“ProductList-Dec13”]”

(If I were using a $webb I would need to update the reference in this line to that too)

Next I need to tell the script I want to use the items within the Source list I’ve just referenced;

“$items=$lista.items”

Here in the script is where I struggled to find any examples on the internet, in my situation I needed to split the data up into multiple lists. To do this I needed to specify a condition within my data to be true and only copy that data if that condition is true. This way I can modify the destination list and condition to keep running this script to split my main list into the 28 i created.

To create the condition, as a C# programmer my first though was I want an “IF” statement. In PowerShell we use a “where-object” instead specify the condition the object (item) needs to have.

Remember to pipe this script after the $list=$lista.items command;

“| Where-Object {$_[“Product Month”] -eq “December”}”

Next we need to run through each item in the list;

“foreach ($item in $items) {“

Now we need to tell the script that we are going to want to add items it runs through to the destination list;

“$newitem=$listb.items.Add()”

Now the script knows the goal is to add items from the source list to the destination list we need to tell the script which columns we want to include in the item;

$newitem[“Column1”] = $item[“Column1”]

$newitem[“Column2”] = $item[“Column2”]

$newitem[“Column2”] = $item[“Column2”]

In my example the source and destination list use the same column names however they do not have to as long as the column titles on the left are the names in the destination and the column titles on the right are the names in the source, spelt correctly with the correct capitalisation and the column types are the same (i.e. a number field to a number field will work but obviously a date and time field to a number field will not work). You can add as many lines of this code as you need for columns, mine had 15 columns in total.

Next we need to tell the script to run the add item now it has all of the information that we’ve specified;

“$newitem.update() }”

Finally to stick to best practise we need to dispose of the web that we referenced at the top;

“$web.dispose”

And that’s it, running this in PowerShell on your SharePoint front end server will copy the items with the chosen columns from the source list to the destination list. Depending on how many items you are moving and your server spec this could take a while. If you are using PowerShell ISE all you have to do is paste the code into the window and press the green arrow “start” and watch the display window which will show the dispose line at the end.

I hope this will help others doing the same task or just learning PowerShell, rather than just give the script I have tried to explain what is happening line by line. This scrip can be added to, to move the items rather than copy by including a delete statement after the update. Personally I like to check it’s all worked fine before deleting anything so I deleted in datasheet view manually after running this script. What this script doesn’t do which maybe I will in future try to add into it is copying the created by and create fields and copy versions. If you know how to include these please add these to the comments.

Here’s the full script;

PS

Advertisements

One thought on “How to copy SharePoint list items in Powershell

  1. David says:

    Thank you! This is exactly what I was looking for!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s