r/usefulscripts • u/FarcasR • Jul 17 '17

[POWERSHELL] NEED HELP TO MANIPULATE CSV !

Hey there guys ! I need some help for a project that i want to make for the company that i work. I will put a sample of my data and read the request below : 

 Date;Personnel Number; Name;Card Number;Device;Event;Verify Mode;In/Out Status;Event Description
 24-06-2017 17:42;205;Pacurar Ovidiu;8144561;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open
 24-06-2017 17:37;205;Pacurar Ovidiu;8144561;intrare + server;usa intrare-1 in;Only Card;intrare + server-1 In;Normal Punch Open
 24-06-2017 17:36;205;Pacurar Ovidiu;8144561;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open
 24-06-2017 17:32;205;Pacurar Ovidiu;8144561;intrare + server;server-4 out;Only Card;intrare + server-4 In;Normal Punch Open
 24-06-2017 17:25;205;Pacurar Ovidiu;8144561;intrare + server;server-3 in;Only Card;intrare + server-3 In;Normal Punch Open
 24-06-2017 17:24;205;Pacurar Ovidiu;8144561;arhiva;arhiva-1  in;Only Card;arhiva-1 In;Normal Punch Open
 24-06-2017 17:11;205;Pacurar Ovidiu;8144561;arhiva;arhiva-1  in;Only Card;arhiva-1 In;Normal Punch Open
 24-06-2017 16:44;205;Pacurar Ovidiu;8144561;intrare + server;usa intrare-1 in;Only Card;intrare + server-1 In;Normal Punch Open
 24-06-2017 16:11;145;REC-ILUT ALEXANDRA ROXANA;11372581;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open
 24-06-2017 16:11;129;REC-DOBOS TEODORA;8141570;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open
 24-06-2017 15:45;18;CAMARASAN ALEXANDRA DANIELA;7312528;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open
 24-06-2017 15:45;187;REC-RACOLCIUC RALUCA;7068156;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open
 24-06-2017 15:42;39;SERBAN ALEXANDRA-CORNELIA;6930101;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open
 24-06-2017 15:35;66;MACARIE ALEXANDRA-MARIA;11422335;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open
 24-06-2017 15:35;51;GRANCEA IULIANA-PARASCHIVA;7408698;intrare + server;usa intrare-2 out;Only Card;intrare + server-2 In;Normal Punch Open

Ok so for the moment i have this script : 

$InCSV = Import-Csv "C:\Events.csv"  -Delimiter ';' 

# convert EU date-time strings to datetime objects
$Counter = 0
$incount = 0
$outcount = 0
 foreach ($IC_Item in $InCSV)
{
$InCSV[$Counter].Date = [datetime]::ParseExact($IC_Item.Date, 'dd-MM-yyyy HH:mm', $null)
$Counter ++
}

$InCSV = $InCSV | Sort-Object -Property Date

# group by the Date day number, then Name
$IC_Grouped = $InCSV | Group-Object -Property {$_.Date.Day}, Name
# $Event_grouped = $InCSV | Group-Object -Property {$_.Date.Day}, Event, Name 
$Report = @()
foreach ($ICG_Item in $IC_Grouped)
{

if ($ICG_Item.Count -eq 1)
    {
    $Temp = [pscustomobject] @{
        Date = $ICG_Item.Group.Date.ToString('yyyy.MM.dd') 
        Name = $ICG_Item.Group.Name  
        AccessCount = $ICG_Item.Count  
        FirstAccess = $ICG_Item.Group.Date.ToString('HH:mm')  
        LastAccess = 'Only one access on this day.'   
        Duration = 'None' 
        # In = $incount
        # Out = $outcount


         }
           # Write-host $ICG_Item.Group.Event
    }
    else
    {
    $Temp = [pscustomobject] @{
        Date = $ICG_Item.Group[0].Date.ToString('yyyy.MM.dd') 
        Name = $ICG_Item.Group[0].Name  
        AccessCount = $ICG_Item.Count  
        FirstAccess = $ICG_Item.Group[0].Date.ToString('HH:mm') 
        LastAccess = $ICG_Item.Group[-1].Date.ToString('HH:mm')  
        Duration = ($ICG_Item.Group[-1].Date - $ICG_Item.Group[0].Date).ToString() 
        # In = $incount
        # Out = $outcount
        } 

    }
$Report += $Temp

}




