<

Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Export data from SharePoint 2010 List using Management Shell

Published on
23,130 Points
16,730 Views
4 Endorsements
Last Modified:
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Join & Write a Comment

Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month