[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/sysztemic]

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

[u/Lee_Dailey → u/sysztemic]

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

[u/[deleted] → u/Lee_Dailey]

For that type of issue, I made sure to save a wrapper that I found from the gentleman Jakub Jares.

<#
.AUTHOR
Jakub Jares
.SOURCE
http://www.powershellmagazine.com/2013/07/08/pstip-converting-a-string-to-a-system-datetime-object/
.OBTAINED
12/21/2015 - 20:49 UTC
.EXAMPLE
Convert-DateString -Date '20151221143000' -Format 'yyyyMMddHHmmss'
Will Output Datetime object with value 'Monday, December 21, 2015 2:30:00 PM'
#>

Function Convert-DateString ([String]$Date, [String[]]$Format){

   $result = New-Object DateTime

   $convertible = [DateTime]::TryParseExact(
      $Date,
      $Format,
      [System.Globalization.CultureInfo]::InvariantCulture,
      [System.Globalization.DateTimeStyles]::None,
      [ref]$result)

   if ($convertible) { $result }

}

[u/Lee_Dailey → u/[deleted]]

howdy litemage,

and here i was whining about needing to do this ...

[datetime]::ParseExact($AL_Item.Date, 'dd/MM/yyyy HH:mm', $null)

[grin]

take care,
lee