Grouping/Sum imported CSV (text)

Welcome Forums General PowerShell Q&A Grouping/Sum imported CSV (text)

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
4 months ago.

  • Author
    Posts
  • #130815

    Participant
    Topics: 5
    Replies: 3
    Points: 61
    Rank: Member

    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

  • #130829

    Participant
    Topics: 1
    Replies: 1266
    Points: 1,391
    Helping Hand
    Rank: Community Hero

    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. 😉 I'm pretty sure there are more sophisticated ways to achieve what you need but at least it's working (I hope). 😉

    $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
        }
    }
  • #130863

    Participant
    Topics: 5
    Replies: 3
    Points: 61
    Rank: Member

    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.

The topic ‘Grouping/Sum imported CSV (text)’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort