How to check if all required columns exist in a CSV file

Question

My goal is to ask the user to pick a CSV file which my script can work on the data. In order to ensure the user has chosen the correct CSV file and the CSV file contains the correct column headers, I want to check this. My code returns to the user the column(s) which are missing. However, it does not seem to be able to check the very first item in the returned columns. For example, my CSV file contains the following headers:

[DBG]: PS C:\Users\jmilano\Documents\PowerShell_Scripts>> $ColumnHeaders
HardDisk_Filename
HardDisk_Name
HDCapacity
Host
Migration result
New Datastore Name
Old Datastore Name
PowerState
VM_Name

The function is called from the main script code using this method:

$CSVColumnHeaderCheck= CheckCSVColumnsExist -CSVImportFile $ImportCSVFile -ColumnsToCheck 'Old Datastore Name', 'New Datastore Name', 'VM_Name', 'HardDisk_Name', 'HardDisk_FileName', 'PowerState'

When I debug the code, I have the following:

Compare column name to check with the list of read in columns:

[DBG]: PS C:\Users\jmilano\Documents\PowerShell_Scripts>> $ColumnHeaders.Contains($ColumnToCheck)
False

[DBG]: PS C:\Users\jmilano\Documents\PowerShell_Scripts>> $ColumnToCheck
HardDisk_FileName

I tried to manually check the first item in $ColumnHeaders with the column I'm looking for:

[DBG]: PS C:\Users\jmilano\Documents\PowerShell_Scripts>> $ColumnHeaders[0] -eq $ColumnToCheck
True

That works!?

I changed the IF line to this and it seems to work!? Why does .Contains not work?

If ($ColumnHeaders -match $ColumnToCheck )

My script code:

Function CheckCSVColumnsExist
    {
    Param(
        [Object]$CSVImportFile,
        [Array]$ColumnsToCheck = ''     
        )

    $ColumnHeaders = (Import-Csv $CSVImportFile | Get-Member -MemberType NoteProperty).Name
    $MissingColumnHeaders = @()
    ForEach( $ColumnToCheck in $ColumnsToCheck)
        {
        $MissingColumnName = New-Object PSObject
        If ($ColumnHeaders.Contains($ColumnToCheck) ) 
            {
            # Nothing to do.
            } 
        Else 
            {
            $MissingColumnName | Add-Member -type NoteProperty -Name 'Column_Name' -Value $ColumnToCheck
            }
        $MissingColumnHeaders += $MissingColumnName
        }
    Return $MissingColumnHeaders
    }

Answer 1

It looks to me like you're always adding a PSObject to the $MissingColumnHeaders array even when the column isn't missing. When you add that non-missing column, the PSObject lacks the "Column_Name" property name and value.

Try it this way:

Function CheckCSVColumnsExist
    {
    Param(
        [Object]$CSVImportFile,
        [Array]$ColumnsToCheck = ''     
        )

    $c = (get-content $CSVImportFile | Select-Object -First 2) | ConvertFrom-CSV
    $ColumnHeaders = $c.psobject.properties.name
    foreach ($ctc in $ColumnsToCheck){
        if ($ColumnHeaders -notcontains $ctc){
            [array]$MissingColumnName += [PSCustomObject]@{Column_Name = $ctc}
        }
    }
    $MissingColumnName
}

CheckCSVColumnsExist c:\junk\dt.csv "Col1","Col2","Col3","Col4","Col5"