8

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.

Filed in: Articles, Online Only Tags: , ,

8 Responses to "Skipping empty CSV objects"

  1. sag1v says:

    Thanks for that post shay..
    a question though,
    lets say my csv look like this:
    #start of CSV File
    UserName,GroupName
    user1,Grouptest1
    user2,Grouptest2
    ,Grouptest3
    #end of file

    now i have a code that adds users to groups…

    (note: I don’t use the grouplist from the csv on purpose, but I do have a situation that the grouplist is longer than the username list and that’s the reason why I got empty objects or lines even though I used your solution )

    #start of code
    Import-Module activedirectory
    $tempuserlist = (Import-Csv C:List.csv | Where-Object { ($_.PSObject.Properties | ForEach-Object {$_.Value}) -ne $null} | Select -ExpandProperty UserName) -join “,” | Out-String
    $userlist = $tempuserlist.split(“,”)
    $grouplist = (“grouptest3,grouptest4”).split(“,”)
    foreach ($g in $grouplist)
    {
    Add-ADGroupMember -Identity $g -Member $userlist
    }

    #end of code

    I get the following error:

    Add-ADGroupMember : Cannot find an object with identity: ‘
    ‘ under: ‘DC=ksbg,DC=local’.
    At line:7 char:23
    + Add-ADGroupMember <<<< -Identity $g -Member $userlist
    + CategoryInfo : ObjectNotFound: (
    :ADPrincipal) [Add-ADGroupMember], ADIdentityNotFoundException
    + FullyQualifiedErrorId : SetADGroupMember.ValidateMembersParameter,Microsoft.ActiveDirectory.Management.Commands.AddADGroupMember

    Add-ADGroupMember : Cannot find an object with identity: '
    ' under: 'DC=ksbg,DC=local'.
    At line:7 char:23
    + Add-ADGroupMember <<<< -Identity $g -Member $userlist
    + CategoryInfo : ObjectNotFound: (
    :ADPrincipal) [Add-ADGroupMember], ADIdentityNotFoundException
    + FullyQualifiedErrorId : SetADGroupMember.ValidateMembersParameter,Microsoft.ActiveDirectory.Management.Commands.AddADGroupMember
    ** if I have 2 lists with same number of objects.. lets say I remove user3 from the csv file I get no error.
    any help will be much appreciate 🙂

    • ShayLevy says:

      In this case you can easily filter out empty UserName items.

      $tempuserlist = Import-Csv C:List.csv | Where-Object {$_.UserName} | …

      • sag1v says:

        amazing. looks so simple 🙂
        it works! (had to remove the pipe “out-string” though)
        thanks shay
        can you please explain to me how does it works?

        • ShayLevy says:

          Sure, you pipe the content of the csv to the Where-Object cmdlet, which tests if its expression (i.e {$_.UserName} ) returns true (has a value). If it’s evaluated to True the current object is written back to the pipeline otherwise it is discarded.

  2. Yau says:

    Hi ShayLevy,
    Thank you very much for the code. wish you can help on my query below:
    I created the sample powershell script as per below to create the new contacts in the domain using csv:
    Import-Csv Contactsample.csv |foreach{New-ADObject -Type Contact -Name $_.DisplayName -OtherAttributes @{‘displayName’=$_.DisplayName;’mail’=$_.mail;’company’=$_.company ;’proxyAddresses’=$_.proxyAddresses} -Path “OU=Contacts,DC=Contoso,DC=Dir”}

    The CSV with the field below:
    DisplayName, mail, company, proxyaddresses
    name1,name1@contoso.com,abc,smtp:name1@contoso.com
    name2,name2@contoso.com,,smtp:name2@contoso.com
    name3,name3@contoso.com,,smtp:name3@contoso.com

    because not every contact with company value, may i know how to do it in the powershell, so i can create every contact even company value is empty?
    thank you very much in advanced for your help..

  3. Shay Levy says:

    @Yau

    You need to take care of it explicitly. You can create an IF/ELSE statement, check if the value is empty and if so create a hashtable for the -OtherAttributes parameter that doesn’t include the company attribute, ELSE run the hashtable with it.

  4. Sajjad says:

    In my case I have csv files with following columns
    User Name Ip Address Date Time

    there are 8 lines blank and then comes the data
    csgh30.01 10.0.16.14 16-Nov-15 8:06:29
    and then again afer 8 blank rows
    fnvs11.02 10.5.17.4 16-Nov-15 12:32:00

    how i can only get data without spaces in output csv file – I have three files containing almost 30,000 lines and blank 50000 blank line so challenge is here to only get data not blank lines

    pleas help

Leave a Reply

Submit Comment

© 2016 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: