Skip to content

Instantly share code, notes, and snippets.

@fatherjack
Created May 31, 2022 07:36
Show Gist options
  • Save fatherjack/24a1ab4ec78c9b8e095a2a5d4581e854 to your computer and use it in GitHub Desktop.
Save fatherjack/24a1ab4ec78c9b8e095a2a5d4581e854 to your computer and use it in GitHub Desktop.
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment