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://github.com/VikasSukhija/Downloads/blob/master/SPlistEXp.zip
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#############################
Tech Wizard
Tech Wizard 