Export SharePoint List data into CSV file

Hi,

I have found couple of blogs explaning the reverse way of expoting csv file data into SharePoint list. I have accomplished the other way around and hence thought of sharing this with you.

InOrder to export data from SharePoint list into CSV file, lets first query SharePoint list and get the required items and then we will export these items into CSV file.


$spSiteUrl = "http://sharepointwebapplication"
$listtitle="My Custom List"
$spWeb= Get-SPWeb -Identity $spSiteUrl
Function ImportDataFromListToCSV
{
  IF($spWeb -ne $null)
  {
  $list = $spWeb.Lists.TryGetList($listtitle)
  $exportlist = @()
  $items = $list.Items
  Write-Host "Exporting........" -ForegroundColor Yellow
  if($items -ne $null)
  {
    #Here "givenName" column name for CSV file and "Given Name" column name SharePoint List
    $items | %{ select-object -input $_ -prop @{Name='givenName';expression={$_["Given     Name"];}},@{Name='surname';expression={$_["Family Name"];}};}| Export-Csv -Path C:\Demo.csv -NoTypeInformation
    Write-Host "SharePoint list data is exported successfully." -ForegroundColor Green
    $spWeb.Dispose()
 }
 Else
 {Write-Host "No data found in the list "$listtitle -ForegroundColor Yellow}}
}

Enjoy PowerShell,
Isha Jain
 

SharePoint and PowerShell

I have realised the importance and power of PowerShell and hence thought about sharing.

These days I am working very deeply with PowerShell in SharePoint.

Sending an email using PowerShell...

Function SendMail
{
  $smtpServer = "your SMTP server name"
  $logFileName= "C:\test.txt"
  $att = new-object Net.Mail.Attachment($logFileName)
  $msg = new-object Net.Mail.MailMessage
  $smtp = new-object Net.Mail.SmtpClient($smtpServer)
  $msg.From ="test@gmail.com"
  $msg.To.Add("he@gmail.com")
  $msg.To.Add("she@gmail.com")
  $msg.Subject = "Test Mail"
  $msg.Body = "This is a test email.
  $msg.Attachments.Add($att)
  $smtp.Send($msg)
  $att.Dispose()
}

Power of SPQuery Object in SharePoint....

$spSiteUrl = "http://webapplication"
$spWeb= Get-SPWeb -Identity $spSiteUrl

Function EmptySPList
{ Param($listname)
  IF($spWeb -ne $null)
  {
   $list = $spWeb.Lists.TryGetList($listname)
   $caml=""
   $query=new-object Microsoft.SharePoint.SPQuery
   $query.ViewAttributes = "Scope='Recursive'"
   $query.Query=$caml
   $items=$list.GetItems($query)
   $items | % { $list.GetItemById($_.Id).Delete()  }
   Write-Host "Items from list '" $listname "' are deleted successfully." -ForegroundColor Green
  }}

Function FindSpecificSPListItems
{
 Param($list)
 $dateFrom=[Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Today)
 $dateTo=[Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Today.AddMonths(-3))
 $spQuery = New-Object Microsoft.SharePoint.SPQuery
 $spQuery.ViewAttributes = "Scope='Recursive'";
 $spQuery.RowLimit = 100
 $caml = '<Where><And><Leq><FieldRef Name="Date" /><Value  Type="DateTime">'+$dateFrom+'</Value></Leq><Geq><FieldRef Name="Date" /><Value Type="DateTime">'+$dateTo+'</Value></Geq></And></Where><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy>'
 $spQuery.Query = $caml
 $listItems = $list.GetItems($spQuery)
 if($listItems -ne $null)
 {
  #do some stuff
 }}

Cheers,
Isha