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
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 you accomplish this in Part 2. Coming Soon I hope.
References:
Roi Kolbinger - I Will Share my Point: Export SharePoint 2010 List to Excel with PowerShell (http://iwillsharemypoint.
Miguel Wood - Developer
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
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 you accomplish this in Part 2. Coming Soon I hope.
References:
Roi Kolbinger - I Will Share my Point: Export SharePoint 2010 List to Excel with PowerShell (http://iwillsharemypoint.
Miguel Wood - Developer