How to get header value in CSV
In PowerShell, I want to pass the header name in a CSV file to another function in a PowerShell script.
How can I get the value-value of the header name in a variable in CSV?
eg. 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 get the text of the Country column value as the text "Country" in a variable in my script?
(Note: I am familiar with the $ _. Country notation to get a value like "Germany" from a string by importing a CSV into Powershell)
My particular problem is that I currently 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
}
Currently this 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-coded in the column name as "DEPTCATEGORY". This is the name of the column that will be viewed in the SharePoint list.
Instead of hardcoding the column name, I want to pass in the column name for the corresponding $ LookupTitle value and replace the hardcoded "DEPTCATEGORY".
I am calling the above function like this:
#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 CSV?
source to share
If you have an object in $ obj, you can list all the property headers like this:
$obj | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name'
It's an array, so you can refer to it individually like this:
($obj | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name')[0]
This will just give you the name of the first property, for example.
source to share
To get the column name from csv, first put the column heading names in 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, the index into the array by the position of the column (index starts at 0). Given your original example, this would be:
$myCountryColumnName = $csvColumnNames[2]
source to share
This is a general comment rather than an answer.
I needed to output the first column header name from CSV and I started by selecting NoteProperty fields from Get-Member. This does not work because the order of the NoteProperty column headings may not match the order of the column headings in the CSV file.
The futureSPQR method will work every time, because the text will not be 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]
source to share