Powershell - Search an array for presence of value from a hash table

Question

I am loading the header row from an Excel spreadsheet to an array and need to compare it to a hash table that has multiple key entries per value. I need to be able to have different variations for each column I am searching for. The array contains the header row (person name, Customer_Account_Number, Electric Company, company name, Service_Address_1, Service_City, Service_State, Service_Zip, Phone Number)

I need to set my variables like this:

$nameColumn = 1 $companyColumn = 4

$headers = @()
for ($col = 1; $col -le $worksheet.UsedRange.Columns.Count; $col++) {
    $headers += $($worksheet.Cells.Item(1, $col).Text)
}

$headerMappings = @{
     "name"               = "Name";
     "person name"        = "Name";
     "contact Name"       = "Name"
     "company name"       = "Company"
     "service_address_1"  = "Address"
     "address"            = "Address"
     "service_city"       = "City"
     "city"               = "City"
     "service_state"      = "State"
     "state"              = "State"
     "service_zip"        = "Zip"
     "zip"                = "Zip"
     "phone number"       = "Phone"
     "phone"              = "Phone"
}

# Get column indices for specific field names using headerMappings

$nameColumn    = $headers.IndexOf($headerMappings["Name"])
$companyColumn = $headers.IndexOf($headerMappings["Company"])
$addressColumn = $headers.IndexOf($headerMappings["Address"])
$cityColumn    = $headers.IndexOf($headerMappings["City"])
$stateColumn   = $headers.IndexOf($headerMappings["State"])
$zipColumn     = $headers.IndexOf($headerMappings["Zip"])
$phoneColumn   = $headers.IndexOf($headerMappings["Phone"])

Answer 1

$headers is an array, $headerMappings is a hashtable.

I suggest a two-step approach:

• Create an array of mapped header names, using $headerMappings for translation; e.g., translate service_address_1 to Address.

• Then base your lookups with .IndexOf() on the array of mapped header names.

# Sample input array.
$headers =  'person name', 'service_state', 'phone', 'service_address_1'

$headerMappings = @{
     "name"               = "Name"
     "person name"        = "Name"
     "contact Name"       = "Name"
     "company name"       = "Company"
     "service_address_1"  = "Address"
     "address"            = "Address"
     "service_city"       = "City"
     "city"               = "City"
     "service_state"      = "State"
     "state"              = "State"
     "service_zip"        = "Zip"
     "zip"                = "Zip"
     "phone number"       = "Phone"
     "phone"              = "Phone"
}

# Translate the headers to their mapped names.
# Note: This assumes that all headers names are covered by the hashtable.
$mappedHeaders = @($headers | ForEach-Object { $headerMappings[$_] })

# Get column indices based on the array of *mapped* header names.
# Note: .IndexOf() is *case-sensitive*.
# ...
$addressColumn = $mappedHeaders.IndexOf('Address') # -> 3

Note that if a given header array contains multiple headers that map to the same name in the hashtable, the above will only report the index of the first such header.

---

As for what you tried:

$headerMappings["Name"]

"Name" is hashtable entry value, not a key; it is used in several entries, namely those with key "name", "person_name", and "contact_Name".

Therefore, the expression predictably evaluates to $null, and the .IndexOf() call predictably returns -1 (to indicate that the input object isn't part of the array).

If you wanted to do this without a intermediate helper array (as shown above), you'd have to do the following, which is inefficient, however, as it involves searching the entire array for every index:

$headers.IndexOf(
  $headers.Where({ $headerMappings[$_] -eq 'Address' }, 'First')[0]
)