CODEWITHSUNDEEP
excelpowershellexport-to-csv
Bobybobp
Bobybobp

Reputation: 95

Powershell odd behaviour when outputting to csv

I'm having a problem when outputting my foreach loop to a csv file.

My Groups are set like this:

$Groups = "Group1", "Group2", "Group3"

My code is:

$results = ForEach ($Group in $Groups) {
        $memberof = get-adgroup $Group | select -expandproperty distinguishedname
        Write-Output $Group
        Get-ADObject -Filter 'memberof -eq $memberof -and (ObjectClass -eq "user" -or ObjectClass -eq "contact")' -properties * | select name, Objectclass, mail
        Write-Output ""
        Write-Output ""
    }

$results | Export-csv  Contacts.csv -NoTypeInformation

The problem seems to be coming from the Write-Output lines but I have no clue why. When I run my code without writing to a csv file, I get the expected result, something like:

NameOfGroup1
name                        Objectclass mail
----                        ----------- ----
User1                       user        [email protected]
User2                       user        [email protected]
                                                            #Spaces caused by write-output ""

NameOfGroup2
User1                       user        [email protected]
Contact1                    contact     [email protected]

Then again when I run my code to write to csv file and have the write-output $Group commented out I get a similar result.

But if I run my full code from the top of this page including the write-output $Group, it comes out like this:

enter image description here

I've figured out what these results represent but I haven't got clue why they do print out like this.

Eseentially the numbers refer to the length of the group name, so the first 17 would be a 17 character group name, and then the number of lines below is equal to the number of contacts and users that are inside that group. The 2 zeros at the end of each group are the length of the write-output "" lines.

What is causing this behavior?

Upvotes: 0

Views: 228

Answers (1)

AdminOfThings
AdminOfThings

Reputation: 25001

The following code will closely output what you are attempting.

$results = ForEach ($Group in $Groups) {
        $memberof = get-adgroup $Group | select -expandproperty distinguishedname
        Get-ADUser -Filter "memberof -eq '$memberof' -and (ObjectClass -eq 'user' -or ObjectClass -eq 'contact')" -properties name,ObjectClass,Mail | Select-Object @{n='Group';e={$Group}},name, Objectclass, mail
    [pscustomobject]"" | Select-Object Group,Name,ObjectClass,Mail
    [pscustomobject]"" | Select-Object Group,Name,ObjectClass,Mail
    }

$results | Export-csv  Contacts.csv -NoTypeInformation

Explanation:

Export-Csv converts an object or array of objects with properties into a CSV file. You can see the same result in the console with ConvertTo-Csv. Properties are converted into columns and property values are placed under their associated columns. When you output a string as in Write-Output $Group, it has a property of Length. To fix this, you need to add $Group as a calculated property in your Select-Object. If you want to do blank lines in your CSV, then you should output another object with all of the property values as ''.

When you mix objects in your PowerShell outputs, you can see unexpected results. Your Get-ADObject outputs a custom object. Your Write-Output lines output a string. Those two object types do not share properties. So you only see the properties for the first object in your array, which is a string. If you put all of the Write-Output statements at the end of your loop, you will see more properties in your CSV. See below for an example that just by reversing the order of processed objects, you get a different result.

$str = "string"
$obj = [pscustomobject]@{property1 = "value1"; property2 = "value2"}

$str,$obj | convertto-csv -notype
"Length"
"6"

$obj,$str | convertto-csv -notype
"property1","property2"
"value1","value2"
,

Notice the properties available to the custom object $obj and the string $str.

$obj | get-member -Type Properties


   TypeName: System.Management.Automation.PSCustomObject

Name      MemberType   Definition
----      ----------   ----------
property1 NoteProperty string property1=value1
property2 NoteProperty string property2=value2

$str | get-member -Type Properties


   TypeName: System.String

Name   MemberType Definition
----   ---------- ----------
Length Property   int Length {get;}

Upvotes: 2

Related Questions