Filter in csv using powershell

user_17574843 0
Viewed 106

I have a csv to import in the power shell, I can import the file with the command:

Import-Csv -Path 'c:/.../etc' -Delimiter ","

And perform some simple filters like this:

$b = "Date development"
$a| Where {$_.$b -l '11/06/2021'} | Format-Table

Given this, I would like to know how I would filter an interval between date stamps. The formatting would be:

Name, development date, ID  --- Headers
P0113D, 10/06/2021,33  
P0123D, 4/16/2020, 42  
P0423D, 10/25/2021, 213  
P0113D, 11/22/2021, 298

I would like to import the Date Range from 10/06/2021 to 10/25/2021 29 and export the filtered file.

Thanks in advance !

csv powershell
2 Answers

You would do it like this, note that, we're using $_.'development date' -as [datetime], this will convert the current values on the development date column, which are strings, to a datetime object.

In this example, it is filtering a range between 10/06/2021 and 10/25/2021 including both dates.

@'
Name, development date, ID
P0113D, 10/06/2021,33  
P0123D, 4/16/2020, 42  
P0423D, 10/25/2021, 213  
P0113D, 11/22/2021, 298
'@ | ConvertFrom-Csv | Where-Object {
    $date = $_.'development date' -as [datetime]
    $date -ge [datetime]'10/06/2021' -and $date -le [datetime]'10/25/2021'
}

Output:

Name   development date ID
----   ---------------- --
P0113D 10/06/2021       33 
P0423D 10/25/2021       213

Note that, the above is there as an example, if you're trying it out from a CSV file the syntax would be:

Import-Csv -Path 'c:/.../etc' -Delimiter "," | Where-Object { ....

To clarify on the difference between using -as and casting [datetime] to the string, here is a practical example:

[datetime]'11/22/2021'       # => Monday, November 22, 2021 12:00:00 AM
'11/22/2021' -as [datetime]  # => Monday, November 22, 2021 12:00:00 AM
[datetime]'notadate!!!'      # => Exception thrown
'notadate!!!' -as [datetime] # => Nothing is returned // no errors
user_153395440

Here you go. Please change the snip below to match your date range. Any issues please respond. Thanks.

Import-Csv -Path 'c:/.../etc' -Delimiter ","

$filterDate = (Get-Date).AddDays(-7)

$Data = Import-CSV "H:\Data.csv" | Where-Object {$_.Date -as [datetime] -lt $filterDate}
user_146386940
Related