<

Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Export data from SharePoint 2010 List using Management Shell

Published on
23,531 Points
17,131 Views
4 Endorsements
Last Modified:
Approved
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
Comment
2 Comments
 

Expert Comment

by:JamesHeathcote1981
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?
0
 
LVL 4

Author Comment

by:Eldrick Vance
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Join & Write a Comment

This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month