{"id":3183,"date":"2013-07-22T00:01:00","date_gmt":"2013-07-22T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2013\/07\/22\/export-user-names-and-proxy-addresses-to-csv-file\/"},"modified":"2013-07-22T00:01:00","modified_gmt":"2013-07-22T00:01:00","slug":"export-user-names-and-proxy-addresses-to-csv-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/export-user-names-and-proxy-addresses-to-csv-file\/","title":{"rendered":"Export User Names and Proxy Addresses to CSV File"},"content":{"rendered":"

Summary<\/strong>: Microsoft Scripting Guy Ed Wilson talks about using Windows PowerShell to export user names and proxy addresses to a CSV file from Active Directory.<\/span><\/p>\n

\"Hey, Hey, Scripting Guy! I am trying to produce a report of our users in Active Directory and their associated proxy addresses. I want it in Excel, so I am using the Export-CSV<\/strong> cmdlet. The issue is that although I can get the user names just fine, the proxy addresses come back with:<\/p>\n

Microsoft.ActiveDirectory.Management.ADPropertyValueCollection<\/p>\n

This does not happen when I print to the screen, only when I export it to a CSV file. Help! This is quite annoying.<\/p>\n

—JF<\/p>\n

\"Hey, Hello JF,<\/p>\n

Microsoft Scripting Guy, Ed Wilson, is here. It is a new week down here in Charlotte, North Carolina in the United States. The weather has taken a turn for the worse, so instead of getting a daily deluge of rain, we now have hot and humid. Personally, I think I already miss the rain—I am not a huge fan of hot and humid. Luckily, I do not have to go out too often (one of the great things about working from home), so I can sit in front of a couple of fans, check my email, and get right to work.<\/p>\n

The issue with a multivalued attribute<\/h2>\n

The issue with a multivalued attribute, such as the ProxyAddresses<\/strong> attribute, is that it is an array. This means it contains multiple values that are associated with a single attribute.This makes sense for something like ProxyAddresses<\/strong> because there could be one or more proxy addresses defined for any particular user in Active Directory Domain Services (AD DS). For something like street address, there is only one value permitted for that attribute because it accepts a single value only.<\/p>\n

When I run the following commands, I can easily replicate the issue with the multivalued attribute. (This is a single, logical line command. I broke it at the pipe to display in the blog).<\/p>\n

Get-ADUser -Filter * -SearchBase ‘ou=testou,dc=iammred,dc=net’ -Properties proxyaddresses |<\/p>\n

select name, proxyaddresses | Export-CSV -Path c:\\fso\\proxyaddresses.csv –NoTypeInformation<\/p>\n

When I run the command, and open the CSV file in Microsoft Excel, I am greeted with the following output: <\/span><\/p>\n

\"Image<\/a><\/p>\n

Fixing the issue with multivalued attributes<\/h2>\n

Perhaps the easiest way to fix the issue with the multivalued ProxyAddresses<\/strong> attribute is to create a custom Select-Object<\/strong> property, then index directly into the array to pull out proxy address 1 and proxy address 2. To do this, I use a hash table to create a new property. The hash table requires two elements: the label and the expression. The label is a string, and the expression is a script block. In Windows PowerShell terms, this means that I can basically do anything I need to do inside the expression element.<\/p>\n

Get-ADUser -Filter * -SearchBase ‘ou=testou,dc=iammred,dc=net’ -Properties proxyaddresses |<\/p>\n

select name, @{L=’ProxyAddress_1′; E={$_.proxyaddresses[0]}},<\/p>\n

@{L=’ProxyAddress_2′;E={$_.ProxyAddresses[1]}} |<\/p>\n

Export-Csv -Path c:\\fso\\proxyaddresses.csv -NoTypeInformation<\/p>\n

I then open the CSV file in Microsoft Excel. The command is shown here:<\/p>\n

PS C:\\> Get-ADUser -Filter * -SearchBase ‘ou=testou,dc=iammred,dc=net’ -Properties pr<\/p>\n

oxyaddresses | select name, @{L=’ProxyAddress_1′; E={$_.proxyaddresses[0]}}, @{L=’Pro<\/p>\n

xyAddress_2′;E={$_.ProxyAddresses[1]}} | Export-Csv -Path c:\\fso\\proxyaddresses.csv –<\/p>\n

NoTypeInformation<\/p>\n

\"Image<\/a><\/p>\n

The Excel spreadsheet appears, and now I have two columns worth of proxy addresses as shown in the following image:<\/p>\n

\"Image<\/a><\/p>\n

JF, that is all there is to using Windows PowerShell to retrieve multivalued attributes and write them to a CSV file. Join me tomorrow when I will talk about more cool Windows PowerShell stuff.<\/p>\n

I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n

Ed Wilson, Microsoft Scripting Guy<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

Summary: Microsoft Scripting Guy Ed Wilson talks about using Windows PowerShell to export user names and proxy addresses to a CSV file from Active Directory.  Hey, Scripting Guy! I am trying to produce a report of our users in Active Directory and their associated proxy addresses. I want it in Excel, so I am using […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[7,169,3,4,20,45],"class_list":["post-3183","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-active-directory","tag-csv-and-other-delimited-files","tag-scripting-guy","tag-scripting-techniques","tag-user-accounts","tag-windows-powershell"],"acf":[],"blog_post_summary":"

Summary: Microsoft Scripting Guy Ed Wilson talks about using Windows PowerShell to export user names and proxy addresses to a CSV file from Active Directory.  Hey, Scripting Guy! I am trying to produce a report of our users in Active Directory and their associated proxy addresses. I want it in Excel, so I am using […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/3183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=3183"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/3183\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=3183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=3183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=3183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}