Formatting the output using powershell

Question

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;
}

Ruslan Dayanov · Accepted Answer · 2014-09-30 08:17:32Z

1

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"

answered Sep 30, 2014 at 8:17

Ruslan Dayanov

1,64311 silver badges17 bronze badges

still displaying in one column

Supermode
– Supermode

2014-10-01 04:55:44 +00:00
Commented Oct 1, 2014 at 4:55
Sorry, what do you mean by "displaying in one column" - displaying in one column in Excel?

Ruslan Dayanov
– Ruslan Dayanov

2014-10-01 05:59:25 +00:00
Commented Oct 1, 2014 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
– Supermode

2014-10-01 07:10:45 +00:00
Commented Oct 1, 2014 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
– Ruslan Dayanov

2014-10-01 07:24:56 +00:00
Commented Oct 1, 2014 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
– Supermode

2014-10-01 07:36:11 +00:00
Commented Oct 1, 2014 at 7:36

Add a comment |

Cameron Verhelst · Accepted Answer · 2014-09-30 08:05:16Z

0

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"))

output:

columnValue1;columnValue2

answered Sep 30, 2014 at 8:05

Cameron Verhelst

1,4049 silver badges11 bronze badges

Add a comment |

Stack Exchange Network

Formatting the output using powershell

2 Answers 2

Your Answer

Hot Network Questions

Formatting the output using powershell

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions