CODEWITHSUNDEEP
powershell
MISNole
MISNole

Reputation: 1062

Selecting specific Excel columns using PowerShell to export to another spreadsheet

I receive Excel spreadsheets in a specific format that includes two columns that have the same column header name - which I do not need but the format of the spreadsheet will not change so I have to work around these two columns.

I am trying to use PowerShell and the Import-Excel module to select only the columns I need and then export to a new spreadsheet so that an SSIS process can pick them up.

I feel like I am almost there but there is something in my script that is importing the columns that should be excluded for some reason. Looking at my screenshots below, it looks like it is trying to bring in the header from the spreadsheet and I am not sure how to get around that. I did try to add a -NoHeader tag on the export but that did not work.

The PS script:

Clear-Host

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

$ExcelFiles = Get-ChildItem $SourceFileDirectory -Filter *.xlsx

foreach ($file in $ExcelFiles)
{

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

Write-Host $ImportFile
Write-Host $DestinationFile

$data = Import-Excel -Path $ImportFile -HeaderName "CompanyID", "CompanyName", "CreateDate", "FileName", "ReferenceNumber" | Select-Object "CompanyID", "CompanyName", "CreateDate", "FileName", "ReferenceNumber"
Write-Host $data

Import-Excel -Path $ImportFile -HeaderName "CompanyID", "CompanyName", "CreateDate", "FileName", "ReferenceNumber" | Select-Object "CompanyID", "CompanyName", "CreateDate", "FileName", "ReferenceNumber" | Export-Excel -Path $DestinationFile | Select-Object "CompanyID", "CompanyName", "CreateDate", "FileName", "ReferenceNumber"

}

This is what the data in the original spreadsheet looks like:

enter image description here

And this is what the data looks like after the script has been run:

enter image description here

Below is what my desired output should be:

enter image description here

Upvotes: 2

Views: 9701

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 59930

Import-Excel similar to Import-Csv will already recognize the headers of your file, if you want the same header names which I think you do there is no need to specify that argument (-HeaderName). Though, if you actually wanted to use specific headers, you could do something like (note the -StartRow 2):

Import-Excel -Header ExampleHeader1, ExampleHeader2, ... -StartRow 2

Now for the code, give this a try, I think it should be working. I also added the Set-ExcelRange using -HorizontalAlignment Center which I think you want :)

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

$ExcelFiles = Get-ChildItem $SourceFileDirectory -Filter *.xlsx
$headers = 'CompanyID','CompanyName','Dupe1','CreateDate','FileName','Dupe2','ReferenceNumber'

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

    $sheetName = 'ExampleSheet' # => Define the WorkSheet Name here

    Write-Host $ImportFile
    Write-Host $DestinationFile

    $xlsx = Import-Excel -Path $ImportFile -HeaderName $headers -StartRow 2 |
    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

Related Questions