Here is my data in csv file
"Day","Person","Start","End","Elapsed"
"2022-10-12","121 ","08:03","18:04","10h, 01m, 06s"
"2022-10-12","sajjad ","15:38","22:59","07h, 21m, 22s"
"2022-10-13","121 ","08:06","18:25","10h, 19m, 09s"
"2022-10-13","sajjad ","08:37","16:02","07h, 24m, 51s"
"2022-10-13","128 ","08:27","17:33","09h, 06m, 34s"
"2022-10-13","140 ","09:02","09:03","00h, 01m, 08s"
"2022-10-17","114 ","08:12","14:58","06h, 46m, 37s"
"2022-10-17","sajjad ","14:08","23:57","09h, 48m, 40s"
"2022-10-17","128 ","08:30","12:23","03h, 53m, 34s"
"2022-10-17","133 ","08:17","08:19","00h, 02m, 30s"
I want to extract entire lines that contains person "sajjad" word with working hours calculation, my desired output is like this:
"Day","Person","Start","End","Elapsed","employee working hours minutes secs"
"2022-10-12","sajjad ","15:38","22:59","07h, 21m, 22s"
"2022-10-13","sajjad ","08:37","16:02","07h, 24m, 51s"
"2022-10-17","sajjad ","14:08","23:57","09h, 48m, 40s","24hrs 34 mints 33"
i used this script
$log = get-content "C:\Users\NOC-Notebook\Desktop\export.csv"
foreach ($line in $log) {
if (($line -like '*sajjad*') )
{
$line | out-file -FilePath "C:\Users\NOC-Notebook\Desktop\export1.txt" -Append
}
}
but not getting appropriate results, from this code only getting person sajjad lines in a single column with out calculation, any help will be highly appreciated.
CodePudding user response:
As already pointed out - if you have a proper CSV file you should treat it as such.
Basically should be all you need:
Import-Csv -Path 'C:\Users\NOC-Notebook\Desktop\export.csv' |
Where-Object -Property 'Person' -Match -Value 'sajjad'
If you want to save it to a file you add a pipe and
Export-Csv -Path 'C:\Users\NOC-Notebook\Desktop\export1.txt' -NoTypeInformation
Edit:
If I got it right according to your additional comment you want to sum the working hours of 'sajjad', right? But doing it the way you showed in your question would end up in an invalid CSV.
If you're just interested in the result you can use a calculated property to calculate the working hours per day and sum them up later on with a loop.
Input data
$InputData = @'
"Day","Person","Start","End","Elapsed"
"2022-10-12","121 ","08:03","18:04","10h, 01m, 06s"
"2022-10-12","sajjad ","15:38","22:59","07h, 21m, 22s"
"2022-10-13","121 ","08:06","18:25","10h, 19m, 09s"
"2022-10-13","sajjad ","08:37","16:02","07h, 24m, 51s"
"2022-10-13","128 ","08:27","17:33","09h, 06m, 34s"
"2022-10-13","140 ","09:02","09:03","00h, 01m, 08s"
"2022-10-17","114 ","08:12","14:58","06h, 46m, 37s"
"2022-10-17","sajjad ","14:08","23:57","09h, 48m, 40s"
"2022-10-17","128 ","08:30","12:23","03h, 53m, 34s"
"2022-10-17","133 ","08:17","08:19","00h, 02m, 30s"
'@ |
ConvertFrom-Csv
Adding the working hours as timespan values
$Result =
$InputData |
Where-Object -Property 'Person' -Match -Value 'sajjad' |
Select-Object -Property *,
@{
Name = 'WorkingHours'
Expression = {
New-TimeSpan -Start $_.Start -End $_.End
}
}
Calculating the sum
$Result |
ForEach-Object {
$WorkingHoursSum = $_.WorkingHours
}
$WorkingHoursSum
You can calculate with the result if you need. And for better readability you could format the output as you need. For example like this:
'sajjad worked {0:n0} hours and {1} minutes in total.' -f $WorkingHoursSum.TotalHours, $WorkingHoursSum.Minutes
CodePudding user response:
I finally got it right. Timespan doesn't work because start and end times do not have seconds.
date person start end elapsed
---- ------ ----- --- -------
10/17/2022 114 08:12 14:58 06h, 46m, 37s, 06 hrs 46 mins 37 secs
10/13/2022 121 08:06 18:25 10h, 19m, 09s, 10 hrs 19 mins 09 secs
10/13/2022 128 08:27 17:33 09h, 06m, 34s
10/17/2022 128 08:30 12:23 03h, 53m, 34s, 13 hrs 00 mins 08 secs
10/17/2022 133 08:17 08:19 00h, 02m, 30s, 00 hrs 02 mins 30 secs
10/13/2022 140 09:02 09:03 00h, 01m, 08s, 00 hrs 01 mins 08 secs
10/12/2022 sajjad 15:38 22:59 07h, 21m, 22s
10/13/2022 sajjad 08:37 16:02 07h, 24m, 51s
10/17/2022 sajjad 14:08 23:57 09h, 48m, 40s, 24 hrs 34 mins 53 secs
$match = Select-String -Path "c:\temp\test.txt" -Pattern '^(?<date>\d\d/\d\d/\d\d\d\d)\s (?<person>[^\s] )\s (?<start>[^\s] )\s (?<end>[^\s] )\s (?<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}
$table | format-table
$groups = $table | Group-Object {$_.person}
#$groups | Format-Table
$pattern = "^(?<hours>\d{2})h,\s(?<minutes>\d{2})m, (?<seconds>\d{2})s"
foreach($group in $groups)
{
$timeSpan = New-TimeSpan
$hours = 0
foreach($col in $group.Group)
{
$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)
$last = $group.Group | Select-Object -Last 1
$last.elapsed = $last.elapsed ", " $totalHours
}
$table | format-table