我想要一个powershell脚本，我从csv文件中提取几行到另一个csv，并计算整列的时间值

Question

这是我在csv文件中的数据

"Day","Person","Start","End","Elapsed"
"2022-10-01","108   ","22:34","22:35","00h, 01m, 25s"
"2022-10-04","108   ","08:44","08:46","00h, 02m, 01s"
"2022-10-10","108   ","14:13","18:19","04h, 06m, 52s"
"2022-10-11","108   ","09:38","09:42","00h, 03m, 58s"
"2022-10-12","108   ","08:27","17:27","09h, 00m, 57s"
"2022-10-14","108   ","09:02","23:20","14h, 18m, 30s"
"2022-10-15","108   ","10:02","10:12","00h, 10m, 25s"
"2022-10-18","108   ","07:48","07:50","00h, 02m, 26s"
"2022-10-19","108   ","08:14","14:51","06h, 36m, 18s"
"2022-10-03","109   ","08:36","19:23","10h, 47m, 19s"
"2022-10-04","109   ","08:23","17:25","09h, 02m, 17s"
"2022-10-05","109   ","08:37","19:52","11h, 14m, 45s"
"2022-10-06","109   ","08:30","20:54","12h, 23m, 42s"
"2022-10-07","109   ","08:32","19:08","10h, 35m, 35s"
"2022-10-10","109   ","08:27","17:23","08h, 56m, 57s"
"2022-10-11","109   ","08:29","16:12","07h, 43m, 12s"
"2022-10-12","109   ","08:32","20:08","11h, 35m, 31s"
"2022-10-13","109   ","08:32","17:33","09h, 01m, 31s"
"2022-10-14","109   ","08:49","19:01","10h, 11m, 49s"
"2022-10-17","109   ","08:33","18:34","10h, 01m, 13s"
"2022-10-18","109   ","08:34","17:54","09h, 20m, 01s"
"2022-10-19","109   ","08:27","08:29","00h, 02m, 08s"
"2022-10-03","112   ","08:04","17:40","09h, 35m, 49s"
"2022-10-04","112   ","09:06","16:26","07h, 19m, 34s"
"2022-10-05","112   ","08:09","17:24","09h, 15m, 06s"
"2022-10-06","112   ","08:02","18:32","10h, 30m, 01s"
"2022-10-07","112   ","08:07","18:40","10h, 32m, 47s"
"2022-10-10","112   ","08:02","18:05","10h, 03m, 03s"
"2022-10-11","112   ","08:05","18:05","10h, 00m, 03s"
"2022-10-12","112   ","08:03","18:04","10h, 01m, 13s"
"2022-10-13","112   ","08:06","18:27","10h, 21m, 10s"
"2022-10-14","112   ","08:23","18:19","09h, 56m, 07s"

我想要的输出是这样的：

"Day","Person","Start","End","Elapsed"
"2022-10-01","108   ","22:34","22:35","00h, 01m, 25s"
"2022-10-04","108   ","08:44","08:46","00h, 02m, 01s"
"2022-10-10","108   ","14:13","18:19","04h, 06m, 52s"
"2022-10-11","108   ","09:38","09:42","00h, 03m, 58s"
"2022-10-12","108   ","08:27","17:27","09h, 00m, 57s"
"2022-10-14","108   ","09:02","23:20","14h, 18m, 30s"
"2022-10-15","108   ","10:02","10:12","00h, 10m, 25s"
"2022-10-18","108   ","07:48","07:50","00h, 02m, 26s"
"2022-10-19","108   ","08:14","14:51","06h, 36m, 18s"
Employee total working hrs with minutes in all days = ?
"2022-10-03","109   ","08:36","19:23","10h, 47m, 19s"
"2022-10-04","109   ","08:23","17:25","09h, 02m, 17s"
"2022-10-05","109   ","08:37","19:52","11h, 14m, 45s"
"2022-10-06","109   ","08:30","20:54","12h, 23m, 42s"
"2022-10-07","109   ","08:32","19:08","10h, 35m, 35s"
"2022-10-10","109   ","08:27","17:23","08h, 56m, 57s"
"2022-10-11","109   ","08:29","16:12","07h, 43m, 12s"
"2022-10-12","109   ","08:32","20:08","11h, 35m, 31s"
"2022-10-13","109   ","08:32","17:33","09h, 01m, 31s"
"2022-10-14","109   ","08:49","19:01","10h, 11m, 49s"
"2022-10-17","109   ","08:33","18:34","10h, 01m, 13s"
"2022-10-18","109   ","08:34","17:54","09h, 20m, 01s"
"2022-10-19","109   ","08:27","08:29","00h, 02m, 08s"
Employee total working hrs with minutes in all days = ?
"2022-10-03","112   ","08:04","17:40","09h, 35m, 49s"
"2022-10-04","112   ","09:06","16:26","07h, 19m, 34s"
"2022-10-05","112   ","08:09","17:24","09h, 15m, 06s"
"2022-10-06","112   ","08:02","18:32","10h, 30m, 01s"
"2022-10-07","112   ","08:07","18:40","10h, 32m, 47s"
"2022-10-10","112   ","08:02","18:05","10h, 03m, 03s"
"2022-10-11","112   ","08:05","18:05","10h, 00m, 03s"
"2022-10-12","112   ","08:03","18:04","10h, 01m, 13s"
"2022-10-13","112   ","08:06","18:27","10h, 21m, 10s"
"2022-10-14","112   ","08:23","18:19","09h, 56m, 07s"
Employee total working hrs with minutes in all days = ?

