Delete lines of tab separated file with date older than 21 days

I have multiple .csv files looking something like this which is tab-separated:

Call ID Latitude    Longitude   Date and Time   wasd
wasd    12.1241234  12.1243 2021-12-15 07:45:13.123 wasd
wasd    12.1241234  12.1243 2021-12-15 07:45:13.123 wasd
wasd    12.1241234  12.1243 2022-1-15 07:45:13.123  wasd
wasd    12.1241234  12.1243 2022-2-15 07:45:13.123  wasd
wasd    12.1241234  12.1243 2022-2-15 07:45:13.123  wasd

Using powershell I need to delete all the lines of the file with a date older than 21 days. unfortunately im limited to use Powershell only. Any Powershell experts out there?

CodePudding user response：

In other words, you need to only keep the rows that have a 'Date and Time' newer or equal than 21 days ago To do that, use a Where-Object clause to filter on only the rows you want to keep:

# get the reference date set to midnight
$threeWeeksAgo = (Get-Date).AddDays(-21).Date
Import-Csv -Path 'D:\Test\Blah.tsv' -Delimiter "`t" | 
# pass rows newer or equal than 21 days ago
Where-Object { [datetime]$_.'Date and Time' -ge $threeWeeksAgo } | 
# write out to (new) file
Export-Csv -Path 'D:\Test\CleanedUpBlah.tsv' -Delimiter "`t" -NoTypeInformation

On my Dutch machine, [datetime]$_.'Date and Time' parses the dates correctly, but depending on your system's locale (date & time culture settings) you may need to use
[datetime]::ParseExact($_.'Date and Time', 'yyyy-MM-dd HH:mm:ss.fff', $null)
Since your CSV file is TAB delimited, I'm using extension .tsv (Tab Separated Values) but the code will accept any extension you have given it.

Result on screen using your example:

Call ID Latitude   Longitude Date and Time          wasd
------- --------   --------- -------------          ----
wasd    12.1241234 12.1243   2022-2-15 07:45:13.123 wasd
wasd    12.1241234 12.1243   2022-2-15 07:45:13.123 wasd