How To Get The Value Of Header In CSV

Question

In PowerShell I want to pass the name of a header in a CSV file into another function in the PowerShell script.

How can I retrieve the value-text of a header name into a variable in CSV?

e.g. if I have the following CSV data:

ID Name     Country
-- ----     -------
1  John     United States
2  Beatrice Germany
3  Jouni    Finland
4  Marcel   France

In the above example how can I retrieve the Country column value text as "Country" text into a variable in my script?

(Note: I am familiar with the notation $_.Country to retrieve the value of, for example, "Germany" from a row by importing the CSV in Powershell)

My specific issue is that currently I have the following function in my script:

function GetItemIdFromTitle([string]$LookupTitle, [ref]$LookupId)
{   
    $LookupField = $LookupList.Fields["DEPTCATEGORY"]   
    $LookupItem = $LookupList.Items | where {$_['DEPTCATEGORY'] -like "*$LookupTitle*"} 
    $LookupId.Value = $LookupItem.ID
}

This currently takes a string value -> $LookupTitle and uses that to find an item in a SharePoint list. As you can see in the script I am hard-coding in the column name as "DEPTCATEGORY". This is the column name that will be looked up to in the SharePoint list.

Instead of hard-coding the column name I want to pass in the name of the column for the corresponding $LookupTitle value and replace the hard-coded "DEPTCATEGORY".

I am calling the above function as follows:

#GET THE LOOKUP COLUMN ID       
GetItemIdFromTitle $_.DEPTCAT ([ref]$LookupIdValue)

( $_.DEPTCAT is the value from the row in the CSV column. )

Can I do something like

$myCountryColumnName = $_.Country.Property.Title 

or

$myCategoryColumnName = $_.DEPTCAT.Property.Name

to get the column name from the CSV?

Answer 1

If you have an object in $obj, you could list all the property headers like this:

$obj | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name'

This is an array, so you can reference them individually like this:

($obj | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name')[0]

This would just give you the name of the first property for instance.

Answer 2

Assuming that you have already read in the CSV using Import-CSV you can examine the property of the resulting object. Similar to this answer but maintains column order in the resulting array

Examining the first row / element

$data = import-csv $path
$data[0].psobject.properties.name

So the second line will return a string array of the properties.

Answer 3

To get the column name from the csv, first, put the names of the column headers into an array (this will also allow you to loop through each column, if needed) ...

$inFilePath = "C:\path\to\file.csv"
$csvColumnNames = (Get-Content $inFilePath | Select-Object -First 1).Split(",")

... , secondly, index into the array by column position (index starts at 0). Given your original example it would be;

$myCountryColumnName = $csvColumnNames[2]

Answer 4

This is more of a general comment than an answer.

I needed to pull the first column header name from CSVs and I started with selecting the NoteProperty fields from Get-Member. This doesn't work because the order of the NoteProperty column header names might not match the order of the column headers in the CSV file.

futureSPQR's method will work every time because the text won't get reordered on you. Below is my one-liner version of his method to get the first column header name.

((Get-Content filename.csv)[0] -split(','))[0]

Answer 5

$f = Import-Csv "...csv"

Get-Member -InputObject $f[0] |
  Where-Object {$_.MemberType -eq "NoteProperty"} |
  select-object Name

Answer 6

If you're looking to check if a header name exists in the array, use below code

$Array | Get-member -MemberType 'NoteProperty'| where {$_.Name -eq "ColumnName"}