how to filter empty values in a column in powershell

Question

i have input file text.csv

"#P","A","E","S"
"1","2","3","4"
"3","5","6",""
"A","B","C",""
"D","F","G","H"

I want output csv file export.csv as

"P","A","E","S"
"1","2","3","4"
"D","F","G","H"




 $File1 = Import-Csv -Path "test.csv" -Header "P","A","E","S" | Where { $_.S -ne $null } | Export-Csv "export.csv" -Force -NoTypeInformation

Answer 1

As explained earlier in the comments, a Comma-separated values (csv) file typically stores tabular data (numbers and text) in plain text, in which case each line will have the same number of fields.

Based on this you might assume that the Import-Csv and ConvertFrom-Csv should never return a $Null for a property (column) value but just an empty string:

$Csv = @'
1,2,3,4
3,5,6,
A,B,C,
D,F,G,H
'@
$Csv |ConvertFrom-Csv -Header "P","A","E","S" |
    ForEach-Object { if ($Null -eq $_.S) { 'Null' } else { 'String' } }

String
String
String
String

But be careful with this assumption as this is not always the case. If not just the last value is omitted but also the preceding comma, the concerned ConvertFrom-Csv - and Import-Csv cmdlet might return a $Null (note that there is no comma behind the A,B,C in the following example):

$Csv = @'
1,2,3,4
3,5,6,
A,B,C
D,F,G,H
'@
$Csv |ConvertFrom-Csv -Header "P","A","E","S" |
    ForEach-Object { if ($Null -eq $_.S) { 'Null' } else { 'String' } }

String
String
Null
String

Besides that, there is an unfortunate bug in the concerned ConvertFrom-Csv - and Import-Csv cmdlet where the last cell of an empty column read by ConvertFrom-Csv is inconsistently $Null #17702:

$Csv = @'
1,2,3,
3,5,6,
A,B,C,
D,F,G,
'@
$Csv |ConvertFrom-Csv -Header "P","A","E","S" |
    ForEach-Object { if ($Null -eq $_.S) { 'Null' } else { 'String' } }

String
String
String
Null

Therefore I would be better safe than sorry and test on both $Null ($Null -ne $_.S)¹⁾ and an empty string ($_.S -ne '') which can be simplified to just: ... |Where-Object { $_.S } |... this results in a so called "Truthy" condition if the concerned field is not empty (or $Null), see also Conversion to Bool):

$Csv = @'     # Or as in your revised question:
1,2,3,4       # "1","2","3","4"
3,5,6,        # "3","5","6",""
A,B,C,        # "A","B","C",""
D,F,G,H       # "D","F","G","H"
'@
# Import-Csv -Path "test.csv" -Header "P","A","E","S" |
#     Where-Object { $_.S } |Export-Csv "export.csv" -Force -NoTypeInformation
$Csv |ConvertFrom-Csv -Header "P","A","E","S" |
    Where-Object { $_.S } |ConvertTo-Csv

"P","A","E","S"
"1","2","3","4"
"D","F","G","H"

1) if you check for $Null; $Null should be at the left side of the comparison operator