Retrieve list of columns in Excel table on a specific worksheet with ImportExcel Powershell module

Question

I have a specific brief: retrieve the column names of an Excel table that is on a given worksheet, using the ImportExcel Powershell module.

Is this possible or am I just better off reading the workbook with an OLE-DB connection or something similar?

I have tried to utilize ImportExcel's GetExcelTable command but Powershell doesn't seem to recognize it - this was my vain attempt to get a reference of the table and somehow use it to get to the column names.

Here's the code snippet:

$table = GetExcelTable -FilePath $xlsxConfigFilePath -SheetName "SYS_SourceSystem"

And here is the error I get when I try to run it ($xlsxConfigFilePath is set as a valid path to the Excel workbook):

GetExcelTable : The term 'GetExcelTable' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:9 char:10 + $table = GetExcelTable -FilePath $xlsxConfigFilePath -SheetName "SYS_ ... + ~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (GetExcelTable:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException

Any help would be appreciated!

Answer 1

Based on @BenH's suggestion I figured out a solution that uses the ImportExcel module without the need of an OLE-DB connection.

First, we get a reference of the table (the worksheet only contains one table):

$table = Import-Excel -Path $xlsxConfigFilePath -WorksheetName "SYS_SourceSystem"

Since the $table variable we get from this is a PSCustomObject, we can parse it with the Get-Member cmdlet:

$table | Get-Member -MemberType NoteProperty | %{
"$($_.Name)"
}

We use the NoteProperty to refer to the part of the object that holds the column names.

Here is the output with the column names:

ID
SRCS_Code
SRCS_Name
SRS_Platform
SSIS_ConnectionName

Thanks everyone for the help!