CODEWITHSUNDEEP
datepowershellcsv
user3336725
user3336725

Reputation: 3

Filtering a CSV where the system date format and file date formats differ

I have a large source CSV file with 3 example rows as below

0000000000254000,19/02/2014,"zzzzzzz","close","BIL","5378874","125.00",
0000000000254000,19/12/2013,"zzzzzzz","close","BIL","5378874","125.00",
0000000000254000,19/08/2013,"zzzzzzz","close","BIL","5378874","125.00",

I want to remove all lines that are < 10 days old i.e. where today() - above date column <10 and am close with the following script based on the kind donations of others on this site:

get-content C:\temp\date2.csv | where {
  [date] ($_.split(','))[1] -lt (get-date).date.adddays(-10)
} | set-content "C:\temp\newdate.txt"

It works if I align the dates in the file with the powershell system date format but could anyone help with how I can do this within the above simple script please?

I've tried this at the start but failed miserably:

(Get-Culture).DateTimeFormat.ShortDatePattern="dd/MM/yyyy"

Upvotes: 0

Views: 775

Answers (1)

Raf
Raf

Reputation: 10107

Try this: 

Import-Csv C:\temp\date2.csv -Header c1,c2,c3,c4,c5,c6,c7 | 
where { [datetime]::ParseExact($_.c2, "dd/MM/yyyy", $null) -lt (get-date).date.adddays(-10)} | 
Export-Csv -NoTypeInformation "C:\temp\newdate.txt"

The -Header gives names to columns and you can change that to whatever you want as long as you amend $_.c2 on line 2 accordingly.

Upvotes: 2

Related Questions