CSV Break Down

I wrote a post recently, but a while back—it is kind of both now. It was about arrays and hash tables. I highlighted the differences between them and who knew, it turned out to be a success. There was a good amount of sharing of the post on Twitter and my same-day visitor count jumped up a fair amount above my average, which is typically around 175 visits per weekday. I do not know who these people are that do not use PowerShell on the weekends but get your priorities straight. Fine, maybe it is me.

The above image indicates I brought in nearly 400 visitors the same day it was published—not bad. There were people in the PowerShell subreddit using the words “array” and “hash table” incorrectly and whatever just took over in me and did what it does to help clarify things for people in the PowerShell community. I have said it before: I have no problem revisiting the things I have already learned about PowerShell. There is a good deal to know, and so any opportunity to learn something you have already learned—we will call that review, right—is okay. I review these concepts all the time; I can only know if I know it by checking! And usually, I have forgotten something. The whole, “If you don’t use it, you lose it” saying, applies to PowerShell, too.

There was another post I did more recently, and while it was not as popular, I liked it. It used a CSV file of links—think, URLs—to create a menu system that could open links as they were chosen inside PowerShell. Good enough for me to push my post count up by one. I will have been writing about PowerShell for eight years in June 2022. Just maybe I can get to 416 posts by then making my average one post per week for eight straight years. Even if I do not make it, to even be this close is a feat. I must like PowerShell.

As I was working with a CSV for that most recent post, I read another PowerShell Reddit post where someone was talking about arrays in conjunction with CSVs. I do not have a link for that post, and I am not sure how much it would apply, but it did get me wondering about the type of variable you end up with when you import a CSV. Do you know by heart!? Care to guess? Let’s bring back the CSV we used in the last post. It will work fine to get us the answers we need.

Title,Link,Note
PowerShell GitHub,https://github.com/PowerShell/PowerShell,
PowerShell GitHub Issues,https://github.com/PowerShell/PowerShell/issues,
PowerShell Docs GitHub,https://github.com/MicrosoftDocs/PowerShell-Docs,
Powershell Docs GitHub Issues,https://github.com/MicrosoftDocs/PowerShell-Docs/issues,
PowerShell Docs,https://docs.microsoft.com/en-us/powershell,
PowerShell Gallery,https://www.powershellgallery.com,
PowerShell Reddit,https://www.reddit.com/r/PowerShell,
Twitter Legends @jeffhicks,https://twitter.com/JeffHicks,
Twitter Legends @jsnover,https://twitter.com/jsnover,
Twitter Legends @concentrateddon, https://twitter.com/concentrateddon,
TechCrunch.com,https://techcrunch.com/,Tech News
Cnet.com,https://www.cnet.com/,Tech News
Gizmodo.com,https://gizmodo.com/,Tech News
9to5mac.com,https://9to5mac.com/,Tech News
Engadget.com,https://www.engadget.com/,Tech News
Wired.com,https://www.wired.com/,Tech News
TechRadar.com,https://www.techradar.com/,News
Axios.com,https://www.axios.com/,News

If we assume the above CSV file is stored in the below path, then the Import-CSV command will supply us with the below results. Further below in the example, we will import it a second time and store that in the $CSVFile variable. From there, we will take a closer look at it. What do we have once it is imported and stored in our variable?

[PS7.2.1][C:\] Import-Csv -Path 'C:\users\tommymaynard\Desktop\Links.csv'

