Export data from SharePoint 2010 List using Management Shell

Eldrick VanceSharePoint Data Program Analyst
CERTIFIED EXPERT
Published:
Updated:
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Data and save it in a csv file.  In Part2, I will explain how to schedule it on a periodic basis.  

After some research I found Roi Kolbinger's blog "I Will Share my Point: Export SharePoint 2010 List to Excel with PowerShell.  The problem with this is that it will only extract the Title field.  I was able to create a PowerShell script to extract the data using Roi's script as the base for my script and with a little help from a developer, named Miguel Wood,  who told me "Yes.  The .Items will only export the Title column.  To export the list, try creating and using a PSObject."  
Now that we have some background information, let’s create a SharePoint Management Shell script to extract the data from a SharePoint 2010 List and into a csv file.

1.  Open notepad and paste the following code customizing it for your environment.
$MyWeb = Get-SPWeb "http://sitename"
$MyList = $MyWeb.Lists["ListName"]
$exportlist = @()
$Mylist.Items | foreach {
$obj = New-Object PSObject -Property @{
            “Column1” = $_["Column Name in SharePoint"]
           "Column2" = $_["Column Name in SharePoint"]                  
}
$exportlist += $obj
$exportlist | Export-Csv -path 'C:\FilePath\Filename.csv'
}
*Notes:   Http://sitename is the url to the site where the list is located and ListName is the name of the List that contains the data you would like to extract.
Column1 and Column2 are the names of the columns you want to be named in the CSV file.  Column Name in SharePoint is the actual column named stored in the SharePoint Database. For instance if you renamed the Title column to Name, your code should resemble the following:
“Name” =$_[“Title”}
Export-Csv – path is the location you want the script to save the file.
2.  After you have made the corrections/customizations Save the file with extension ps1.  IE:  Export.ps1.
3.  Place or copy the file to a drive located on the SharePoint Server you will run the script.  We utilize our Application Server.
4.  Now that we have created the script, manually run it to test the file.  RDP to the server, open a SharePoint Management Shell prompt, go to the directory where you saved the file and run the following command: .\Filename.ps1

Now that you know how to extract data from a SharePoint 2010 list using SharePoint Management Shell, you can also create a scheduled task to run the file using the Windows Task Scheduler.  I will explain how you accomplish this in Part 2. Coming Soon I hope.

References:  
Reference 1:  Roi Kolbinger - I Will Share my Point: Export SharePoint 2010 List to Excel with PowerShell (http://iwillsharemypoint.blogspot.com/2011/02/export-sharepoint-2010-list-to-excel.html)

Reference 2:  Miguel Wood - Developer

Thanks,
Eldrick
4
20,731 Views
Eldrick VanceSharePoint Data Program Analyst
CERTIFIED EXPERT

Comments (2)

Hi Miguel

Thank you for your article, I will be attempting shortly.

I wondered if the following was also possible as we need to export our job data into a csv file for our accounting software which is Soloman.

We would need to filter items which have a tick box placed next to ready for invoice in the list.

Further to that, we need a column which is called invoiced which gets selected once the export has run so that duplicate entries don't get sent to the csv file.

Is this possible?
Eldrick VanceSharePoint Data Program Analyst
CERTIFIED EXPERT

Author

Commented:
Hello James. Thanks for taking time to read my article.  Unfortunately I do not know how to make the script do what you need.  I will do some research and see if I can assist.  

Thanks,
Eldrick

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community