Export .xlsx file based on conditions in PowerShell data mining

user_15200763 0
Viewed 70

I have the code below which imports an Excel file and then exports the workbook into one sheet:

$sheets = (Get-ExcelSheetInfo -Path 'C:\TEMP\Template\TemplateName.xlsx').Name 
$i = 1
foreach ($sheet in $sheets) {
    Import-Excel -WorksheetName $sheet -Path 'C:\TEMP\Template\TemplateName.xlsx' -StartRow 5 -HeaderName 'Property','Current settings','Proposed settings' | Export-Excel -Path C:\Temp\Template\Changes.xlsx -AutoSize
    Write-Progress -Activity "Generating Changes" -Status "Worksheet $i of $($sheets.Count) completed" -PercentComplete (($i / $sheets.Count) * 100)  
    $i++
}

The problem with the code above is that it outputs the xlsx file last row first and then goes in reverse. I would like to maintain the original order of the imported file.

Secondly, I've been playing with different ways to assign columns to PS Object and use Where-Object to filter the output, but can't figure it out.

What I'd like to do is if the Column "Current settings" is blank or does NOT match text in column "Proposed settings", then leave that row out of the export. Essentially, I would just like to fill in rows where "Current settings" is not blank and -notmatch "Proposed settings.

export-to-excel conditional-statements excel powershell automation
1 Answers

The problem I see on your code, aside from not filtering the Current settings column, is that you're outputting to your Excel file on each iteration of your outer loop, hence, you're replacing the output for the contents of the last Worksheet (this is because you're not using -Append switch).

However, the proper and efficient way to do it, instead of appending to a file first generate the output you want in memory and then output it to the file once (this applies always as long as the file fits in memory).

$param = @{
    Path       = 'C:\TEMP\Template\TemplateName.xlsx'
    StartRow   = 5
    HeaderName = 'Property', 'Current settings', 'Proposed settings'
}

# foreach worksheet in this file
Get-ExcelSheetInfo -Path 'C:\TEMP\Template\TemplateName.xlsx' | ForEach-Object {
    # set the worksheetname name in $param
    $param['WorksheetName'] = $_.Name
    # import the worksheet and enumerate it
    foreach($line in Import-Excel @param) {
        $currSettings = $line.'Current settings'
        $propSettings = $line.'Proposed settings'
        # if the value of 'Current settings' cell is equal to the value of
        # 'Proposed settings' cell OR is empty / white spaces, skip it, go to next iteration
        if($currSettings -eq $propSettings -or [string]::IsNullOrWhiteSpace($currSettings)) {
            continue
        }
        # if we're here, condition before this was not true, hence we want to
        # output this line
        $line
    }
} | Export-Excel -Path C:\Temp\Template\Changes.xlsx -AutoSize
user_153395440
Related