Advanced Windows PowerShell Scripting Video Training

Advanced Windows PowerShell Scripting Video Training
Advanced Windows PowerShell Scripting Video Training

Tuesday, August 1, 2017

Adding Header Data to CSV Files

Don’t you just hate it when you get partial information?  With information systems, you would think that you would get everything you need.  It is like when I ask one of my Sailors why they are late and I have to ask a half dozen more questions before I get the full story. (Insert rolling eyes).

A neat thing that I do to demonstrate to my advance PowerShell classes is how to make a GUI using SAPIEN PowerShell Studio.  Yes, I know. PowerShell was not supposed to be ran in a GUI.  Try asking a non-technical user to execute something at the command line.  I would rather visit the dentist.  Placing a pretty GUI on top of the code fools them into wanting to use it.

For our stock application, We download information from Yahoo Finance.  Go ahead and execute the code below.

$Sym = "Msft"
$URL = "$Sym&f=snl1ghjkvw4P2"
Invoke-RestMethod -Uri $URL

"Msft","Microsoft Corporation",72.58,72.49,73.42,55.61,74.42,22184949,N/A,"-0.17%"

As you can see from the output, this is comma delimited information. You also may notice is that we are missing information.  Do you see what is missing? We are missing the header information that tells us the property names.  It is not part of the data received.  No problem.  We are free to create our own property names.  To do this, we are going to use ConvertFrom-CSV and use its parameter –Header.  Reading the help file for ConvertFrom-CSV, we see that we need to provide a comma separated list of property names.  The order of the names need to match the order that the data is received. Just remember that these names are the values of the -Header parameter.  Do not place the entire string in quotes. Provide a normal comma separated.

$Sym = "Msft"
$URL = "$Sym&f=snl1ghjkvw4P2"
$Data = Invoke-RestMethod -Uri $URL
$Data | ConvertFrom-csv -Header Symbol,Name,Ask,Low,High,Low52,High52,Volume,DayChange,ChangePercent

Symbol        : Msft
Name          : Microsoft Corporation
Ask           : 72.58
Low           : 72.49
High          : 73.42
Low52         : 55.61
High52        : 74.42
Volume        : 22185420
DayChange     : N/A

ChangePercent : -0.17%