Welcome › Forums › General PowerShell Q&A › How to check if a property is null/empty in a CSV
- This topic has 10 replies, 2 voices, and was last updated 9 months, 1 week ago by
Participant.
-
AuthorPosts
-
-
April 23, 2020 at 9:05 am #221706
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:
PowerShell1234567$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.countHere 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.
-
April 23, 2020 at 9:08 am #221718
If I got it right you should check for an empty string instead of $null because Import-Csv only returns strings not objects.
-
April 23, 2020 at 9:11 am #221724
I already tested it out with an empty string..same result
PowerShell1$a = $CSV | Where-Object {$_.PersonalArea -eq "myLocation -and $_.Employeenumber -ne ""}OR
PowerShell1$a = $CSV | Where-Object {$_.PersonalArea -eq "myLocation -and $_.Employeenumber -ne ''} -
April 23, 2020 at 9:19 am #221730
And why not just
PowerShell1$CSV | Where-Object {$_.PersonalArea -eq "myLocation -and $_.Employeenumber} -
April 23, 2020 at 9:26 am #221739
I didn’t know this works. But it doesn’t work either.
-
April 23, 2020 at 9:44 am #221742
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.
-
April 23, 2020 at 10:01 am #221745
I’m using the same CSV data for testing as I mentioned above. Can you post your code?
PowerShell12345PersonalArea;EmployeenumbermyLocation;123456myLocation;234567otherLocation;345678otherLocation;-
This reply was modified 9 months, 1 week ago by
tim--9706.
-
This reply was modified 9 months, 1 week ago by
-
April 23, 2020 at 10:15 am #221754PowerShell1234567891011$CSVData = @'PersonalArea;EmployeenumbermyLocation;123456myLocation;234567otherLocation;345678otherLocation;'@ | 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:PowerShell1234567891011121314PS C:\> $CSVData = @'PersonalArea;EmployeenumbermyLocation;123456myLocation;234567otherLocation;345678otherLocation;'@ | ConvertFrom-Csv -Delimiter ';'($CSVData | Where-Object {$_.Employeenumber}).count$CSVData.Count34PS C:\> -
April 23, 2020 at 10:39 am #221760
Now it works. First I have added the brackets + .count
PowerShell1($CSVData | Where-Object {$_.Employeenumber}).countThen I removed these changes and tested it again with my code…
PowerShell12345678$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.countThe 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
PowerShell1$CSV = Import-Csv -Path $filepath -Delimiter ";" -Encoding UTF8 | Measure-Object-
This reply was modified 9 months, 1 week ago by
tim--9706.
-
This reply was modified 9 months, 1 week ago by
-
April 23, 2020 at 12:16 pm #221796
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üß 😉
-
April 23, 2020 at 12:21 pm #221802
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.
-
-
AuthorPosts
- The topic ‘How to check if a property is null/empty in a CSV’ is closed to new replies.