Simplest Powershell Import-Excel Question

Question

Output contents of Excel file where one or more columns are NOT empty.

Sample data:

Want to output just the Company, City, State and first Product

My lame attempt to code:

$File  = 'C:\test1.xlsx'
$data = Import-Excel -Path $File | Where-Object "Company" -ne " "
$data 

Output is same as input, i.e. all lines appear

Answer 1

Simple answer...........hours in the making:

$File  = 'C:\test1.xlsx'
$data = Import-Excel -Path $File | where {$_.Company} 
$data 

Answer 2

• Your own answer is effective for column values that are either strings or not filled in (in which case they are $null):

  • where {$_.Company} relies on PowerShell's implicit to-Boolean coercion and uses the built-in where alias of the Where-Object cmdlet.

  • As such, you could simplify your solution by using simplified syntax (which is also what you used in your initial attempt, which mistakenly used " ", i.e. a single space for comparison):

    $File  = 'C:\test1.xlsx'
    $data = Import-Excel -Path $File | where Company
    $data
    

• A generalized solution that also works with columns that contain numeric data:

  $File  = 'C:\test1.xlsx'
  $data = Import-Excel -Path $File | where Company -notlike ''
  $data
  

  • PowerShell's aforementioned implicit to-Boolean coercion also considers numeric 0 to be $false, so where Company would also exclude column values that are 0.
  • By using -notlike '', the LHS is implicitly coerced to a string, which causes both '' and $null to yield $true, but not numerical 0 values.