How to Dynamically Export to CSV Files with PowerShell

Jul 28, 2016 by Greg Turner

Have you ever wanted to export data to a CSV file from within PowerShell only to find that the results are incompatible with the CSV format and on top of that, the ConvertTo-Csv command doesn’t work? You would think that exporting email proxy addresses in Exchange would be simple, but it isn’t unless you can convert the EmailAddresses property, a.k.a the “proxyAddresses” attribute, before exporting to CSV. Doing this, however, requires a script or the use of a one-line command that you may not be able to remember later.

It seems like the following command would work, but it doesn’t: “Get-Mailbox user1 | select primarysmtpaddress,emailaddresses | export-csv addresses.csv”

When you look at the results, you see “Microsoft.Exchange.Data.ProxyAddressCollection” instead. What is this?!

This is what the CSV file looks like:

Similarly, for the command, “Get-ADUser -Filter * -Properties memberof | export-csv”, the CSV looks like:

When I sat down to write a script to export just about everything involving Active Directory and Exchange to CSV files, I knew I had to come up with a better method than creating thousands of lines of code for PowerShell to accomplish this. Recently, I started to create CSV files by creating a new PowerShell Object to create a file of only strings. Converting the variable to a string into a new PsObject works around this very well. So well, in fact, that the few items that don’t come over well have never been missed, such as “nTSecurityDescriptor” having a value of “System.DirectoryServices.ActiveDirectorySecurity”. If I used this method to document Active Directory and Exchange, the PS1 file would have thousands of lines. To get the results from just one command, “Get ADUser -Properties *” using my New-Object method, would have taken more than 140 lines.

I remembered once reading that someone created CSVs dynamically, so I started working to figure it out. Knowing that the “Property” from PowerShell’s results was the only way, I focused on the Get-Member command. I then realized all I had to do was to loop it through each member property using the New-Object way of creating CSV. At this point, all that had to be done was to have a script to write the code for me. Once I completed it, it only came to around 20 lines of PowerShell (without the comments), and a function to do similar with just 18 lines.

By commenting out lines in this script or uncommenting them, the MakeCSV.ps1 script can do several things:

• Display the code it creates
• Display what the “select” method to export to a CSV will look like (if that is your preference)
• Create the CSV for you
• Copy the code to the clipboard so it can be pasted into another script
• Append the generated script to a TXT file
• Have the script run the code it just dynamically created. (Say what?! When I realized I could do this very easily, it just blew me away!)

After running the following a couple times while appending to the CSV, one file will contain all the data available from within PowerShell:

.\MakeCSV.ps1 “Resolve-DnsName -Name www.mirazon.com -type MX -Server 8.8.8.8”

.\MakeCSV.ps1 “Resolve-DnsName -Name loumug.org  -type MX -Server 8.8.8.8”

“MakeCSV.ps1” will create the code needed to export the CSV by using a PsObject. After a few modifications, I created the MakeCSV function that can run the code inside your own script.

This worked great, but I still didn’t like having a script with thousands of lines, so I also created a function where all that has to be done is to type something like, “MakeCSV Get-Mailbox”. The function is basically the same as the script, but only displays the command sent to it and will not save or use the clipboard like the standalone script does.

Just like the standalone script, if you have a quote in the command, have it inside a single-quote (‘ “ ‘ ).

One big difference with using the function is the command is always inside of quotes. For example, MakeCSV “Get-Mailbox”

I do recommend verifying the function works. I’ve had it not work a few times. When it doesn’t, I just copy/paste the code into the script that’s created by MakeCSV.ps1 and it works fine unless the properties change (depending on how you use the command). “Resolve-DnsName” is one command where the properties are different depending if you query for an A record and a MX record. In this case, I just hard coded the properties within a PSObject so I can query every DNS record type and maintain a single CSV file.

If you want the full writeup with the scripts, fill out the form below.

Oops! We could not locate your form.