i want a powershell script from which i extract few lines from csv file to another csv and calculate-CodePudding

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