<

Export data from SharePoint 2010 List using Management Shell

Published on
24,526 Points
18,126 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

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Join & Write a Comment

Learn how to collaborate with office 365 Office Online
Microsoft Office 365 Backup and Restore Solution by SysTools to export Office 365 mailbox to PST / EML file format on Windows OS. On Mac, tool backup O365 to PST / MBOX / MSG / EML / EMLX file formats. Not only this, restore option helps to import s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month