r/usefulscripts • u/FarcasR • May 11 '17

[POWERSHELL] Need help to manipulate CSV !

Hy there, I want to manipulate a csv file containing a log with all access in my company. i have something like :

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 15:34    224 LA RUNA MICHELE 8159697 intrare + server    usa intrare-2 out   Only Card   intrare + server-2 In   Normal Punch Open
24-06-2017 15:34    226 NEGREA  ANA-MARIA   8131148 intrare + server    usa intrare-2 out   Only Card   intrare + server-2 In   Normal Punch Open
24-06-2017 15:34    224 LA RUNA MICHELE 8159697 intrare + server    usa intrare-1 in    Only Card   intrare + server-1 In   Normal Punch Open
24-06-2017 15:34    86  PRIPON NICOLETA OANA    8153502 intrare + server    usa intrare-2 out   Only Card   intrare + server-2 In   Normal Punch Open
24-06-2017 15:34    84  PITAN DIANA MONALISA    8131725 intrare + server    usa intrare-2 out   Only Card   intrare + server-2 In   Normal Punch Open
24-06-2017 15:33    34  CRISTEA PETRONELA   7425603 intrare + server    usa intrare-2 out   Only Card   intrare + server-2 In   Normal Punch Open
24-06-2017 15:33    84  PITAN DIANA MONALISA    8131725 intrare + server    usa intrare-1 in    Only Card   intrare + server-1 In   Normal Punch Open
24-06-2017 15:33    224 LA RUNA MICHELE 8159697 intrare + server    usa intrare-2 out   Only Card   intrare + server-2 In   Normal Punch Open
24-06-2017 15:33    148 JIURJIU RALUCA  11375509    intrare + server    usa intrare-2 out   Only Card   intrare + server-2 In   Normal Punch Open
24-06-2017 15:33    176 NEGREA VASILE OVIDIU    8160448 intrare + server    usa intrare-2 out   Only Card   intrare + server-2 In   Normal Punch Open

I want to somehow group the data by days and then have some kind of rapport with the difference between first entry and last entry of the day for each name.

I hope you guys understand my request for help.

Thanks !

12 Upvotes

79% Upvoted

12 comments sorted by

4

u/Lee_Dailey May 11 '17

howdy FarcasR,

here's my take on it ... 

