Skipping empty CSV objects

Working with CSV files in PowerShell is a common practice. You import the file, loop on its records and you’re good to go. Sometimes however you may find yourself in a situation where you get a file that has blank lines in it, and those lines can break your script. Consider the following CSV content:

## sample.csv ##
column1,column2,column3
Value1,Value2,Value3
Value1,Value2,Value3
<empty line>
<empty line>
<empty line>
## file ends here

On the surface, nothing looks suspicious when you import the file:

PS> Import-Csv sample.csv
column1  column2  column3
-------  -------  -------
Value1   Value2   Value3
Value1   Value2   Value3
PS>

But if you pipe it to Format-List you can clearly see what’s going on. You get empty objects for each empty line in the file.

PS> Import-Csv sample.csv | Format-List

column1 : Value1
column2 : Value2
column3 : Value3

column1 : Value1
column2 : Value2
column3 : Value3

column1 :
column2 :
column3 :

column1 :
column2 :
column3 :

column1 :
column2 :
column3 :

To filter out empty objects you need to test that all properties are not equal to an empty string and throw them away.

You might be attempted to do that with:

Import-Csv sample.csv |
Where-Object {$_.column1 -ne '' -and $_.column1 -ne '' -and $_.column1 -ne ''}

But what if each record has 20 properties, or even more? This is where the PSObject property comes to rescue. In a nutshell, PSObject allows us to work with any object in the same way without really knowing its structure. PowerShell wraps the base object in a PSObject and provide us a simplified and consistent view of the object, its methods, properties, and so on. One of the properties of PSObject is Properties, and it gives us a list of properties of the base object.

On a related note, PSObject and other members are not visible when you pipe an object to the Get-Member cmdlet. To reveal those members add the -Force switch to Get-Member.

For our purpose, we can process the properties list and filter out those who have a Value of null.

Import-Csv sample.csv |
Where-Object { ($_.PSObject.Properties | ForEach-Object {$_.Value}) -ne $null} |
Format-List

column1 : Value1
column2 : Value2
column3 : Value3

column1 : Value1
column2 : Value2
column3 : Value3

In PowerShell 3.0 and the new Member Enumeration feature we can get the same result in less characters:

Import-Csv sample.csv |
Where-Object { $_.PSObject.Properties.Value -ne $null}

I logged an Import-Csv feature enhancement, and you can add your vote if you’d like to have a built-in option of ignoring empty lines.