How to parse string from column in csv in Powershell

Question

I have a csv configured as such:

PK,INV_AMT,DATE,INV_NAME,NOTE
1,123.44,634,asdfljk,TEST 12OING 06/01/2010 DATE: 04/10/2012
2,123.44,634,wet aaa,HI HOW ARE YOU 11.11 DATE: 01/01/2011
3,123.44,634,dfssdsdfRR,LOOK AT ME NOW….HI7&&& DATE: 06/11/1997
4,123.44,634,asdfsdgg,LOOK AT ME NOW….HI7&&& DATE: 03-21-2097
5,123.44,634,45746345,LOOK AT ME NOW….HI7&&& DATE: 02/18/2000

How can I parse the date after the string "DATE:" in the note column using powershell?

For example, the first row has the string "TEST 12OING 06/01/2010 DATE: 04/10/2012" in the note column. I need to parse '04/10/2012' out of that row.

I would like to be able to read from a csv file such as the one above and parse out that date and add it as a new column in the csv file.

Thanks for any help.

Answer 1

Split the value of Note property (the default delimiter is space), select the last element (-1) and cast it to a datetime objects. Lastly, return the object back to the pipeline ($_).

Import-Csv test.csv | Foreach-Object { $_.Note = [datetime]$_.Note.Split()[-1]; $_}