我使用这个powershell代码以升序对进行排序

`导入csv"；c： \temp\export.csv&quot-"分隔符"|排序对象-属性@｛Expression="person"｝，@｛Expression="Date"｝|导出csv；c： \temp\export2.csv&quot-NoTypeInformation-Delimiter"&quot-强制

`

Answer 1

正如我之前提到的，您不能以发布的方式使用CSV，因为这将是一种无效的CSV格式。尝试以下片段：

$InputData = @'
"Day","Person","Start","End","Elapsed"
"2022-10-01","108   ","22:34","22:35","00h, 01m, 25s"
"2022-10-04","108   ","08:44","08:46","00h, 02m, 01s"
"2022-10-10","108   ","14:13","18:19","04h, 06m, 52s"
"2022-10-11","108   ","09:38","09:42","00h, 03m, 58s"
"2022-10-12","108   ","08:27","17:27","09h, 00m, 57s"
"2022-10-14","108   ","09:02","23:20","14h, 18m, 30s"
"2022-10-15","108   ","10:02","10:12","00h, 10m, 25s"
"2022-10-18","108   ","07:48","07:50","00h, 02m, 26s"
"2022-10-19","108   ","08:14","14:51","06h, 36m, 18s"
"2022-10-03","109   ","08:36","19:23","10h, 47m, 19s"
"2022-10-04","109   ","08:23","17:25","09h, 02m, 17s"
"2022-10-05","109   ","08:37","19:52","11h, 14m, 45s"
"2022-10-06","109   ","08:30","20:54","12h, 23m, 42s"
"2022-10-07","109   ","08:32","19:08","10h, 35m, 35s"
"2022-10-10","109   ","08:27","17:23","08h, 56m, 57s"
"2022-10-11","109   ","08:29","16:12","07h, 43m, 12s"
"2022-10-12","109   ","08:32","20:08","11h, 35m, 31s"
"2022-10-13","109   ","08:32","17:33","09h, 01m, 31s"
"2022-10-14","109   ","08:49","19:01","10h, 11m, 49s"
"2022-10-17","109   ","08:33","18:34","10h, 01m, 13s"
"2022-10-18","109   ","08:34","17:54","09h, 20m, 01s"
"2022-10-19","109   ","08:27","08:29","00h, 02m, 08s"
"2022-10-03","112   ","08:04","17:40","09h, 35m, 49s"
"2022-10-04","112   ","09:06","16:26","07h, 19m, 34s"
"2022-10-05","112   ","08:09","17:24","09h, 15m, 06s"
"2022-10-06","112   ","08:02","18:32","10h, 30m, 01s"
"2022-10-07","112   ","08:07","18:40","10h, 32m, 47s"
"2022-10-10","112   ","08:02","18:05","10h, 03m, 03s"
"2022-10-11","112   ","08:05","18:05","10h, 00m, 03s"
"2022-10-12","112   ","08:03","18:04","10h, 01m, 13s"
"2022-10-13","112   ","08:06","18:27","10h, 21m, 10s"
"2022-10-14","112   ","08:23","18:19","09h, 56m, 07s"
'@ | 
ConvertFrom-Csv

$Result = 
$InputData |
Select-Object -Property Person,
@{
Name       = 'WorkingHours'
Expression = {
New-TimeSpan -Start $_.Start -End $_.End
}
}
$Result | 
Group-Object -Property Person |
ForEach-Object {
$sum = New-TimeSpan
$_.Group.WorkingHours |
ForEach-Object {
$Sum += $_
}
[PSCustomObject]@{
Employee = $_.Name
Hours = [System.Math]::Floor($sum.TotalHours)
Minutes = $Sum.Minutes
}
}

结果是：

Employee Hours Minutes
-------- ----- -------
108         34      20
109        120      55
112         97      35

Answer 2

我终于做对了。

