How to check if a property is null/empty in a CSV

Welcome Forums General PowerShell Q&A How to check if a property is null/empty in a CSV

Viewing 10 reply threads
  • Author
    Posts
    • #221706
      Participant
      Topics: 1
      Replies: 5
      Points: 22
      Rank: Member

      Hi,

       

      I have a CSV with several properties like PersonalArea;Employeenumber;…

      I want to count the lines in this CSV but only these where the property “Employeenumber” is not null and the PersonalArea is equal  to “myLocation”.

      Currently, I got the following code:

       

      $filepath = "C:path\to\csv"
      $CSV = Import-Csv -Path $filepath -Delimiter ";" -Encoding UTF8 | Measure-Object
      $a = $CSV | Where-Object {$_.PersonalArea -eq "myLocation -and $_.Employeenumber -ne $null}
      #count lines where the expression is true
      $a.count
      #count all lines except op the first one
      $CSV.count

      Here is an CSV example:

      PersonalArea;Employeenumber

      myLocation;123456

      myLocation;234567

      otherLocation;345678

      otherLocation;

       

      Currently, I get the following output:

      $a.count –> 0

      $CSV.count –> 4

       

      Can someone tell me why I getting a wrong output on $a.count?

      I appreciate your help.

      • This topic was modified 1 month, 1 week ago by tim--9706.
      • This topic was modified 1 month, 1 week ago by tim--9706.
      • This topic was modified 1 month, 1 week ago by tim--9706.
    • #221718
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      If I got it right you should check for an empty string instead of $null because Import-Csv only returns strings not objects.

    • #221724
      Participant
      Topics: 1
      Replies: 5
      Points: 22
      Rank: Member

      I already tested it out with an empty string..same result

      $a = $CSV | Where-Object {$_.PersonalArea -eq "myLocation -and $_.Employeenumber -ne ""}

      OR

      $a = $CSV | Where-Object {$_.PersonalArea -eq "myLocation -and $_.Employeenumber -ne ''}
    • #221730
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      And why not just

      $CSV | Where-Object {$_.PersonalArea -eq "myLocation -and $_.Employeenumber}
    • #221739
      Participant
      Topics: 1
      Replies: 5
      Points: 22
      Rank: Member

      I didn’t know this works. But it doesn’t work either.

    • #221742
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      It does work for me with my sample data. 😉 Could you please post a few sanitized but still representative rows of your CSV file? Please format it as code.

    • #221745
      Participant
      Topics: 1
      Replies: 5
      Points: 22
      Rank: Member

      I’m using the same CSV data for testing as I mentioned above. Can you post your code?

      PersonalArea;Employeenumber
      myLocation;123456
      myLocation;234567
      otherLocation;345678
      otherLocation;
      
      
      • This reply was modified 1 month, 1 week ago by tim--9706.
    • #221754
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP
      $CSVData = @'
      PersonalArea;Employeenumber
      myLocation;123456
      myLocation;234567
      otherLocation;345678
      otherLocation;
      '@ | ConvertFrom-Csv -Delimiter ';'
      
      ($CSVData | Where-Object {$_.Employeenumber}).count
      
      $CSVData.Count

      That’s the actual code I’m using to check if it works.
      The output is this:

      PS C:\> $CSVData = @'
      PersonalArea;Employeenumber
      myLocation;123456
      myLocation;234567
      otherLocation;345678
      otherLocation;
      '@ | ConvertFrom-Csv -Delimiter ';'
      
      ($CSVData | Where-Object {$_.Employeenumber}).count
      
      $CSVData.Count
      3
      4
      PS C:\>
    • #221760
      Participant
      Topics: 1
      Replies: 5
      Points: 22
      Rank: Member

      Now it works. First I have added the brackets + .count

      ($CSVData | Where-Object {$_.Employeenumber}).count

      Then I removed these changes and tested it again with my code…

      $filepath = "C:\path\to\csv"
      $CSV = Import-Csv -Path $filepath -Delimiter ";" -Encoding UTF8
      $a =$CSV | Where-Object {$_.PersonalArea -eq "myLocation" -and $_.Employeenumber}
      $a.count
      #count lines where the expression is true
      #$a.count
      #count all lines except op the first one
      $CSV.count

      The output is correct in both test cases.

      Thank you. Could you explain why you are using $_.Employeenumber instead of $_.Employeenumber -ne ‘ ‘ ? Is it just shorter or is there another reason?

      I think the problem was the piping of Measure-Object

      $CSV = Import-Csv -Path $filepath -Delimiter ";" -Encoding UTF8 | Measure-Object
      • This reply was modified 1 month, 1 week ago by tim--9706.
    • #221796
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      I think the problem was the piping of Measure-Object

      Ooops … I missed that completely until you just mentioned it now. Sorry. 😉

      Could you explain why you are using $_.Employeenumber instead of $_.Employeenumber -ne ‘ ‘ ? Is it just shorter or is there another reason?

      Mostly I’m really bad with explaining of what’s going on under the hood. Personally I like to write the code as short as possible but still as descriptive and detailed as possible without using aliasses or abbreviations.

      BTW: Sometimes it’s easier to ask a quesiotn in your native language. There are German Powershell-related forums as well like MCSEBoard.de or the German Microsoft Technet Powershell forum.

      Tschüß 😉

    • #221802
      Participant
      Topics: 1
      Replies: 5
      Points: 22
      Rank: Member

      BTW: Sometimes it’s easier to ask a quesiotn in your native language. There are German Powershell-related forums as well like MCSEBoard.de or the German Microsoft Technet Powershell forum.

      Danke für den Tipp 🙂 Vielen Dank für die Hilfe.

      Tschüss.

Viewing 10 reply threads
  • You must be logged in to reply to this topic.