Often when I'm writing scripts in PowerShell, I'm pulling data that I ultimately want to export to csv. From there, Excel's filtering tools are awesome, and I can manipulate till my heart's content, or the boss is happy!

PowerShell's native export to csv command is excellent.

In its most basic form it could look like this:

PS> Get-ChildItem | Export-Csv "Listing.csv"
PS> Get-Content "Listing.csv"
"PSPath","PSParentPath","PSChildName","PSDrive","PSProvider","PSIsContainer","Mode","BaseName","Target","LinkType","Name","FullName","Parent","Exists","Root","Extension","CreationTime","CreationTimeUtc","LastAccessTime","LastAccessTimeUtc","LastWriteTime","LastWriteTimeU
tc","Attributes"
"Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Docs","Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath","My Docs","C","Microsoft.PowerShell.Core\FileSystem","True","d---
--","My Docs","System.Collections.Generic.List`1[System.String]",,"My Docs","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Docs","TestFolderPath","True","C:\","","1/07/2017 6:26:10 PM","1/07/2017 8:26:10 AM","1/07/2017 6:26:10 PM","1/07/2017 8:26:10 AM","
1/07/2017 6:26:10 PM","1/07/2017 8:26:10 AM","Directory"
"Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Music","Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath","My Music","C","Microsoft.PowerShell.Core\FileSystem","True","d-
----","My Music","System.Collections.Generic.List`1[System.String]",,"My Music","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Music","TestFolderPath","True","C:\","","1/07/2017 6:26:15 PM","1/07/2017 8:26:15 AM","1/07/2017 6:26:15 PM","1/07/2017 8:26:15
AM","1/07/2017 6:26:15 PM","1/07/2017 8:26:15 AM","Directory"
"Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Photos","Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath","My Photos","C","Microsoft.PowerShell.Core\FileSystem","True","
d-----","My Photos","System.Collections.Generic.List`1[System.String]",,"My Photos","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Photos","TestFolderPath","True","C:\","","1/07/2017 6:26:20 PM","1/07/2017 8:26:20 AM","1/07/2017 6:26:20 PM","1/07/2017 8:2
6:20 AM","1/07/2017 6:26:20 PM","1/07/2017 8:26:20 AM","Directory"
"Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Sharona","Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath","My Sharona","C","Microsoft.PowerShell.Core\FileSystem","True"
,"d-----","My Sharona","System.Collections.Generic.List`1[System.String]",,"My Sharona","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Sharona","TestFolderPath","True","C:\","","1/07/2017 6:26:47 PM","1/07/2017 8:26:47 AM","1/07/2017 6:26:47 PM","1/07/201
7 8:26:47 AM","1/07/2017 6:26:47 PM","1/07/2017 8:26:47 AM","Directory"
"Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Videos","Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath","My Videos","C","Microsoft.PowerShell.Core\FileSystem","True","
d-----","My Videos","System.Collections.Generic.List`1[System.String]",,"My Videos","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Videos","TestFolderPath","True","C:\","","1/07/2017 6:26:26 PM","1/07/2017 8:26:26 AM","1/07/2017 6:26:26 PM","1/07/2017 8:2
6:26 AM","1/07/2017 6:26:26 PM","1/07/2017 8:26:26 AM","Directory"
"Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\cities.txt","Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath","cities.txt","C","Microsoft.PowerShell.Core\FileSystem","False
","-a----","cities","System.Collections.Generic.List`1[System.String]",,"cities.txt","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\cities.txt",,"True",,".txt","3/07/2017 8:40:26 PM","3/07/2017 10:40:26 AM","3/07/2017 8:40:26 PM","3/07/2017 10:40:26 AM","3/0
7/2017 8:40:26 PM","3/07/2017 10:40:26 AM","Archive"
"Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\Listing.csv","Microsoft.PowerShell.Core\FileSystem::C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath","Listing.csv","C","Microsoft.PowerShell.Core\FileSystem","Fal
se","-a----","Listing","System.Collections.Generic.List`1[System.String]",,"Listing.csv","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\Listing.csv",,"True",,".csv","1/07/2017 6:29:55 PM","1/07/2017 8:29:55 AM","1/07/2017 6:29:55 PM","1/07/2017 8:29:55 AM","
12/07/2017 9:00:00 PM","12/07/2017 11:00:00 AM","Archive"
PS>

Of course there is a lot information there we are not really interested in. Export-Csv is great mates with Select-Object (a default alias for Select-Object is simply Select):

PS> Get-ChildItem | Export-Csv "Listing.csv" -NoTypeInformation
PS> Get-Content .\Listing.csv
TYPE System.IO.DirectoryInfo
"Name","FullName","CreationTime","LastAccessTime"
"My Docs","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Docs","1/07/2017 6:26:10 PM","1/07/2017 6:26:10 PM"
"My Music","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Music","1/07/2017 6:26:15 PM","1/07/2017 6:26:15 PM"
"My Photos","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Photos","1/07/2017 6:26:20 PM","1/07/2017 6:26:20 PM"
"My Sharona","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Sharona","1/07/2017 6:26:47 PM","1/07/2017 6:26:47 PM"
"My Videos","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Videos","1/07/2017 6:26:26 PM","1/07/2017 6:26:26 PM"
"cities.txt","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\cities.txt","3/07/2017 8:40:26 PM","3/07/2017 8:40:26 PM"
"Listing.csv","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\Listing.csv","1/07/2017 6:29:55 PM","1/07/2017 6:29:55 PM"
PS>

While there are a bunch of parameters available for Export-Csv, the one I use the most is -NoTypeInformation. This ensures the output doesn't contain the object type information:

PS> Get-ChildItem | Select-Object Name,FullName,CreationTime,LastAccessTime | Export-Csv "Listing.csv" -NoTypeInformation
PS> Get-Content "Listing.csv"
"Name","FullName","CreationTime","LastAccessTime"
"My Docs","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Docs","1/07/2017 6:26:10 PM","1/07/2017 6:26:10 PM"
"My Music","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Music","1/07/2017 6:26:15 PM","1/07/2017 6:26:15 PM"
"My Photos","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Photos","1/07/2017 6:26:20 PM","1/07/2017 6:26:20 PM"
"My Sharona","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Sharona","1/07/2017 6:26:47 PM","1/07/2017 6:26:47 PM"
"My Videos","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\My Videos","1/07/2017 6:26:26 PM","1/07/2017 6:26:26 PM"
"cities.txt","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\cities.txt","3/07/2017 8:40:26 PM","3/07/2017 8:40:26 PM"
"Listing.csv","C:\Users\Damon\OneDrive\Scripts\In Progress\TestFolderPath\Listing.csv","1/07/2017 6:29:55 PM","1/07/2017 6:29:55 PM"

Much better. I've used those basic commands a bazillion time to get data from AD, usually Users and Computers, and it really lends itself to quick and easy one-liners. That's all well and good when dealing with native objects, but what about when we are collating data and need to define our own objects?

Custom objects of course. Firstly, we need to define a "blank" object:

PS> $myObject = New-Object PSObject

Simple enough, although as you can see, at the moment it isn't particularly useful:

PS> $myObject | Get-Member

        TypeName: System.Management.Automation.PSCustomObject

    Name        MemberType Definition
    ----        ---------- ----------
    Equals      Method     bool Equals(System.Object obj)
    GetHashCode Method     int GetHashCode()
    GetType     Method     type GetType()
    ToString    Method     string ToString()

PS>

We need to add some properties to make it useful. There is a couple of way to do this. I usually use the pipeline like this:

PS> $myobject | Add-Member -Type NoteProperty -Name "City" -Value "Brisbane"
PS> $myobject | Add-Member -Type NoteProperty -Name "Population" -Value "2.2 million"
PS> $myobject | Add-Member -Type NoteProperty -Name "Climate" -Value "Awesome!"

Now we have some useful properties in our object:

PS> $myObject | Get-Member

       TypeName: System.Management.Automation.PSCustomObject

    Name        MemberType   Definition
    ----        ----------   ----------
    Equals      Method       bool Equals(System.Object obj)
    GetHashCode Method       int GetHashCode()
    GetType     Method       type GetType()
    ToString    Method       string ToString()
    City        NoteProperty string City=Brisbane
    Climate     NoteProperty string Climate=Awesome
    Population  NoteProperty string Population=2.2 million

PS>  $myObject

    City     Population  Climate
    ----     ----------  -------
    Brisbane 2.2 million Awesome

PS>

It's also worth mentioning that you can use the Add-Member command on it's own:

PS> Add-Member -InputObject $myObject -MemberType NoteProperty -Name "URL" -Value "https://www.brisbane.qld.gov.au/"
PS> $myObject

        City     Population  Climate URL
        ----     ----------  ------- ---
        Brisbane 2.2 million Awesome https://www.brisbane.qld.gov.au/

PS>

Now we have a custom object. Used in a loop, each iteration of the object can be saved to an array, and then you quickly build up a dataset worthy of exporting to CSV:

PS> $results

    City      Population  Climate  
    ----      ----------  -------  
    Brisbane  2.2 million Awesome  
    Sydney    5 million   Meh
    Melbourne 4.6 million Variable
    Darwin    5 million   Soupy
    Adelaide  5 million   Dry Heat
    Perth     5 million   No Idea

    PS> $results | Export-Csv cities.txt -NoTypeInformation
    PS> Get-Content .\cities.txt
    "City","Population","Climate"
    "Brisbane","2.2 million","Awesome"
    "Sydney","5 million","Meh"
    "Melbourne","4.6 million","Variable"
    "Darwin","5 million","Soupy"
    "Adelaide","5 million","Dry Heat"
    "Perth","5 million","No Idea"
PS>

For brevity I've omitted the code for the loop and array. I'll cover those in another post in the future. Also, the MemberType I'm using, NoteProperty, is a Name/Value pair, however there are others available. For a full list, have a look at Microsoft's MSDN page here.

There we have it, exporting native and custom objects to csv files (and my first "real" post).

Thanks for reading!


Note. While I've been using this method for ages, I originally put it together after numerous google searches and trial and error. This will be the case for bunch of techniques that may show up on my blog. If I still have the reference site, I'll mention it. Also, I've no doubt there are other ways to do accomplish the same task. Would love to hear about those in the comments section. And a big thank you to those websites long forgotten that provided some code and inspiration.