The answer to this has to do with how a CSV file is used with PowerShell.
In a CSV file, you are importing objects to be used with PowerShell. The first line list all of the property names of the objects. Each line after that is a unique object with the value of the property. Take a look below:
| Name | GivenName | SurName | City | Office |
| Jason Yoder | Jason | Yoder | Indianapolis | HQ |
| Matt Myers | Matt | Myers | Tampa | Regional |
In this example, we are creating 2 objects. Each object will have 5 properties:
- Name
- GivenName
- SurName
- City
- Office
CSV property values can only hold a singular value. Many properties that you come across will contain multiple properties. Take a look at this example:
PS C:\> Get-ADUser -Identity Administrator -Properties MemberOf
DistinguishedName : CN=Administrator,CN=Users,DC=PowerIT,DC=com
Enabled : True
GivenName :
MemberOf : {CN=Organization Management,OU=Microsoft Exchange Security
Groups,DC=PowerIT,DC=com, CN=Group Policy Creator
Owners,CN=Users,DC=PowerIT,DC=com, CN=Domain
Admins,CN=Users,DC=PowerIT,DC=com, CN=Enterprise
Admins,CN=Users,DC=PowerIT,DC=com...}
Name : Administrator
ObjectClass : user
ObjectGUID : 65b7e8a6-b040-4e6c-8dda-d408eca9f22c
SamAccountName : Administrator
SID : S-1-5-21-2015014380-1668822753-3530896179-500
Surname :
UserPrincipalName : Administrator@PowerIT.com
Take note of the MemberOf property. This user is a member of multiple groups. In other words, this property contains a collection (array). You can also see the { and } at the beginning and end of the property value. This tells you that this property contains (or could contain) multiple values. Because of this, it is not a good candidate for a CSV file.
Let’s export this to a CSV file and see the results.
Get-ADUser -Identity Administrator -Properties MemberOf | Export-csv -Path c:\temp\admin.csv
And now let’s read the CSV intoPowerShell
PS C:\> Import-Csv -Path C:\Temp\admin.csv
DistinguishedName : CN=Administrator,CN=Users,DC=PowerIT,DC=com
Enabled : True
GivenName :
MemberOf : Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
Name : Administrator
ObjectClass : user
ObjectGUID : 65b7e8a6-b040-4e6c-8dda-d408eca9f22c
SamAccountName : Administrator
SID : S-1-5-21-2015014380-1668822753-3530896179-500
Surname :
UserPrincipalName : Administrator@PowerIT.com
Take a look at the MemberOf property. You can see that instead of getting all the groups the user is a member of, we get Microsoft.ActiveDirectory.Management.ADPropertyValueCollection. To objects to disk that have properties that contain collections, you need to use XML.
PS C:\> Get-ADUser -Identity Administrator -Properties MemberOf | Export-Clixml -Path c:\temp\admin.xml
PS C:\> Import-Clixml -Path C:\Temp\admin.XML
DistinguishedName : CN=Administrator,CN=Users,DC=PowerIT,DC=com
Enabled : True
GivenName :
MemberOf : {CN=Organization Management,OU=Microsoft Exchange Security
Groups,DC=PowerIT,DC=com, CN=Group Policy Creator
Owners,CN=Users,DC=PowerIT,DC=com, CN=Domain
Admins,CN=Users,DC=PowerIT,DC=com, CN=Enterprise
Admins,CN=Users,DC=PowerIT,DC=com...}
Name : Administrator
ObjectClass : user
ObjectGUID : 65b7e8a6-b040-4e6c-8dda-d408eca9f22c
SamAccountName : Administrator
SID : S-1-5-21-2015014380-1668822753-3530896179-500
Surname :
UserPrincipalName : Administrator@PowerIT.com
I often get told that the user needs to consume the data in Excel. Well, you can’t. The problem is that a CSV file is not designed to do this. Unfortunately life is not always far. The default cmdlets that PowerShell comes with cannot help you. uses, you could create a custom object with a unqiue property for each potential value of MemberOf. The problem with that is that A CSV file header is created from the properties of the first object. You would have to read all objects and make sure that your first object has a place holder for the maximum expected number of values in MemberOf. Sound complex, well it is. The other option is to filter all all values of the object that do not contain singular data.
Comments