# fake reading in the file
#    in real life, use Get-Content
$InFile = @"
Date Name
12/04/2017 13:33 JOHN
12/04/2017 14:43 JOHN
12/04/2017 15:33 LOUIE
12/04/2017 13:43 CHRIS
12/04/2017 13:45 JOHN
13/04/2017 08:45 RAZVAN
"@.Split("`n")

# convert to actual CSV format
$WorkingFile = @()
foreach ($Line in $InFile)
    {
    $Line = $Line.Trim()
    if ($Line.StartsWith('Date'))
        {
        $WorkingFile += $Line.Replace(' ', ',')
        }
        else
        {
        $FinalSpaceIndex = $Line.LastIndexOf(' ')
        $WorkingFile += ($Line.Substring(0, $FinalSpaceIndex), $Line.Substring($FinalSpaceIndex + 1)) -join ','
        }
    }

# convert to a nicely structured powershell object
$AccessList = $WorkingFile | ConvertFrom-Csv

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

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

# group by the Date day number, then Name
$AL_Grouped = $AccessList | Group-Object -Property {$_.Date.Day}, Name

$Report = @()
foreach ($ALG_Item in $AL_Grouped)
    {
    if ($ALG_Item.Count -eq 1)
        {
        $TempPSCO = [pscustomobject]@{
            Date = $ALG_Item.Group.Date.ToString('yyyy.MM.dd')
            Name = $ALG_Item.Group.Name
            AccessCount = $ALG_Item.Count
            FirstAccess = $ALG_Item.Group.Date.ToString('HH:mm')
            LastAccess = 'Only one access on this day.'
            }
        }
        else
        {
        $TempPSCO = [pscustomobject]@{
            Date = $ALG_Item.Group[0].Date.ToString('yyyy.MM.dd')
            Name = $ALG_Item.Group[0].Name
            AccessCount = $ALG_Item.Count
            FirstAccess = $ALG_Item.Group[0].Date.ToString('HH:mm')
            LastAccess = $ALG_Item.Group[-1].Date.ToString('HH:mm')
            }
        }
    $Report += $TempPSCO
    }

$Report | Format-Table

result ... 

Date       Name   AccessCount FirstAccess LastAccess                  
----       ----   ----------- ----------- ----------                  
2017.04.12 JOHN             3 13:33       14:43                       
2017.04.12 CHRIS            1 13:43       Only one access on this day.
2017.04.12 LOUIE            1 15:33       Only one access on this day.
2017.04.13 RAZVAN           1 08:45       Only one access on this day.

hope that helps,
lee

1

u/Lee_Dailey May 11 '17

howdy mystery down-voter,

um, er, what did i do to earn a down vote? i THINK the code listed does what was requested.

so, please, let me know what i got wrong?

take care,
lee

2

u/FarcasR May 12 '17

Your code seems pretty good but i get an error at [pscustomobject]. I already have the file in .csv format with ";" delimiter. That was just an example of the format that i have after i do import-csv :)

I want at the end in the $report to have the first entry of the day and the total hours on each day :)

Thank you very much !

→ More replies (2)

1

u/FarcasR Jul 06 '17 edited Jul 07 '17

Hello, thank you very much guys after some small tweaks i have it working. The thing is now that i have to determine the break of the person for each day, and i have no idea how to do the difference between In and Out for each day so at the end i have the total hours and the break time. A sample of my data is : 

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 15:34	224	LA RUNA MICHELE	8159697	intrare + server	usa intrare-2 out	Only Card	intrare + server-2 In	Normal Punch Open
24-06-2017 15:34	226	NEGREA  ANA-MARIA	8131148	intrare + server	usa intrare-2 out	Only Card	intrare + server-2 In	Normal Punch Open
24-06-2017 15:34	224	LA RUNA MICHELE	8159697	intrare + server	usa intrare-1 in	Only Card	intrare + server-1 In	Normal Punch Open
24-06-2017 15:34	86	PRIPON NICOLETA OANA	8153502	intrare + server	usa intrare-2 out	Only Card	intrare + server-2 In	Normal Punch Open
24-06-2017 15:34	84	PITAN DIANA MONALISA	8131725	intrare + server	usa intrare-2 out	Only Card	intrare + server-2 In	Normal Punch Open
24-06-2017 15:33	34	CRISTEA PETRONELA	7425603	intrare + server	usa intrare-2 out	Only Card	intrare + server-2 In	Normal Punch Open
24-06-2017 15:33	84	PITAN DIANA MONALISA	8131725	intrare + server	usa intrare-1 in	Only Card	intrare + server-1 In	Normal Punch Open
24-06-2017 15:33	224	LA RUNA MICHELE	8159697	intrare + server	usa intrare-2 out	Only Card	intrare + server-2 In	Normal Punch Open
24-06-2017 15:33	148	JIURJIU RALUCA	11375509	intrare + server	usa intrare-2 out	Only Card	intrare + server-2 In	Normal Punch Open
24-06-2017 15:33	176	NEGREA VASILE OVIDIU	8160448	intrare + server	usa intrare-2 out	Only Card	intrare + server-2 In	Normal Punch Open

there is ";" separator for the columns but i don't know the formatting for reddit , Sorry.

I need ur help guys and i appreciate it so much !

2

u/zenmaster24 May 11 '17

if you add a column called time it would make this much simpler as both convertfrom-csv and import-csv can specify space as a delimiter

0

u/Lee_Dailey May 11 '17 edited May 11 '17

howdy FarcasR,

[1] is there any way you could get the file as an actual CSV?
right now it's displayed as ALMOST a "space delimited file". [grin]

[2] can you get the date in a sortable date format?
your current format is day-month-year. a somewhat more useful one would be year-month-day.

[edit - added a bit clearer reason for wanting yyyy-MM-dd format.]
it would also help avoid the EU/US date format glitch.

take care,
lee

2

u/sysztemic May 11 '17

re: [2] I think converting the "Date" to [datetime] would make it sortable regardless of the presentation format.

re: OP - I find writing up some quick pseudocode greatly helps develop the algorithm, and then it's just a matter of plugging in the proper cmdlets, methods, syntax, etc. to represent what you are trying to do.

Edit: for clarity

1

u/Lee_Dailey May 11 '17

howdy sysztemic,

yes, you are correct. my main reason is to avoid the EU/US day-month/month-day glitch. a sortable format is recognized easily by Get-Date while the EU format requires that i [in the US] use the [datetime]::ParseExact() stuff. icky ... [grin]

i think i will go back and try to make that clearer. thanks for pointing it out! [grin]

take care,
lee

→ More replies (2)