We have SharePoint list that has more than 5000 list items and we need
to export the list to CSV file and then upload to SharePoint document Library,
delete the list items that are already exported.
Here is the PowerShell Script to Export SharePoint List items to CSV file and then Upload to SharePoint Document Library
Add-PSSnapin
Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
###########################Variables######################################
$smtpServer
= "mail.yourcompany.com"
$fromadd
= "Fromaddress@yourcompany.com"
$email1
= "toaddress@yourcompany.com"
$sitepath= "SharePoint
Site Path"
$listname
= "List
Name"
$timer
= (Get-Date
-Format yyy-mm-dd-hhmmss_)
$exportfilepath="File Path \"+ $timer
+ "FileName.txt"
$doclibname
="Document
Library Name"
#####################################################################################
## Step 1- Export SharePoint List
#####################################################################################
$web
= Get-SPWeb
-identity $sitepath
$list
= $web.Lists[$listname]
$ListItemCollection
= @()
$spQuery
= new-object
Microsoft.SharePoint.SPQuery
$camlQuery =
"<Where>
<Eq>
<FieldRef Name='STATUS' />
<Value
Type='Choice'>Shipped</Value>
</Eq>
</Where>"
$spQuery.Query =
$camlQuery
$list.GetItems($spQuery) | foreach {
$ExportItem
= New-Object
PSObject
$ExportItem
| Add-Member
-MemberType NoteProperty
-name “ID” -value $_[“ID”]
$ExportItem
| Add-Member
-MemberType NoteProperty
-name "UK" -value $_["UK"]
$ListItemCollection
+= $ExportItem
}
$ListItemCollection
| Export-CSV
$exportfilepath -NoTypeInformation
#####################################################################################
## Step 2- Upload the file to SharePoint
document Library
#####################################################################################
# Get a variable that
points to the folder
$dlist
= $web.GetFolder($doclibname)
$files
= $dlist.Files
# Get just the name of
the file from the whole path
$filename
= $exportfilepath.Substring($exportfilepath.LastIndexOf("\")+1)
# Load the file into a
variable
$file= Get-ChildItem
$exportfilepath
# Upload it to
SharePoint
$Files.Add($doclibname
+"/"
+ $filename,$file.OpenRead(),$false)
#####################################################################################
## Step 3- Delete the sales order backlog list
itme that is exproted
#####################################################################################
Import-CSV
$exportfilepath -Header
ID,UK | Foreach-Object{
$items =
$list.Items
| Where {$_[“ID”] -eq $_.ID}
foreach($item
in $items)
{
$item.Delete();
break;
}
}
$web.Dispose()
#####################################################################################
if
($error -ne
$null)
{
$msg
= new-object
Net.Mail.MailMessage
$smtp
= new-object
Net.Mail.SmtpClient($smtpServer)
$msg.From =
$fromadd
$msg.To.Add($email1)
$msg.Subject =
"Exporting list error"
$msg.Body =
$error
$smtp.Send($msg)
$error.clear()
exit
}
