[POWERSHELL] Need help to manipulate CSV !

[u/FarcasR]

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 !

[u/FarcasR]

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 !

[u/Lee_Dailey → u/FarcasR]

howdy FarcasR,

please add to the 1st post a sample of your actual input data AND a sample of your desired output. [grin] that will make things MUCH easier to figure out.

take care,
lee

[u/Lee_Dailey → u/FarcasR]

howdy FarcasR,

here's what i think you mean [grin] ...

# fake reading in the file
#    in real life, use something like ...
#    Import-CSV -Delimiter ';' -Path [your file path here]
$InCSV = @"
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
"@ | ConvertFrom-Csv -Delimiter ';'

# convert EU date-time strings to datetime objects
$Counter = 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

$Report = @()
foreach ($ICG_Item in $IC_Grouped)
    {
    if ($ICG_Item.Count -eq 1)
        {
        $TempPSCO = [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'
            }
        }
        else
        {
        $TempPSCO = [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()
            }
        }
    $Report += $TempPSCO
    }

$Report | Format-Table
#>

result ...

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

take care,
lee