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 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
    • #189679
      Participant
      Topics: 3
      Replies: 136
      Points: 719
      Helping Hand
      Rank: Major Contributor

      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 papa.boy@company.com

       

      
      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: 510
      Points: 1,301
      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.

      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: 9
      Replies: 1277
      Points: 1,051
      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
      Participant
      Topics: 3
      Replies: 136
      Points: 719
      Helping Hand
      Rank: Major Contributor

      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
  • You must be logged in to reply to this topic.