The following working as expected except the format. It appears the record of value $r and $s variable are display in the same column field. How i can make display in different column field? or when i open the csv document with Excel. Thank you

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) { 
        Add-PSSnapin "Microsoft.SharePoint.PowerShell" 

$timestamp = $(get-date -f MM-dd-yyyy_HH_mm_ss)
$Output=$timestamp + "_" + "SAPRoles.csv";

$wa = Get-SPWebApplication;
$site =  Get-SPSite $wa.Url;

$serviceContext = Get-SPServiceContext -Site $site

#Instantiate User Profile Manager
$userProfileConfigManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($serviceContext);

#Get All User Profiles
$profiles = $userProfileConfigManager.GetEnumerator();

#Loop through all user profiles and display account name and role
foreach($profile in $profiles)
 $s= $profile.get_Item("AccountName") 
 $r= $profile.get_Item("DataRoles") 
 $test= $s + $r | Out-File -Encoding Default -Append -FilePath $Output;

I think you can try something like this:

$profiles|Select-Object -Property @{Name="AccountName"; Expression = {$_.get_Item("AccountName")}},@{Name="DataRoles"; Expression = {$_.get_Item("DataRoles") -join ','}}| Export-Csv $Output -encoding "unicode"
| improve this answer | |
  • still displaying in one column – Supermode Oct 1 '14 at 4:55
  • Sorry, what do you mean by "displaying in one column" - displaying in one column in Excel? – Ruslan Dayanov Oct 1 '14 at 5:59
  • yes, that is correct. I want it display in different column when open with Excel.Sorry about that i should state on my question.thx – Supermode Oct 1 '14 at 7:10
  • So the actual problem is that delimiter symbol in Excel depends on your locale. The best way - import Text Data and set the delimiter in it's wizard. hesa.ac.uk/submit-import – Ruslan Dayanov Oct 1 '14 at 7:24
  • hi Ruslan, I make a minor change to your answer changing -encoding "unicode" to Default is doing what i want instead of import text data method. $profiles|Select-Object -Property @{Name="AccountName"; Expression = {$_.get_Item("AccountName")}},@{Name="DataRoles"; Expression = {$_.get_Item("DataRoles") -join ','}}| Export-Csv $Output -encoding Default. Thanks – Supermode Oct 1 '14 at 7:36

You need to output a seperator for your CSV (like ';'). It's easier if you just add your column values to an array (@() in PS) and then do something like this:

[String]::Join(";", @("columnValue1", "columnValue2"))


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.