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

[tim--9706]

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:

 

PowerShell

$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

1 2 3 4 5 6 7

$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 9 months, 1 week ago by tim--9706.
• This topic was modified 9 months, 1 week ago by tim--9706.
• This topic was modified 9 months, 1 week ago by tim--9706.

[Olaf Participant Topics: 10 Replies: 2481 Points: 6,543Rank: 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.

[tim--9706 Participant Topics: 1 Replies: 5 Points: 22Rank: Member]

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

PowerShell

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

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

OR

PowerShell

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

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

[Olaf Participant Topics: 10 Replies: 2481 Points: 6,543Rank: Community MVP]

And why not just

PowerShell

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

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

[tim--9706 Participant Topics: 1 Replies: 5 Points: 22Rank: Member]

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

[Olaf Participant Topics: 10 Replies: 2481 Points: 6,543Rank: 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.

[tim--9706]

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

PowerShell

PersonalArea;Employeenumber myLocation;123456 myLocation;234567 otherLocation;345678 otherLocation;

1 2 3 4 5

PersonalArea;Employeenumber myLocation;123456 myLocation;234567 otherLocation;345678 otherLocation;

• This reply was modified 9 months, 1 week ago by tim--9706.

[Olaf Participant Topics: 10 Replies: 2481 Points: 6,543Rank: Community MVP]

PowerShell

$CSVData = @' PersonalArea;Employeenumber myLocation;123456 myLocation;234567 otherLocation;345678 otherLocation; '@ | ConvertFrom-Csv -Delimiter ';' ($CSVData | Where-Object {$_.Employeenumber}).count $CSVData.Count

1 2 3 4 5 6 7 8 9 10 11

$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:

PowerShell

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:\>

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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:\>

[tim--9706]

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

PowerShell

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

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

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

PowerShell

$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

1 2 3 4 5 6 7 8

$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

PowerShell

$CSV = Import-Csv -Path $filepath -Delimiter ";" -Encoding UTF8 | Measure-Object

1	$CSV = Import-Csv -Path $filepath -Delimiter ";" -Encoding UTF8 | Measure-Object

• This reply was modified 9 months, 1 week ago by tim--9706.

[Olaf Participant Topics: 10 Replies: 2481 Points: 6,543Rank: Community MVP]

tim–9706 wrote:

I think the problem was the piping of Measure-Object

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

tim–9706 wrote:

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üß 😉

[tim--9706 Participant Topics: 1 Replies: 5 Points: 22Rank: Member]

Olaf wrote:

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.

[Author]

Posts