Selecting specific Excel columns using PowerShell to export to another spreadsheet

Question

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:

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

Below is what my desired output should be:

Santiago Squarzon · Accepted Answer · 2021-08-05 14:33:42Z

2

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
}

edited Aug 5, 2021 at 14:33

answered Aug 4, 2021 at 21:38

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

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

6 Comments

MISNole Over a year ago

Thanks for the answer - but that doesn't work for me. When I modified my script to use your code, I get the following: "Failed importing the Excel workbook 'C:\STLFeeReport\Test\Test2.xlsx' with worksheet '': Duplicate column headers found on row '1' in columns '3 6'. Column headers must be unique, if this is not a requirement please use the '-NoHeader' or '-HeaderName' parameter."

Santiago Squarzon Over a year ago

@MISNole you're right, my bad, I did not see you had 2 columns with the same header name. I did update my code, try again, I think it should work now.

Santiago Squarzon Over a year ago

Import-Excel -Path $ImportFile -HeaderName $headers -StartRow 2 | Select-Object * -ExcludeProperty Dupe* should give you the expected results, I recreated your file and it works if you use the $headers variable.

MISNole Over a year ago

Thank you - I had to remove the -PassThru tag to get it to work but it is now working as expected.

Santiago Squarzon Over a year ago

@MISNole happy to help

|

Collectives™ on Stack Overflow

Selecting specific Excel columns using PowerShell to export to another spreadsheet

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related