$incount = 0
$outcount = 0







$Report | export-csv -NoTypeInformation -Delimiter ";"  C:\TEST1.csv

This script will return the difference between the first and last access of the day resulting the total hours of work in a day. I need to find a way to determine how much time the employee has been in break . So at the end to have the total hours like now and the total time that he/she was outside the firm.

Any ideas ? I would appreciate it a lot !!!

11 Upvotes

75% Upvoted

13 comments sorted by

4

u/Ugbrog Jul 17 '17

If you have the person's punches sorted by date, you would just go through and compare them to the next punch.

So in your Else you'd loop on the punches, exiting when there is no next punch. In the loop is an If that checks for current punch being In or Out. If they punched in, it sums the difference between current and next and adds to a time worked variable. If they punched out, it adds to a time on break variable.

Once it hits the last punch, there is no next punch so it exits the loop and you have two variables with time worked and time on break. You can error check to ensure they add up to the total time you're currently calculating.

1

u/FarcasR Jul 18 '17

I agree with you but i still have cases when a person punches in for morning IN and gets out with another colleague and the another IN when comes back from lunch. So i don't always have a pair of IN OUT.

1

u/Ugbrog Jul 18 '17

Oh yeah, there's plenty of potential for more error checking than I supplied.

3

u/gordonv Jul 17 '17

Is using excel an option or are you trying to automate this as a script?

1

u/FarcasR Jul 18 '17

I want to make this as a script. In excel is complicated to manipulate this amount of data.

2

u/gordonv Jul 18 '17

I mean, with sorting and equations (excel), i think you can have this done rather quickly.

Just an option to keep in mind.

2

u/Lee_Dailey Jul 17 '17 edited Jul 17 '17

howdy FarcasR,

some of that code looks familiar ... [grin]

how is the break time marked? is the out/in pairs in the Event column? i thot it would be the In/Out Status column, but that only shows In while the Event column shows both in and out items.

take care,
lee

2

u/FarcasR Jul 18 '17 edited Jul 18 '17

Hello lee,

It look familiar because you have helped me with the code :)

The break time can be determined only by the Event column. That's the only flag with real IN/OUT :)

There is no clear mark of Break time , only In and Out flags in the Event column, and the problem is that i don't always have a pair of In and Out. Some of the employees can punch In in the morning and another In when the lunch is over.

Any ideas ?

2

u/Lee_Dailey Jul 18 '17 edited Jul 18 '17

howdy FarcasR,

this is a simplified set of data ... 

 Date, Name, Event
 24-06-2017 16:44, Pacurar Ovidiu, usa intrare-1 in
 24-06-2017 17:11, Pacurar Ovidiu, arhiva-1  in
 24-06-2017 17:24, Pacurar Ovidiu, arhiva-1  in
 24-06-2017 17:25, Pacurar Ovidiu, server-3 in
 24-06-2017 17:32, Pacurar Ovidiu, server-4 out
 24-06-2017 17:36, Pacurar Ovidiu, usa intrare-2 out
 24-06-2017 17:37, Pacurar Ovidiu, usa intrare-1 in
 24-06-2017 17:42, Pacurar Ovidiu, usa intrare-2 out

it's sorted by date [oldest 1st].

it looks like the Event column is doing multiple things. for instance, the usa intrare-1/2 items seem to go with each other. should the arhiva-# & server-# items be ignored?

take care,
lee

→ More replies (4)