ImportExcelHeaders.ps1

# Set $Source to be the path to the xlsx to process
#requires -module ImportExcel

# cleans Excel column headers from having leading / trailing spaces and replaces other spaces with underscore '_'
# and then uses this set of headers to import data from the xlsx with headers that are easier to handle in code. 
# Essentially lines 8-14 are extra to a normal use of Import-Excel and the import line uses the header info and -StartRow to skip the file headers
# the target xlsx remains unchanged

$h = Import-Excel $source -StartRow 1 -EndRow 2
$headers = ($h | Get-Member -MemberType Properties | ForEach-Object {
        [PSCustomObject]@{
            Header = $_.name.trim().replace(' ', '_')
        }
    }
).header
  
$MyData = Import-Excel $source -headername $headers -StartRow 2