Create easy to use Excel (csv) from a PowerShell export

Problem

I get rusty in between coding projects, and I forget how to do what seems to be basic things like building and exporting a table to CSV for use in Excel or other downstream process/automation/data integration, etc.

PowerShell covered

• Variables
• simple process for collecting an array of information 
• Foreach loop
• @([pscustomobject]@{})
• +=
• Export-CSV

Logical Process

To build a file I can use in Excel, the steps involved are creating an empty variable which I will use as the array to store the table, 

1. Crate an empty variable to store data for later export to the CSV/Table
2. Process to retrieve raw data to process into the CSV/Table
1. for me this is usually Get-ADUser, Get-ADGroupMember, a SQL query, or a request from a SharePoint API for list data
2. for this demonstration we use Get-Process because everyone uses it for demos 😋
3. Foreach loop to process the raw data gleaning only the data I need for the table
4. += appending each looped occurrence into the CSV/Table
5. @([pscustomobject]@{}) - this is the thing I forget the most. This is how to create a line item in my new table with the header I want and putting the data in the right columns
6. Export-CSV -noTypeInformation is the final step to move the variable data to a file on the computer to open in Excel

Code

$data = $null

$processes = get-process

foreach ($eachProcess in $processes) {

    $data +=

    @(

        [pscustomobject]@{

            Name=$eachProcess.Name

            CPU=$eachProcess.CPU

            Id=$eachProcess.Id

            ThreadCount=$eachProcess.Threads.Count}

    )

}

$data | Export-Csv C:\users\public\Downloads\TableExample.csv -NoTypeInformation

Explanation

In the code, the initial variable is created/initialized and emptied (in case there was any data already).

Get-Process is loaded into a variable called processes. The $processes variable could be exported right away but the purpose of this code is to evaluate and transform the data to a custom output. Many of the reasons this is often performed are for data integrations, data transfers, and downstream processes which only require just-in-time data for other processes. 

The foreach loop, transforms each line in of the processes variable and collects only four items from each line. Using the $eachProcess variable collects each line and after the closing bracket of the foreach loop, it overwrites the $eachProcess variable with a new instance for the next $processes line. 

Using the '+=' symbols together tells PowerShell to add a line to the $data variable. 

For bullet 5 notice how the column heading precedes the equals signs and the object property of the variable $eachProcess is mapped to the heading. (If you want to know all the object properties of your processes variable you can use the Get-Member cmdlt - $processes | Get-Member. It can be very helpful to figure out what you are looking for.) 

Finally, the $data can be exported to the location you desire. I am using this location as an example. Chances are the data needs to be exported to a server or emailed to a client.