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.

Shay Levy · Accepted Answer · 2012-06-09 11:13:43Z

5

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]; $_}

answered Jun 9, 2012 at 11:13

Shay Levy

128k34 gold badges193 silver badges210 bronze badges

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

2 Comments

user1445620 Over a year ago

Thanks for the answer. One more thing. If there is data after the date as such: "TEST 12OING 06/01/2010 DATE: 04/10/2012 aaa test 123", how can I just pull out the same value '04/10/2012'. Would this just be something similar to a substring function? Thanks.

Shay Levy Over a year ago

Try with a regular expression, replace the string with captured characters of: anything after 'date: ', then any character until the first space: 'TEST 12OING 06/01/2010 DATE: 04/10/2012 aaa test 123' -replace '^.+ DATE: ([^\s]+) .+$','$1'

Bob · Accepted Answer · 2012-06-09 03:24:00Z

Since the DATE: ########## section is at the end, and you want to separate it into its own section, simply replacing DATE: with , works:

# Open files for reading/writing line by line
$reader = New-Object System.IO.StreamReader("in.csv")
$writer = New-Object System.IO.StreamWriter("out.csv")

# Copy first line over, with an extra ",DATE"
$writer.WriteLine($reader.ReadLine() + ",DATE")

# Process lines until in.csv ends
while (($line = $reader.ReadLine()) -ne $null) {
    # Get index of last occurrence of "DATE: "
    $index = $line.LastIndexOf("DATE: ")

    # Replace last occurrence of "DATE: " with a comma
    $line = $line.Remove($index, 6).Insert($index, ',')

    # Write the modified line to the new file
    $writer.WriteLine($line)
}

# Close the file handles
$reader.Close()
$writer.Close()

If there is always a space before DATE:, then replacing " DATE: " instead of "DATE: " may be slightly better.

ajk · Accepted Answer · 2012-06-10 03:39:27Z

1

An alternative using regular expressions:

Get-Content in.csv |
# Perform a replace on each line with the DATE: pattern. For convenience,
# eliminate preceding whitespace.
Foreach-Object { $_ -replace "\s*DATE: (\d{1,2}[-/]\d{1,2}[-/]\d{2,4}).*",",`$1" } |
Set-Content out.csv

Edit: Updated in response to the OP's question about eliminating stray characters after the date.

edited Jun 10, 2012 at 3:39

answered Jun 9, 2012 at 4:34

ajk

4,6232 gold badges21 silver badges24 bronze badges

2 Comments

user1445620 Over a year ago

Thanks for the answers ajk, bob, and @shay-levy .. One more thing. If there is data after the date as such: "TEST 12OING 06/01/2010 DATE: 04/10/2012 aaa test 123", how can I just pull out the same value '04/10/2012'. Would this just be something similar to a substring function? Thanks.

ajk Over a year ago

Again there are multiple ways to handle that. Since I've already started down the regular expression path, I stuck with that and updated my answer.

Collectives™ on Stack Overflow

How to parse string from column in csv in Powershell

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related