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
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-inwhere
alias of theWhere-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
, sowhere Company
would also exclude column values that are0
. - By using
-notlike ''
, the LHS is implicitly coerced to a string, which causes both''
and$null
to yield$true
, but not numerical0
values.
- PowerShell's aforementioned implicit to-Boolean coercion also considers numeric