Skip to main content

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.

Comments

Post a Comment

Popular posts from this blog

How to Create and Use a SharePoint App Registration

SharePoint App Registrations are incredibly useful for unattended tasks to read or update SharePoint sites. It's similar to a user service account but more secure. They cannot be used as a user sign-in. They are used in custom applications or automated scripted processes. They are made even more secure with a certificate, in which case, they may only run from the machine that owns the certificate. SharePoint Things to Do with App Registrations Read data and write the data to another process Write CSV, text, or XML files Useful for data for to import into report software Writing the data to other APIs such as ERPs, Project software, or work authorization systems Changing the data Check the data based on a schedule. Update the data based on constraints Change permissions of items in a SharePoint list once an item is past due Work with partners and vendors to provide just-in-time, specific access to necessary data Simple Process Create the application either in SharePoint or in App Re...
Post a Comment
Read more

The Fix: npm ERR! code SELF_SIGNED_CERT_IN_CHAIN [SOLVED]

npm ERR! code SELF_SIGNED_CERT_IN_CHAIN npm ERR! errno SELF_SIGNED_CERT_IN_CHAIN npm ERR! request to https://registry.npmjs.org/gulp-cli failed, reason: self signed certificate in certificate chain What does it even mean? How does one fix this? There are so much information, it's hard to parse what I'm supposed to do. The Solution: Disable the Company VPN This is not ideal and you should work in cooperation with your IT security operations to let them know what you are doing and why you need it. It was reading this article:  https://stackoverflow.com/questions/54611707/request-to-https-registry-npmjs-org-co-failed  and the comments that finally clued me into our VPN causing the conflict. I previously did not have any issues but our company hired a new CISO who has been very active in hardening our corporate environment. What didn't work: gulp untrust-dev-cert Removing package-lock.json file Reinstall Node.js What I wasn't going to try (and you shouldn't either): npm...
Post a Comment
Read more

Pronouns Available on Microsoft Outlook and Teams Profile Cards

I'm really pleased with the feature in Microsoft 365 that lets you add pronouns to the profile card! Pronouns are used to replace someone's name in a sentence, such as "she", "he", or "they". Adding pronouns to your profile can help you express your identity and show respect for others. It can also improve communication and trust among your colleagues in a hybrid multicultural workplace. Never set pronouns on behalf of someone else. To turn on pronouns: You need to be able to access the settings in your organizational settings for security and privacy Settings - Microsoft 365 admin center Select Pronouns Settings - Microsoft 365 admin center Check "Turn on and allow pronouns" To add pronouns to your profile: Open your profile card in Teams or Outlook on the web. On your profile card, select + Pronouns or the pronouns listed below your name. To add or change your pronouns, select from the examples (only available in English), or enter your...
Post a Comment
Read more