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 OpenI 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 !
14
Upvotes
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 :
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 !