Grouping/Sum imported CSV (text)

PowerShell Help
1

Hi All,

I have an imported CSV:
Project,StartDate,StartTime,EndDate,EndTime,Duration
Project 1,19122018,16:39:26,19122018,16:39:28,00:01.8
Project 2,19122018,16:39:28,19122018,16:39:29,00:00.3
Project 3,19122018,16:39:29,19122018,16:39:29,00:00.4
Project 4,19122018,16:39:29,19122018,16:39:29,00:00.3
Project 5,19122018,16:39:29,19122018,16:39:30,00:00.3
Project 6,19122018,16:39:30,19122018,16:39:30,00:00.3
Project 1,19122018,16:39:30,19122018,16:39:31,00:00.5

Each project can occur multiple times on the same day or over several days. I am attempting to sum the duration of each project on each day.

$output = (Import-CSV -Path csvPath -Delimiter ",") | Group-Object -property StartDate,Project

$output

foreach ($name in $output)
{
    write-host $name.name "," $name.Group.Duration
}

I have gotten as far as the above, which groups by date, then by project as desired, unfortunately I am struggling to Sum the duration as they appear to be stored as text rather then timespan?

Is anyone able to suggest a way around this?

Thanks in advance

2

The format of the duration in your CSV data seems a little bit odd for me. I think you’d be better off when you use the start and end dates and times. I tweaked your data a little bit because your data almost do not make sense. :wink: I’m pretty sure there are more sophisticated ways to achieve what you need but at least it’s working (I hope). :wink: 

$Source = @’

Project,StartDate,StartTime,EndDate,EndTime,Duration

Project 1,19122018,16:29:26,19122018,16:39:28,00:01.8

Project 2,19122018,16:19:28,19122018,16:39:29,00:00.3

Project 3,19122018,16:36:29,19122018,16:39:29,00:00.4

Project 4,19122018,16:22:29,19122018,16:39:29,00:00.3

Project 5,19122018,16:15:29,19122018,16:39:30,00:00.3

Project 6,19122018,16:12:30,19122018,16:39:30,00:00.3

Project 1,19122018,15:30:30,19122018,15:55:31,00:00.5

'@

$csv = ConvertFrom-Csv -InputObject $Source -Delimiter ‘,’

$output = $csv | Group-Object -property StartDate,Project


foreach ($name in $output){

$Sum = $name.Group | ForEach-Object {$Start = [DateTime]::ParseExact(($.StartDate + $.StartTime) ,‘ddMMyyyyHH:mm:ss’,$null) ;$End = [DateTime]::ParseExact(($.EndDate + $.EndTime) ,‘ddMMyyyyHH:mm:ss’,$null) ; New-TimeSpan -Start $Start -End $End} | Measure-Object -Property TotalSeconds -Sum

$Duration = New-TimeSpan -Seconds $Sum.Sum

[PSCustomObject]@{

Project = $name.name

Duration = “{0:00}:{1:00}:{2:00}” -f $Duration.Hours, $Duration.Minutes,$Duration.Seconds

}

}

3

Thanks Olaf, your may be right, there may be a more sophisticated way to achieve what I need but I am just greatful that its working.

Thanks again for your help.