Filter CSV-file

Welcome Forums General PowerShell Q&A Filter CSV-file

Viewing 6 reply threads
  • Author
    Posts
    • #189649
      Participant
      Topics: 1
      Replies: 2
      Points: 1
      Rank: Member

      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.

      $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)}

       

      Company Name lastdayofwork mail
      Banana Company John Smith [email protected]
      Pear Company Jane Smith [email protected]
      Banana Company Boris Jeltsin [email protected]
      Banana Company Papa Boy 20/9/2019 [email protected]
      Banana Company Peppa Pig 22/6/2018 [email protected]
      Pear Company Mr Tamagotchi 9/12/2019 [email protected]
    • #189679
      Moderator
      Topics: 3
      Replies: 223
      Points: 1,221
      Helping Hand
      Rank: Community Hero

      Try this, it works…

      $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}
    • #189685
      Participant
      Topics: 1
      Replies: 2
      Points: 1
      Rank: Member

      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 [email protected]

       

      Name lastdayofwork
      —- ————-
      Name lastdayofwork
      —- ————-
      John Smith
      Jane Smith
      Boris Jeltsin
      Peppa Pig 22/6/2018
      Mr Tamagotchi 9/12/2019 

       

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

       

      Name lastdayofwork
      —- ————-
      John Smith
      Jane Smith
      Boris Jeltsin  

       

    • #189688
      Participant
      Topics: 2
      Replies: 513
      Points: 1,322
      Helping Hand
      Rank: Community Hero

      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. 🙂

    • #189706
      Participant
      Topics: 12
      Replies: 1642
      Points: 2,660
      Helping Hand
      Rank: Community Hero

      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
      
    • #189733
      Moderator
      Topics: 3
      Replies: 223
      Points: 1,221
      Helping Hand
      Rank: Community Hero

      When I run your modified code I get all rows except one

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

    • #189961
      Participant
      Topics: 1
      Replies: 2
      Points: 1
      Rank: Member

      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!

       

Viewing 6 reply threads
  • The topic ‘Filter CSV-file’ is closed to new replies.