Export SharePoint List Items to CSV using PowerShell
Requirement: Export SharePoint List items to a CSV file.
Export SharePoint list data to CSV using PowerShell:
This script exports SharePoint list to csv using PowerShell. It retrieves all list items, Filters it based on the provided column value and then creates a property to hold the list item values and then appends the objects which holds the list item values to an array.
Finally, using the Export-CSV Cmdlet, we are exporting the data to CSV file.
Export All fields in the list to Excel using PowerShell
In an another case, required to export all fields from the user information list (UIL) to CSV
This script exports all items in the UIL to CSV format. Here is the screenshot of the CSV file generated in Excel.
Export Large List data to CSV file in Batches:
When you have a larger list, reading and exporting all data in one stretch may end-up in memory and performance-related issues. So, here is how you can export list items in batches:
Export SharePoint list data to CSV using PowerShell:
This script exports SharePoint list to csv using PowerShell. It retrieves all list items, Filters it based on the provided column value and then creates a property to hold the list item values and then appends the objects which holds the list item values to an array.
Finally, using the Export-CSV Cmdlet, we are exporting the data to CSV file.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue #Get the Web $web = Get-SPWeb -identity "http://sharepoint.crescent.com/sites/Operations/" #Get the Target List $list = $web.Lists["Monthly Schedule Log"] #Array to Hold Result - PSObjects $ListItemCollection = @() #Get All List items where Status is "In Progress" $list.Items | Where-Object { $_["Status"] -eq "In Progress"} | foreach { $ExportItem = New-Object PSObject $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"] $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"] $ExportItem | Add-Member -MemberType NoteProperty -name "Status" -value $_["Status"] $ExportItem | Add-Member -MemberType NoteProperty -name "Priority" -value $_["Priority"] #Add the object with property to an Array $ListItemCollection += $ExportItem } #Export the result Array to CSV file $ListItemCollection | Export-CSV "c:\ListData.txt" -NoTypeInformation #Dispose the web Object $web.Dispose()
Export All fields in the list to Excel using PowerShell
In an another case, required to export all fields from the user information list (UIL) to CSV
Add-PSSnapin Microsoft.SharePoint.PowerShell –ErrorAction SilentlyContinue #Variables $SiteUrl="http://intranet.crescent.com" $ListName="User Information List" $OutPutFile = "C:\Temp\ListData.csv" #Get Web and List $web = Get-SPWeb $SiteUrl $List = $Web.Lists[$ListName] Write-host "Total Number of Items Found:"$List.Itemcount #Array to Hold Result - PSObjects $ListItemCollection = @() #Get All List items $List.Items | ForEach { write-host "Processing Item ID:"$_["ID"] $ExportItem = New-Object PSObject #Get Each field foreach($Field in $_.Fields) { $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $_[$Field.InternalName] } #Add the object with property to an Array $ListItemCollection += $ExportItem } #Export the result Array to CSV file $ListItemCollection | Export-CSV $OutPutFile -NoTypeInformation Write-host -f Green "List '$ListName' Exported to $($OutputFile) for site $($SiteURL)"
This script exports all items in the UIL to CSV format. Here is the screenshot of the CSV file generated in Excel.
Export Large List data to CSV file in Batches:
When you have a larger list, reading and exporting all data in one stretch may end-up in memory and performance-related issues. So, here is how you can export list items in batches:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue #Set config variables $WebURL="http://projects.crescent.com" $ListName ="Projects" $OutPutFile = "C:\temp\Report.csv" #Delete the Output File if exists If (Test-Path $OutPutFile) { Remove-Item $OutPutFile } #Get Web and List Objects $Web = Get-SPWeb $WebURL $List = $Web.Lists[$ListName] Write-host "Total Number of Items Found:"$List.Itemcount #Define the CAML Query $BatchSize = 500 $Query = New-Object Microsoft.SharePoint.SPQuery $Query.ViewXml = @" <View Scope='Recursive'> <Query> <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy> </Query> <RowLimit Paged='TRUE'>$BatchSize</RowLimit> </View> "@ $Counter = 0 #Process items in batch Do { #Get List Items $ListItems = $List.GetItems($Query) $Counter = $Counter+$ListItems.Count Write-Progress -PercentComplete ($Counter / ($List.ItemCount) * 100) -Activity "Exporting List Items of '$($List.Title)'" -Status "Processing Items $Counter of $($List.ItemCount)" #Array to Hold Result - PSObjects $ListItemCollection = @() #Get All List items $ListItems | ForEach { #write-host "Processing Item ID:"$_["ID"] $ExportItem = New-Object PSObject #Get Each field - Exclude hidden Foreach($Field in $_.Fields | Where {!$_.hidden}) { $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $_[$Field.InternalName] } #Add the object with property to an Array $ListItemCollection += $ExportItem } #Export the result Array to CSV file $ListItemCollection | Export-CSV $OutPutFile -NoTypeInformation -append $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition } While($Query.ListItemCollectionPosition -ne $null) Write-host "List data exported to CSV Successfully!" -f Green
I like your script. What if I just want to export a specific view instead of all the list items? I'm pretty sure it's just a minor tweak to the code but I couldn't figure it out. Thanks.
ReplyDeleteSimple,
Delete#Get the View
$view = $list.Views["YOUR-VIEW-NAME"]
#Get All items from the view
$items = $list.GetItems($view)
hi boss, your code work great!
Deletei also want to show only the specific view. but, it still showing all of the view.
this is my code, i'm right? if i want to make it same with specific view, i need to rearrange the exportitem align. What i want is, if i change at view (the arrangement of column) without need to change hardcode, so it will change it according to the view. But it's not showing.
#Get the Target List
$list = $web.Lists["test"]
#Get the View
$view = $list.Views["Today"]
#Get All items from the view
$items = $list.GetItems($view)
#Array to Hold Result - PSObjects
$ListItemCollection = @()
#Get All List items where Status is "In Progress"
$items | foreach {
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -name "Full Name" -value $_["Title"]
$ExportItem | Add-Member -MemberType NoteProperty -Name "Contact Number" -value $_["Contact Number"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Id1" -value $_["Id1"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Id2" -value $_["Id2"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Id3" -value $_["Id3"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Reference Number" -value $_["Reference Number"]
#Add the object with property to an Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $savePath -NoTypeInformation
#Dispose the web Object
$web.Dispose()
Thank you for the best code! :)
It worked. Thanks!
ReplyDeleteHi Rajack,
ReplyDeleteYour Script is really Great and very useful for beginners also .
Am new to Sharepoint . We are using sharepoint Foundation (2010)Server-1 and Server-2. I have a scenario. I need to export a filtered items in a list from one server and moved it to another server. After moved the items to 2nd server, the particular items will be removed from 1st server.
For Eg :
In 1st Server My list name is TestList and have a lot of fields one of while is Stage. When field Stage ="Archive" it must be moved to 2nd server which is having a same list template with name "ArchiveTestList". After moving it the particular rows will be removed from 1st server List.
Please Help me. Thanks in Advance.
Vaishnavi
Hi There,
DeleteWhen you say: 1st Server and 2nd server, Are they different SharePoint environments? If so, You can write a PowerShell script to do copy-move operations. However, This script would be either scheduled basis or On-demand, But not on list data update event!
Hi Rajack,
ReplyDeleteMany Thanks for your response. I achieve it through the Powershell Script.
But when i try to run it via batch file and invoke the Administrator: Sharepoint 2010 management shell and through that when i run myscript.ps1 it worked fine on Manually running the batch file.
But when i try to execute my batch file through vb.net code it shows the following error in powershell : "The term 'Get-SPWeb' is not recognized as the name of a cmdlet,function,script file, or operable program ". Am also added "Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue" in my script.
How to resolve it. Help me.
Thanks.
Vaishnavi.
Great script! Thank you for sharing this. How do I modify this to only export a specific Date?
ReplyDeleteYou can filter list items using SPQuery. Here is an example: How to use SPQuery in SharePoint PowerShell
DeleteNice PoweShell script, it is really very helpful for exporting all items from Sharepoint list to csv files format. I tried the following above script to export SharePoint list data into CSV files and I also found good application (http://www.lepide.com/sharepoint-audit/) which enables to automate export reports and get real time alerts on detecting changes to servers, farms, content, groups, users, permissions, sites, lists, libraries, files/folders, etc.
ReplyDeleteHi rajack,
ReplyDeletenice script .
I m trying to use for a view in a list.
i dont need any status .
How can i achieve this ?
Hi Sasikanth,
DeleteSure!
#Get the View
$view = $list.Views["YOUR-VIEW-NAME"]
#Get All items from the view
$items = $list.GetItems($view)
Get List Items from SharePoint View in PowerShell
HI Rajack,
Deleteit worked but the script is not pulling whole items .
its pulling only the first row only
The given code pulls all rows with status="In progress" { $_["Status"] -eq "In Progress"} . Remove that part and try. Line # 13 should be:
Delete$list.Items | foreach {
Instead of:
$list.Items | Where-Object { $_["Status"] -eq "In Progress"} | foreach {
HI Rajack,
ReplyDeleteI got one more issue . export list is getting one of the columns as 72465.1234 instead of 72465
That's because of Excel's column formatting!
DeleteHi Rajack, I am trying to run your code in window poweshell. It is giving below error.. I am sharepoint user and trying import lists into CSV file... How do I overcome below error.. Thanks
ReplyDelete"Get-SPWeb : The term 'Get-SPWeb' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again."
@Again Reddy,
ReplyDeleterun this below code before executing/calling the sharepoint script.
add-pssnapin microsoft.sharepoint.powershell
Love you script! I do have a question. Is there a way when export dates to exclude the time (my output has [date, 0:00])? Also, some of my fields which have names it exports and id number and #. (ex: 755#,John Smith). Is there a way to exclude that info?
ReplyDeleteFor column type like People Picker and Lookup, You'll have retrieve column values bit different. Refer How to retrieve People Picker column values using PowerShell
DeleteI want to export pages library data, What if we have data inside folders.
ReplyDeleteI want to export each folder data to separate csv file.
Hi,
ReplyDeleteYour code is awesome, I have folder hierarchy in Document Library, So When I execute the code it is not exporting data inside folders. How can I achieve this please help me.
Hi Satya, Here you go: Export Document Library File-Folder-SubFolder structure to CSV
DeleteThanks:) Rajak, Great work. You killed my frustration.
ReplyDeleteHi Rajak,
ReplyDeleteI need to extract columns(fields) which are available only in the default view. Could you please advise the change in the script. Also I don't want internal name to be there in extracted report rather displayname. I tried
$ExportItem | Add-Member -MemberType NoteProperty -name $Field.DisplayName -value $_[$Field.InternalName]
But it didn't work.
Kindly advise.
Hi Rajak,
ReplyDeleteI need to extract columns(fields) which are available only in the default view. Could you please advise the change in the script. Also I don't want internal name to be there in extracted report rather displayname. I tried
$ExportItem | Add-Member -MemberType NoteProperty -name $Field.DisplayName -value $_[$Field.InternalName]
But it didn't work.
Kindly advise.
Hi Saurav, Here is the code you wanted:
Delete$web = Get-SPWeb "http://portal.crescent.com"
$list = $web.Lists["Academy"]
$DefaultView = $list.Defaultview
$ViewFields = $DefaultView.viewFields
#Array to Hold Result - PSObjects
$ListItemCollection = @()
$list.items | foreach {
$ExportItem = New-Object PSObject
foreach($field in $ViewFields)
{
$ExportItem | Add-Member -MemberType NoteProperty -name $Field -value $_[$Field]
}
#Add the object with property to an Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV "c:\results.csv" -NoTypeInformation
Question, how would I change it so I'm able to export different types of columns such as dates, a text field or a user filed otherwise its throwing an error.
ReplyDeleteBased on the column type, You have to retrieve its value differently. E.g. For column type like People Picker and Lookup. Search this blog for such scripts!
DeleteThanks! Nice script.
ReplyDeleteWhen my sharepoint list items are blank. CSV is coming without header.
Can we pull the list items with headers even if the list items are blank.
Vivek
Technically, Yes! You can export SharePoint columns Names to CSV file. Refer PowerShell to Get All Column Names in SharePoint List
DeleteHow do i get the complete urls of all the items in the same script. I want the item url and not the id or title.
ReplyDeleteHere you go: $ItemURL = $("{0}{1}?ID={2}" -f $Web.Url.Replace($Web.ServerRelativeUrl,''), $Item.ParentList.DefaultDisplayFormUrl,$Item.ID)
DeleteHello Salaudeen,
ReplyDeletei would want to export all list items with the data to CSV file.
i Ran your script which you posted "Export All fields in the list to Excel using PowerShell"
but it is giving empty file. could you please help me to export all list items with the data to CSV file.
thank you in advance !!
#Read more: http://www.sharepointdiary.com/2013/04/export-sharepoint-list-items-to-csv-using-powershell.html#ixzz5eZL9L1y6"
Hello,
ReplyDeletei would want to export all items with the data to CSV file.
i ran your script which you posted " Export All fields in the list to Excel using PowerShell"
#Read more: http://www.sharepointdiary.com/2013/04/export-sharepoint-list-items-to-csv-using-powershell.html#ixzz5eZNKGqor
but i got the empty csv file. kindly help me.
i have updated the URL and list name like
$SiteUrl="myurl"
$OutPutFile = "C:\UserInfoList.csv"
#Get Web and User Information List
$web = Get-SPWeb $SiteUrl
$UserInfoList = $Web.listname
is there anything else do i need to change..?
Hi There,
DeleteSecond Script has been updated for easier use. Just change these three variables: $Web, $List, $OutputFile
Hi, I trying to concatenate the list ID with another column. Something like this:
ReplyDelete$ExportItem | Add-Member -MemberType NoteProperty -name "EXTERNALID" -value $_["EXTERNAL"] $_["ID"]
any help would be much appreciated.
I keep getting the "cannot index into null array". The list I am trying to work with is populated. How can I fix this?
ReplyDeleteHi Salaudeen,
ReplyDeleteI ran your script on our server and got 400,000 items and my server told me that there was not enough memory avialable.
Is there a way to limit the records to 10,000 or so and than tell the script which items I want? So kinda like first run is 1 to 10,000, second run is 10,001 to 20,000 and so on?
Hi Borreltje,
DeleteUse the "Batch" method to export from larger lists!
Hi Salaudeen,
ReplyDeletePlease provide the complete code for the specific view in the list with all the fileds in that view.
drop me an email to : [email protected]
Here is how: PowerShell to Get All Items from SharePoint List View
DeleteHI Salaudeen,
ReplyDeleteThe code for all internal field is working fine for me
Could you help provide code for all internal fields with all versions metadata
https://www.sharepointdiary.com/2018/01/sharepoint-online-export-list-version-history-to-excel-using-powershell.html
Is working fine.. but few fields values are having comma .. so export data having problem
Could you help provide code for all internal fields with all versions metadata
Try Wrapping the field value with " (double quote)
DeleteHello,
ReplyDeletei would want to export all items with internal names and all versions the data to CSV file.
i ran your script which you posted " Export All fields in the list to Excel using PowerShell.
I need all internal fiels and all versions at time
To export version history, use this script: Export SharePoint List Version History to Excel using PowerShell
DeleteI'm trying to adapt your script to export items from a list on all subsites (webs) in a site collection using ForEach($Web in $Site.AllWebs), but am having trouble. The same list exists on all subsites and I need to dump out the data, either to a single CSV using -append, or multiple files. Can you help?
ReplyDeleteUse -Append switch in Export-CSV cmdlet. If you want to export to multiple files from subsites,use:
Delete$ListItemCollection | Export-CSV "c:\$($List.Title)_$($Web.Title).txt" -NoTypeInformation
Hi. Thanks for this script. I need to export only some columns. How i can do that? Thanks!
ReplyDeleteUse the first script in this article and map desired columns. E.g. If you want to add "Title" column to the CSV, use: $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]
DeleteHi ..How can i export files from all document library where column value is null in Sharepoint On-premise ?
ReplyDelete