delete rows having more than a day old date in csv file using Powershell

Question

Noob question. I have a csv file with the following format

2019-04-18 08:29:47,ACE001P,QL.IN.XCENTER.ERECEIPT.FACADE,2

The date and time stamp goes back almost a month. Now I only want data for the last day's date, means 1 day old data. I am using below code, but it gives me no output.

$Data = Import-CSV "E:\Chayan\QL.IN.XCENTER.ERECEIPT.FACADE_ACE001P_1.csv"
$CutoffDate = (Get-Date).AddDays(-30)
$Data | Where-Object {$_.Date -as [datetime] -lt $CutoffDate} | Out-File .\QL.IN.XCENTER.ERECEIPT.FACADE_ACE001P_2.csv

I know I am doing something extremely stupid. Just need a way to make this work.

I don't see any headers in your CSV file. You need a Date header in order to access the date property ($_.Date). (Get-Date).AddDays(-30) should also be (Get-Date).AddDays(-1) for one day old data. — AdminOfThings
– AdminOfThings, Commented Apr 30, 2019 at 12:07

AdminOfThings · Accepted Answer · 2019-04-30 12:24:41Z

2

In order to use Import-Csv effectively, you need headers in your data. I used the -Header switch and added made up headers that include Date. Feel free to change those. It can be omitted entirely if your file already has headers.

$Data = Import-CSV "E:\Chayan\QL.IN.XCENTER.ERECEIPT.FACADE_ACE001P_1.csv" -Header "Date","Col2","Col3","Col4"
$CutoffDate = (Get-Date).AddDays(-30)
$Data | Where-Object {$_.Date -as [datetime] -gt $CutoffDate} |
   Export-Csv -Path ".\QL.IN.XCENTER.ERECEIPT.FACADE_ACE001P_2.csv" -NoTypeInformation

I switched the -lt to -gt because you are giving the low end of the date range. This means you need to find a date greater than that, i.e. today is greater than yesterday.

I left .AddDays(-30) as your date starter even though the post says you want one day old data. That should be changed to .AddDays(-1) or .AddHours(-24) if you really want data within the last day.

edited Apr 30, 2019 at 12:24

answered Apr 30, 2019 at 12:15

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

delete rows having more than a day old date in csv file using Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related