How to export a specific Excel column with PowerShell?

Question

I have an excel with multiples columns and I would like to export some specific columns to a .xlsx file but it export the first 3 columns of the excel and not the columns with the specific headers: 'Host','CPU usage %',"Memory usage %"

$SourceFileDirectory = "C:\TEMP\"
$CurrentDate = Get-Date -Format "yyyyMMdd"
$TestFile = "Test2"
$ExcelExt = ".xlsx"

$ExcelFiles = Get-ChildItem $SourceFileDirectory -Filter vHost.xlsx
$headers = 'Host','CPU usage %',"Memory usage %"

foreach ($file in $ExcelFiles)
{
    $ImportFile = -JOIN($SourceFileDirectory,$file)
    $DestinationFile = -JOIN($SourceFileDirectory,$TestFile,"_",$CurrentDate,$ExcelExt)

    $sheetName = 'vHost.xlsx' # => Define the WorkSheet Name here

    Write-Host $ImportFile
    Write-Host $DestinationFile

    $xlsx = Import-Excel -Path $ImportFile -HeaderName $headers -StartRow 1 |
    Select-Object * -ExcludeProperty Dupe* |
    Export-Excel -Path $DestinationFile -PassThru -WorksheetName $sheetName
    
    $ws = $xlsx.Workbook.Worksheets[$sheetName]
    Set-ExcelRange -HorizontalAlignment Center -Worksheet $ws -Range $ws.Dimension.Address
    Close-ExcelPackage $xlsx
}

Santiago Squarzon · Accepted Answer · 2022-02-01 00:52:34Z

4

If you know the specific column numbers you can use the -ImportColumns parameter in combination with -HeaderName and the -DataOnly switch if you also want to have new column names.

Minimal example:

$tmp = 'tempfile.xlsx'

@'
col1,col2,col3,col4
1,2,3,4
'@ | ConvertFrom-Csv | Export-Excel $tmp

Import-Excel $tmp -ImportColumns 2, 4 -HeaderName NewCol2, NewCol4 -DataOnly

Which yields:

NewCol2 NewCol4
------- -------
      2       4

answered Feb 1, 2022 at 0:52

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Santiago Squarzon Over a year ago

@EduardoNoyola happy to help :)

Collectives™ on Stack Overflow

How to export a specific Excel column with PowerShell?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related