CODEWITHSUNDEEP
powershell
Nate
Nate

Reputation: 862

Calculate time from document

Building on my previous question (thanks @MartinBrandl), I'd like to grab the time values to calculate time as well.

Example data:

07:30 - 07:45PMTS09526052 | Sev9 | Location| | Due: 12/23/2016
NON PC HARDWARE - TROUBLESHOOT SW
Complete this Job

Martin Brandl came up with this to get the other data I need:

Select-String $WLDir -pattern '(PMT[S|T]\d{8})' -Context 0,2 | ForEach-Object {
[PSCustomObject]@{
    Time = $_.Matches.Groups[1].Value
    Topic = $_.Context.PostContext[0]
    Status = $_.Context.PostContext[1]
}
} | ConvertTo-Csv -NoTypeInformation

I'd like to grab the 07:30 - 07:45 bit from right before the PMT part. I plan to use this to get the time:

$StartTime = [However I get the `7:30` here]
$EndTime = [However I get the `7:45` here]
$ElapsedTime = (NEW-TIMESPAN –Start $StartTime –End $EndTime).TotalHours

and I'd add a fourth field to the CSV:

Elapsed = $ElapsedTime

But I'm not sure how specifically to grab the two times and isolate them as variables. Can anyone assist?

Upvotes: 1

Views: 84

Answers (2)

mklement0
mklement0

Reputation: 438008

Ansgar Wiechers provided the crucial pointers in comments on the question (adding capture groups (\d{2}:\d{2}) to the regular expression captures time strings such as 07:30), and LotPing's helpful answer fleshed them out to a working solution that directly makes Nate's code work.

Let me offer a more concise alternative that uses PowerShell's type accelerators and casting:

Select-String $WLDir -pattern '(\d{2}:\d{2}) - (\d{2}:\d{2})(PMT[ST]\d{8})' -Context 0,2 |
    ForEach-Object {
        [pscustomobject] @{
            Time = $_.Matches.Groups[3].Value
            Topic = $_.Context.PostContext[0]
            Status = $_.Context.PostContext[1]
            Elapsed = (
             [datetime] $_.Matches.Groups[2].Value - [datetime] $_.Matches.Groups[1].Value
            ).TotalHours
        }
    } | ConvertTo-Csv -NoTypeInformation $OutFile

  • Casting the time strings captured via the first 2 capture groups ($_.Matches.Groups[1].Value and $_.Matches.Groups[2].Value) to [datetime] converts them to [datetime] (System.DateTime) instances whose time portion reflects the specified time of day (and whose date portion defaults to today).

  • You can directly subtract a [datetime] instance from another to get the time span between them: the result is a [timespan] (System.TimeSpan) instance, whose .TotalHours property can then be accessed.

Note that the solution assumes that PM values are represented in 24-hour format (e.g., 3 PM must be represented as 15:00).

Upvotes: 1

user6811411
user6811411

Reputation:

In your first question the time span wasn't in the Csv as it wasn't referenced by the RegEx. Now it is and 7:30 being $matches.groups[1].value and so forth

$WLDir = ".\File.txt" 
$Pattern='(\d{2}:\d{2}) - (\d{2}:\d{2})(PMT[S|T]\d{8})'
Select-String $WLDir -pattern $Pattern -Context 0,2 |
    ForEach-Object {
        $StartTime=[datetime]::ParseExact($_.Matches.Groups[1].Value,"HH:mm",$null)
        $EndTime=[datetime]::ParseExact($_.Matches.Groups[2].Value,"HH:mm",$null)
        $ElapsedTime = (NEW-TIMESPAN –Start $StartTime –End $EndTime).TotalHours
        [PSCustomObject]@{
            Time = $_.Matches.Groups[3].Value
            Topic = $_.Context.PostContext[0]
            Status = $_.Context.PostContext[1]
            ElapsedHrs = $ElapsedTime
        }
} | ConvertTo-Csv -NoTypeInformation

Giving this output (I added a second testentry with PMTT)

"Time","Topic","Status","ElapsedHrs"
"PMTS09526052","NON PC HARDWARE - TROUBLESHOOT SW","Complete this Job","0,25"
"PMTT10952605","NON PC HARDWARE - TROUBLESHOOT SW","Complete this Job","0,25"

The column Time still doesn't contain the Time (my locale has a decimal comma)

Upvotes: 2

Related Questions