Title                            Link                                                    Note
-----                            ----                                                    ----
PowerShell GitHub                https://github.com/PowerShell/PowerShell
PowerShell GitHub Issues         https://github.com/PowerShell/PowerShell/issues
PowerShell Docs GitHub           https://github.com/MicrosoftDocs/PowerShell-Docs
Powershell Docs GitHub Issues    https://github.com/MicrosoftDocs/PowerShell-Docs/issues
PowerShell Docs                  https://docs.microsoft.com/en-us/powershell
PowerShell Gallery               https://www.powershellgallery.com
PowerShell Reddit                https://www.reddit.com/r/PowerShell
Twitter Legends @jeffhicks       https://twitter.com/JeffHicks
Twitter Legends @jsnover         https://twitter.com/jsnover
Twitter Legends @concentrateddon https://twitter.com/concentrateddon
TechCrunch.com                   https://techcrunch.com/                                 Tech News
Cnet.com                         https://www.cnet.com/                                   Tech News
Gizmodo.com                      https://gizmodo.com/                                    Tech News
9to5mac.com                      https://9to5mac.com/                                    Tech News
Engadget.com                     https://www.engadget.com/                               Tech News
Wired.com                        https://www.wired.com/                                  Tech News
TechRadar.com                    https://www.techradar.com/                              News
Axios.com                        https://www.axios.com/                                  News

[PS7.2.1][C:\] $CSVFile = Import-Csv -Path 'C:\users\tommymaynard\Desktop\Links.csv'
[PS7.2.1][C:\] $CSVFile.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

It is an array! That makes perfect sense, right!? It is a list of values. Each row in the CSV file is a single value, in the array. Sure, each row has the potential of containing multiple values (Title, Link, Note), but each containing row is one single element in the array. The two commands below do the same thing. They return the first row, or element, in the CSV file, as it is stored in the $CSVFile variable.

[PS7.2.1][C:\] $CSVFile[0]

Title             Link                                     Note
-----             ----                                     ----
PowerShell GitHub https://github.com/PowerShell/PowerShell

[PS7.2.1][C:\] $CSVFile | Select-Object -First 1

Title             Link                                     Note
-----             ----                                     ----
PowerShell GitHub https://github.com/PowerShell/PowerShell

Just putting this out there, but we did not even have to use the .GetType() method earlier, to know this was an array. The minute we were able to use a numeric index ([0]), we should have known. I do not know about you, but this got me wondering about two things: One, where did the header row go? As we saw, it had nothing to do with index [0], and two, if each row is an element in an array, what is inside each element? How are the previous columns represented? Let’s answer these.

Before we do, though, a quick reminder: There is really no need to know the things we are discussing to use CSV files and the Import-CSV command. This happens whether we care to know it or not. It is just that I wanted to know what was happening and so I brought you with me. Back to the discussion.

As we have seen, the header row, as we may want to picture it, is gone. It found a place though. Several even. It is stored once per array element, or line or row, if you will, within the $CSVFile variable. What does that even mean!? That is answered by viewing the type of object returned when we view a single element in the array. The below examples use the .GetType() method to return the type of just index [0]. It is a PSCustomObject! We saw the output of this in the last two examples, however, we may have not put it together from just that. Let’s pipe index [0] to the Get-Member command and see what we recognize.

[PS7.2.1][C:\] $CSVFile[0].GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     False    PSCustomObject                           System.Object

[PS7.2.1][C:\] $CSVFile[0] | 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()
Link        NoteProperty string Link=https://github.com/PowerShell/PowerShell
Note        NoteProperty string Note=
Title       NoteProperty string Title=PowerShell GitHub

Each column of the CSV has become a NoteProperty in the PSCustomObject. Each name is a column header and each corresponding value has become a Definition. Each definition is a string that includes an entry such as <header>=<value>. Knowing that it makes sense that each of the following examples indicates that their type is a string.

[PS7.2.1][C:\] $CSVFile[0].Title
PowerShell GitHub
[PS7.2.1][C:\] $CSVFile[0].Title.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object

[PS7.2.1][C:\] $CSVFile[0].Link.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object

[PS7.2.1][C:\] $CSVFile[0].Note.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object

While we do not need to know these things to import a CSV file and use it as a part of an assignment or project, there is value in understanding what the command does with each row and the values in each row of a CSV file.

Leave a Reply

Your email address will not be published. Required fields are marked *