Filter CSV-file

PowerShell Help
1

Hi,

I have a CSV-file where I need to filter the column lastdayofwork. I want to keep all rows with a null value or dates that have not expired 31 days from today.

[pre]$date = get-date (Get-Date).AddDays(-31) -Format MM/dd/yyyy
$file = Import-Csv C:\temp\file.csv -Encoding default | Where-Object{(!$.lastdayofwork) -or ($.lastdayofwork -ge $date)}[/pre]

 

Company Name lastdayofwork mail
Banana Company John Smith John.smith@company.com
Pear Company Jane Smith jane.smith@company.com
Banana Company Boris Jeltsin boris.jeltsin@company.com
Banana Company Papa Boy 20/9/2019 papa.boy@company.com
Banana Company Peppa Pig 22/6/2018 Peppa.pig@company.com
Pear Company Mr Tamagotchi 9/12/2019 Mr.tamagotchi@company.com
2

Try this, it works…

[pre]

$date = get-date (Get-Date).AddDays(-31) -Format MM/dd/yyyy

Import-Csv -Path C:\temp\file.csv -Encoding ascii | Where-Object {[string]::IsNullOrEmpty($_.lastdayofwork) -or $_.lastdayofwork -ge $date}
[/pre]
3

Hi Kiran,

Thanks for your reply. When I run your modified code I get all rows except one

Banana Company Papa Boy 20/9/2019 papa.boy@company.com
 

[pre]

Name lastdayofwork

Name lastdayofwork

John Smith
Jane Smith
Boris Jeltsin
Peppa Pig 22/6/2018
Mr Tamagotchi 9/12/2019 [/pre]

 

and when I Remove the -or $_.lastdayofwork -ge $date I recieved only the rows with null value.

 

[pre]Name lastdayofwork

John Smith
Jane Smith
Boris Jeltsin [/pre]

 

4

You’re probably going to need to parse the date into a properly comparable format, I think. Date strings can be compared, but you may not get reliable results from comparing the strings.

I’m not sure on the latter condition as it’s not super clear exactly what you’re getting at with the expiry, but the parsing will get you a date object you can use and compare in ways that are consistent and not as prone to error. :slight_smile:

5

Another method similar to Joel’s code, but this uses a calculated expression to do the parse and then you can do date filters to your hearts content

$date = (Get-Date).AddDays(-31)

$file = Import-Csv C:\temp\file.csv | 
        Select Company, 
               Name, 
               @{Name='LastDayOfWork';Expression={[datetime]::ParseExact($_.LastDayOfWork, "MM/dd/yyyy", [cultureinfo]::CurrentCulture)}}, 
               Mail

$theseAreTheUsersYoureLookingFor = $file | Where-Object {[string]::IsNullOrWhitespace($_.LastDayOfWork) -or $_.LastDayOfWork -lt $date}

$theseAreTheUsersYoureLookingFor
6

Exactly, as Joel /u/ta11ow @vexx32 mentioned you need to parse the date value. Thank you.

7

Hi,

I found a problem with my lastdayofwork column. All dates used slash instead of dash so I replaced them and then used Rob Simmers code and its works. I haven’t tried the other solutions yet.

Did I do something wrong with the format or is slash unsupported in the date format?

Thanks everyone for the help and I really appreciate it!