Powershell odd behaviour when outputting to csv

Question

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:

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?

I have no idea how your script is working to be honest. in your Get-ADObject command, you are trying to use a variable in single quotes. This won't evaluate the quote and will instead just use $memberof — Mark Harwood
– Mark Harwood, Commented Sep 18, 2019 at 16:22
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, 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 ''. — AdminOfThings
– AdminOfThings, Commented Sep 18, 2019 at 16:55

AdminOfThings · Accepted Answer · 2019-09-18 17:10:53Z

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

Thank you for this, the only thing I had to change was Get-ADUser back to Get-ADObject as ADUser doesn't report contacts, apart from that it's working now.

Collectives™ on Stack Overflow

Powershell odd behaviour when outputting to csv

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related