Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Sunday, September 16, 2018 8:37 AM
Post Code Name Contact Name Phone number
I want to export data from document library column into csv file using powershell.
In document library i had column name "Post Code" under column i had different post codes as "XXXXX-12-AD-2345, XXXXX-24-AD-2134, XXXXX-12-CD-1111, AAAAA-34-CR-1232, CCCCC-90-CX-11222, etc". I want data of "Post code" column and "post code" starting with XXXXX- only with all columns.
Means output on csv file as below
Post Code Name Contact Name Phone number
XXXXX-02-AD-1234 Jhonson [email protected] 12345678
XXXXX-24-AD-2134 Mike [email protected] 34534564
XXXXX-12-CD-1111 Peter [email protected] 11222233
I tried "Where-Object" for getting data of "Post code" column, but it generating empty file. Is there other condition (or) other powershell script using Loop condtion etc. Please share script. Thank you so much in Advance.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
Get the Web
$web = Get-SPWeb -identity "site url"
Get the Target List
$list = $web.Lists["Document Library"]
Array to Hold Result - PSObjects
$ListItemCollection = @()
#Get All List items
$list.Items | Where-Object {$_.'Post code' -like 'XXXXX-'} | foreach {
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -name "Post code" -value $_["Post code"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Contact name" -value $_["Contact Name"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Phone number" -value $_["Phone number"]
#Add the object with property to an Array
$ListItemCollection += $ExportItem } #Export the result Array to CSV file
$ListItemCollection | Export-CSV "Path location" -NoTypeInformation
Dispose the web Object
$web.Dispose()
write-Host "Exiting"
Murali
All replies (4)
Monday, September 17, 2018 11:35 AM ✅Answered
Hi Murali,
If you use the same powershell script, I cannot get the results. It generates empty file. Because using using ”Where-Object {$_.'Post code' -like 'XXXXX-'}”, it cannot get the value from the column.
So to get the value of the column, we should use the format $item["columnname"].
There is a demo for your reference. You should change to your site and list.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$web = Get-SPWeb -identity "http://sp:20971/"
$list = $web.Lists["list3"]
$items=$list.Items
$ListItemCollection = @()
foreach($item in $items)
{
if($item["Post code"] -like "XXXXX-*" )
{
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -name "Post code" -value $item["Post code"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $item["Name"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Contact name" -value $item["Contact Name"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Phone number" -value $item["Phone number"]
$ListItemCollection += $ExportItem
}
}
$ListItemCollection | Export-CSV "C:\test\f1\test3.csv" -NoTypeInformation
The result as below:
Best regards,
Sara Fan
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
Tuesday, September 18, 2018 1:24 AM ✅Answered
Hi Murali,
If you want to use where-object to filter list items in the list, we should use “Where-Object {$_["Post code"] -like "XXXXX-*" }”.
There is another demo for your reference. And change to your site and list.
$web = Get-SPWeb -identity "http://sp:20971/"
$list = $web.Lists["list3"]
$items=$list.Items
$ListItemCollection = @()
#Get All List items
$items| Where-Object {$_["Post code"] -like "XXXXX-*" } | foreach {
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -name "Post code" -value $_["Post code"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Contact name" -value $_["Contact Name"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Phone number" -value $_["Phone number"]
$ListItemCollection += $ExportItem }
$ListItemCollection | Export-CSV "C:\test\f1\test5.csv" -NoTypeInformation
$web.Dispose()
write-Host "Exiting"
Best regards,
Sara Fan
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
Thursday, October 4, 2018 9:55 AM ✅Answered
Hi Murali,
I am checking to see how things are going there on this issue. Please let us know if you would like further assistance.
If the issue was resolved, you can mark the helpful post as answer to help other community members find the helpful information quickly.
Best regards,
Sara Fan
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
Thursday, September 20, 2018 2:15 AM
Hi Murali,
If the reply is helpful to you, you could mark the reply as answer. Thanks for your understanding.
Best regards,
Sara Fan
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.