"Day","Person","Start","End","Elapsed"
"2022-10-04","108   ","08:44","08:46","00h, 02m, 01s"
"2022-10-10","108   ","14:13","18:19","04h, 06m, 52s"
"2022-10-11","108   ","09:38","09:42","00h, 03m, 58s"
"2022-10-12","108   ","08:27","17:27","09h, 00m, 57s"
"2022-10-14","108   ","09:02","23:20","14h, 18m, 30s"
"2022-10-15","108   ","10:02","10:12","00h, 10m, 25s"
"2022-10-18","108   ","07:48","07:50","00h, 02m, 26s"
"2022-10-19","108   ","08:14","14:51","06h, 36m, 18s"
Employee total working hrs with minutes in all days = 34 hrs 21 mins 27 secs
"Day","Person","Start","End","Elapsed"
"2022-10-03","109   ","08:36","19:23","10h, 47m, 19s"
"2022-10-04","109   ","08:23","17:25","09h, 02m, 17s"
"2022-10-05","109   ","08:37","19:52","11h, 14m, 45s"
"2022-10-06","109   ","08:30","20:54","12h, 23m, 42s"
"2022-10-07","109   ","08:32","19:08","10h, 35m, 35s"
"2022-10-10","109   ","08:27","17:23","08h, 56m, 57s"
"2022-10-11","109   ","08:29","16:12","07h, 43m, 12s"
"2022-10-12","109   ","08:32","20:08","11h, 35m, 31s"
"2022-10-13","109   ","08:32","17:33","09h, 01m, 31s"
"2022-10-14","109   ","08:49","19:01","10h, 11m, 49s"
"2022-10-17","109   ","08:33","18:34","10h, 01m, 13s"
"2022-10-18","109   ","08:34","17:54","09h, 20m, 01s"
"2022-10-19","109   ","08:27","08:29","00h, 02m, 08s"
Employee total working hrs with minutes in all days = 120 hrs 56 mins 00 secs
"Day","Person","Start","End","Elapsed"
"2022-10-03","112   ","08:04","17:40","09h, 35m, 49s"
"2022-10-04","112   ","09:06","16:26","07h, 19m, 34s"
"2022-10-05","112   ","08:09","17:24","09h, 15m, 06s"
"2022-10-06","112   ","08:02","18:32","10h, 30m, 01s"
"2022-10-07","112   ","08:07","18:40","10h, 32m, 47s"
"2022-10-10","112   ","08:02","18:05","10h, 03m, 03s"
"2022-10-11","112   ","08:05","18:05","10h, 00m, 03s"
"2022-10-12","112   ","08:03","18:04","10h, 01m, 13s"
"2022-10-13","112   ","08:06","18:27","10h, 21m, 10s"
"2022-10-14","112   ","08:23","18:19","09h, 56m, 07s"
Employee total working hrs with minutes in all days = 97 hrs 34 mins 53 secs

这是代码

$input_filename = "c:temptest.txt"
$output_filename = "c:temptest1.txt"
#$match = Select-String -Path "c:temptest.txt" -Pattern '^(?<date>dd/dd/dddd)s+(?<person>[^s]+)s+(?<start>[^s]+)s+(?<end>[^s]+)s+(?<elapsed>.*$)'
$match = Select-String -Path $input_filename -Pattern '^"(?<date>dddd-dd-dd)","(?<person>[^s]+)s+","(?<start>[^"]+)","(?<end>[^"]+)","(?<elapsed>[^"]+)'
#Write-Host $match
$table = [System.Collections.ArrayList]::new()
$i = 0
foreach( $row in $match.Matches )
{
if(++$i -gt 1)
{
$newRow = New-Object -TypeName psobject
$j = 0
foreach($group in $row.Groups)
{
if(++$j -gt 1)
{
$newRow | Add-Member -NotePropertyName $group.Name -NotePropertyValue $group.Value
}
}
$table.Add($newRow)  | Out-Null
}
}
$table = $table | Sort-Object {$_.person},{[System.DateTime]::ParseExact($_.date, "yyyy-MM-dd", $null)}
$table | format-table
$groups = $table | Group-Object {$_.person}
#$groups | Format-Table
$writer = [System.IO.StreamWriter]::new($output_filename)
$pattern = "^(?<hours>d{2})h,s(?<minutes>d{2})m, (?<seconds>d{2})s" 
foreach($group in $groups)
{
$writer.WriteLine("""Day"",""Person"",""Start"",""End"",""Elapsed""")
$timeSpan = New-TimeSpan
$hours = 0
foreach($col in $group.Group)
{
$writer.WriteLine("""" + [System.String]::Join(""",""", @($col.date, $col.person.PadRight(6), $col.start, $col.end, $col.elapsed)) + """")
$match = $col.elapsed | Select-String -Pattern $pattern
$hours = [int]$match.Matches.groups[1].value  #.hours.Value
$minutes = [int]$match.Matches.groups[2].value
$seconds = [int]$match.Matches.groups[3].value
$newTimeSpan = New-TimeSpan -Hours $hours -Minutes $minutes -Seconds $seconds
$timeSpan += $newTimespan
}
$totalHours =("{0:00} hrs {1:00} mins {2:00} secs" -f (24 * $timeSpan.Days + $timeSpan.Hours),$timeSpan.Minutes, $timeSpan.Seconds)
$writer.WriteLine("Employee total working hrs with minutes in all days = {0}", $totalHours)
$last = $group.Group | Select-Object -Last 1
$last.elapsed = $last.elapsed + ", " + $totalHours
}
$table | format-table
$writer.Flush()
$writer.Close()