CODEWITHSUNDEEP
excelwindowspowershell
Eduardo Noyola
Eduardo Noyola

Reputation: 161

How to export a specific Excel column with PowerShell?

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
}

Upvotes: 2

Views: 2701

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 61093

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

Upvotes: 4

Related Questions