Home > Back-end >  Simplest Powershell Import-Excel Question
Simplest Powershell Import-Excel Question

Time:12-11

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

Sample data:

enter image description here

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

CodePudding user response:

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

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

CodePudding user response:

  • 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.
  • Related