excluding Empty cells in powershell

Question

I have Excel spreadsheet and few empty blank cells are present in couple of rows. I am writing a powershell script to exclude those empty blank cells while using that spread sheet and display the output as the excluded rows. Below is the powershell code:

# Load the ImportExcel module
Import-Module ImportExcel

# Set the path to your Excel file
$excelFilePath = "C:\Users\madamaneril\powershell\NSG_Rules.xlsx"

# Read the Excel file
$excelData = Import-Excel -Path $excelFilePath

# Filter rows with empty cells
$nonEmptyRows = $excelData | Where-Object {
    $_.PSObject.Properties.Value -notcontains ""
}

# Display the rows without empty cells
if ($nonEmptyRows) {
    $nonEmptyRows | Format-Table
} else {
    Write-Host "No rows with empty cells found."
}

After executing the above script, it is giving the all rows as output not the empty cell rows. I have tried different ways to test the same but not getting the expected output. Can someone please help me if any changes needs to do to exclude the empty blank cells and display the rows as output.

Answer 1

• Import-Excel, from the third-party ImportExcel module, imports empty cell values as $null, not as empty strings ('' or "" or [string]::Empty).

• Therefore, replace $_.PSObject.Properties.Value -notcontains "" with $_.PSObject.Properties.Value -notcontains $null in order to detect rows that do not have empty cells - or use -contains $null to get those that do have empty cells.

• Additionally, you need to at least compare the count of filtered rows to the total count of rows in order to detect if rows with empty cells were present.

  if ($nonEmptyRows.Count -lt $excelData.Count) {
     Write-Host 'Rows with empty cells found.'
  }
  Write-Host 'All rows with non-empty cells:'
  $nonEmptyRows | Format-Table
  

  • However, you may want to create a list of those partially filled-in rows too.
  • The intrinsic .Where() method offers a convenient way to partition an input list into two lists based on a filter criterion, yielding one list with all elements that do meet the criterion and another with those that do not. See the code below.

$excelFilePath = "C:\Users\madamaneril\powershell\NSG_Rules.xlsx"

# Read the Excel file
[array] $excelData = Import-Excel -Path $excelFilePath

# Partition rows into two lists:
#  * One with only rows *without* empty ($null) values.
#  * On with rows *with* empty values.
$rowsFullyFilled, $rowsWithEmptyCells = $excelData.Where(
  { $_.PSObject.Properties.Value -notcontains $null }, 
  'Split'
)

# For-display output of the results:

Write-Verbose -Verbose 'Rows WITHOUT empty cells:'
$rowsFullyFilled | Format-Table | Out-Host

Write-Verbose -Verbose 'Rows WITH empty cells:'
$rowsWithEmptyCells | Format-Table | Out-Host