Filter CSV-file

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

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]

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]

 

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.

https://gist.github.com/vexx32/fd286d7fa5ef43a815183d56ceb04c69

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:

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

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

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!