Export Sharepoint List to CSV

This script will assist in exporting the Sharepoint List contents in csv format.

It would be useful in scenarios where you want to export list & further feed some other system to complete further processing.

Example which is being shared was used in one of the automation where IT people can fill the form for setting up alert on service accounts, this information further gets log into the list, from where we extract & feed it to particular path daily.

This csv is than further processed by two other scripts.

For now I am just sharing the part where we exported CSV from the list.

List example:

Extract the solution zip from below link:

https://gallery.technet.microsoft.com/scriptcenter/Export-Sharepoint-List-to-b60f3848

Update the .ps1 file with location of script logs & path where CSV to be saved for other system to process

$dir= “E:\scripts\SPlistEXp\logs”
$netpath1 = “\\server01\c$\scripts\AdAccountexp\Accountexpiry.csv”

$netpath2 = “\\server01\c$\scripts\speventid\Accountexpiry.csv”

Update the sharepoint site details/ List name

$web = Get-SPWeb “http://teamsite.lab.com/sites/accexpAlert”

$list = $web.lists[“AccexpAlert”]

Modify the below code as per your list values

#########################Get items so that these can be exported first##################

$items | foreach-object { $SamaccountName = $_[‘Title’]

$Email1 = $_[‘EmailAddress1’]

$Email2 = $_[‘EmailAddress2’]

$Oncall = $_[‘OnCall’]

$coll = “”| select Account,Email1,Email2,Oncall
$coll.Account = $SamaccountName

$coll.Email1 = $Email1

$coll.Email2 = $Email2

$coll.Oncall = $Oncall

$collection += $coll
}
}

###############################################Export CSV##################

Logs will be retained for 90 days, you can also change this value inside the script.

Now you can just schedule the script or run it by using batch file.

CSV will be extracted in ArchiveCSV folder as well as saved to other paths.

Let me know if there are any queries.

########################################################################################### 
##           Script to export List in CSV    
##           Author: Vikas SUkhija                                                              
##           Reviewer:                                                                 
##           Review Date: 05-19-2016   
##          
##            
########################################################################################### 
#############################Log Files##################################################### 
 
$date = get-date -format d 
$date = $date.ToString().Replace(“/”, “-”) 
$time = get-date -format t 
$month = get-date  
$month1 = $month.month 
$year1 = $month.year 
$time = $time.ToString().Replace(":""-"$time = $time.ToString().Replace(" """) 
 
$log1 = ".\Logs" + "\" + "Splistexport_" + $date + $time + "_.log" 
 
$output1 = ".\Archivecsv" + "\" + "Accountexpiry_" + $date + "_" + $time + ".csv" 
 
$logs = ".\Logs" + "\" + "Powershelllog" + $date + "_" + $time + "_.txt" 
 
$limit = (Get-Date).AddDays(-90) #for Retention 
 
$dir"E:\scripts\SPlistEXp\logs" 
 
$netpath1 = "\\server01\c$\scripts\AdAccountexp\Accountexpiry.csv" 
$netpath2 = "\\server01\c$\scripts\speventid\Accountexpiry.csv" 
 
################################Add Sharepoint Shell##################################### 
 
#Start-Transcript -Path $logs  
 
If ((Get-PSSnapin | where {$_.Name -match "SharePoint.PowerShell"}) -eq $null) 
{ 
    Add-PSSnapin Microsoft.SharePoint.PowerShell 
} 
 
 
######################################get List items to be delted######################## 
 
$web = Get-SPWeb "http://teamsite.lab.com/sites/accexpAlert" 
$list = $web.lists["AccexpAlert"$collection = @() 
 
$items = $list.items 
$itemscount = $items.count 
$itemscount 
$date = get-date 
Add-Content $log1 "$date Reading items in the List" 
$date = get-date 
Add-Content $log1 "$date $itemscount items in the List" 
 
if($items -eq $null) 
{ 
Write-host "nothing to process, script will exit" 
$date = get-date 
Add-Content $log1 "$date nothing to process, script will exit" 
$date = get-date 
Add-Content $log1 "$date Finished processing the requests" 
exit 
 
} else { 
 
$date = get-date 
Add-Content $log1 "$date Collecting the columns from the list item " 
 
#########################Get items so that these can be exported first################## 
$items | foreach-object { 
    $SamaccountName = $_['Title'] 
        $Email1 = $_['EmailAddress1'] 
    $Email2 = $_['EmailAddress2'] 
    $Oncall = $_['OnCall'$coll = ""| select Account,Email1,Email2,Oncall 
 
$coll.Account = $SamaccountName 
$coll.Email1 = $Email1 
$coll.Email2 = $Email2 
$coll.Oncall = $Oncall 
$collection +$coll 
 
} 
 
} 
###############################################Export CSV################################# 
 
 
$date = get-date 
Add-Content $log1 "$date Export List items to CSV" 
 
$collection | export-csv $output1 -notypeinformation 
 
copy $output1 $netpath1 -confirm:$false 
 
timeout 5 
 
copy $output1 $netpath2 -confirm:$false 
 
###########################Recycle##########################################  
  
$path = $dir   
   
Get-ChildItem -Path $path  | Where-Object {    
$_.CreationTime -lt $limit } | Remove-Item -recurse -Force   
  
############################################################################## 
 
#stop-transcript  
 
 
#########################################Script finished#############################

Regards

Sukhija Vikas

http://msexchange.me

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s