SharePoint lists are very powerful and we often need to export list data for our customers. One of the best option to achieve that is by PowerShell. So in this blog post we will cover how to export list in SharePoint via PowerShell. After exported to CSV, it’s easy to convert to an Excel format and do whatever you want. It’s important to say that this script will work on SharePoint Server On-Premises and you will need to enter the server to run it.
But why using a PowerShell if we can do that by the UI? Well, I need to agree with you. What I can answer about that? Basically, you can schedule exporting activities, you can take leave the script running on long lists without short timeouts, etc.
The powershell script to export list via PowerShell
The script follows this flow:
- First initializes the required variables like Site, Web and List title;
- Prepares the final Collection of List Items;
- Creates a simple query based on the Created column;
- Queries the data and converts each list item in an anonymous object;
- Exports the final data into a CSV file;
- Disposes the objects.
The script creates a CSV file inside a “temp” folder in your C directory, but you can change it before running the script in your SharePoint environment.
In conclusion, the script is very flexible and the initial verson was made to give you a starting point. From now on, you can customize the script in order to export list in SharePoint via PowerShell. Finally, if you want to check on other PowerShell scripts in our blog, you can check them here.
References:
Microsoft DevBlogs
It is not mentioned here in this article that in order for the script to succeed there are some prerequisites. The first line “Add-PSSnapin Microsoft.SharePoint.PowerShell” throws this error (if I remove the error suppression): The Windows PowerShell snap-in ‘Microsoft.SharePoint.PowerShell’ is not installed on this computer. After a couple of attempts at resolving this I cannot determine how you got your code to run. I suggest you try running this on a fresh install or a vanilla machine and see how far you get without errors.
Hello Nate Mills,
How are you doing?
I believe you must run this script inside a SharePoint Farm Server. In that way, the PowerShell will work.. But in case you want to export from your local machine and remotely connect to SharePoint, let me work on a post just for you 😉
Best,
Wiliam Rocha
Hello,
Have you been able to figure out how to do this OUTSIDE a SharePoint farm. On my local PC or Windows server.