We are trying to eliminate our employee time clock by using our job tracking software. The job tracking software also logs employee punch in, out and lunches. However, it will not export to a file formatted for use with our payroll program. I'm hoping that a script can be written to automatically reformat the data so it can be imported into our payroll software.
I've pasted the sample data table below, and the following are the formatting parameters:
We need to split each row of data into 4 separate rows of data (1 for each punch: Start time, lunch time, lunch stop, and stop time). Each new row of data would be formatted as:
YYYYMMDD,HHMMSS,BBBBB(employee code padded out to 5 characters),1(static number one),*(* is for a start time or Lunch start punch and # would be used for Lunch stop or Stop time punch),,,,,
The only other concerns would be:
-A “split day” where an employee might log into epace for Start of Shift and End of Shift, multiple times within the same 24 hour period. I believe ePace creates a new Basic time record for each Shift start/shift stop transaction set that are input.
-A shift that traverses a date. For example an employee that starts at 10pm Monday and works until 6:00am Tuesday
-A lunch that traverses a date. For example a lunch Start time of 11:30pm and a Lunch Stop time of 12:30am.
<colgroup><col><col span="6"></colgroup><tbody>
</tbody>
I've pasted the sample data table below, and the following are the formatting parameters:
We need to split each row of data into 4 separate rows of data (1 for each punch: Start time, lunch time, lunch stop, and stop time). Each new row of data would be formatted as:
YYYYMMDD,HHMMSS,BBBBB(employee code padded out to 5 characters),1(static number one),*(* is for a start time or Lunch start punch and # would be used for Lunch stop or Stop time punch),,,,,
The only other concerns would be:
-A “split day” where an employee might log into epace for Start of Shift and End of Shift, multiple times within the same 24 hour period. I believe ePace creates a new Basic time record for each Shift start/shift stop transaction set that are input.
-A shift that traverses a date. For example an employee that starts at 10pm Monday and works until 6:00am Tuesday
-A lunch that traverses a date. For example a lunch Start time of 11:30pm and a Lunch Stop time of 12:30am.
Employee Code | Start Date | Start Time | Lunch Start | Lunch Stop | Stop Date | Stop Time |
8070 | 04/07/2014 | 6:42 AM | 12:33 PM | 1:06 PM | 04/07/2014 | 5:11 PM |
24 | 04/07/2014 | 6:45 AM | 12:08 PM | 12:33 PM | 04/07/2014 | 3:15 PM |
4911 | 04/07/2014 | 6:49 AM | 10:48 AM | 11:32 AM | 04/07/2014 | 3:32 PM |
1200 | 04/07/2014 | 6:58 AM | 1:42 PM | 2:06 PM | 04/07/2014 | 3:28 PM |
1241 | 04/07/2014 | 6:59 AM | 11:42 AM | 12:35 PM | 04/07/2014 | 3:16 PM |
1174 | 04/07/2014 | 7:00 AM | 04/07/2014 | 7:01 AM | ||
1060 | 04/07/2014 | 7:00 AM | 1:03 PM | 1:31 PM | 04/07/2014 | 3:32 PM |
66 | 04/07/2014 | 7:01 AM | 1:04 PM | 1:32 PM | 04/07/2014 | 3:31 PM |
4126 | 04/07/2014 | 7:01 AM | 12:41 PM | 1:11 PM | 04/07/2014 | 3:41 PM |
9144 | 04/07/2014 | 7:10 AM | 04/07/2014 | 8:32 AM | ||
1256 | 04/07/2014 | 7:18 AM | 04/07/2014 | 12:19 PM | ||
1400 | 04/07/2014 | 7:19 AM | 12:44 PM | 1:09 PM | 04/07/2014 | 2:46 PM |
1141 | 04/07/2014 | 7:19 AM | 1:58 PM | 2:31 PM | 04/07/2014 | 3:50 PM |
1195 | 04/07/2014 | 7:21 AM | 1:14 PM | 2:12 PM | 04/07/2014 | 3:44 PM |
5177 | 04/07/2014 | 7:21 AM | 12:00 PM | 12:30 PM | 04/07/2014 | 4:51 PM |
1037 | 04/07/2014 | 7:22 AM | 2:25 PM | 2:59 PM | 04/07/2014 | 4:09 PM |
6279 | 04/07/2014 | 7:29 AM | 11:54 AM | 12:34 PM | 04/07/2014 | 3:22 PM |
0223 | 04/07/2014 | 7:41 AM | 12:29 PM | 12:50 PM | 04/07/2014 | 4:44 PM |
6942 | 04/07/2014 | 7:44 AM | 1:30 PM | 1:53 PM | 04/07/2014 | 4:36 PM |
1266 | 04/07/2014 | 7:50 AM | 1:29 PM | 1:59 PM | 04/07/2014 | 4:48 PM |
7043 | 04/07/2014 | 7:59 AM | 2:04 PM | 2:26 PM | 04/07/2014 | 4:52 PM |
1225 | 04/07/2014 | 8:08 AM | 12:00 PM | 12:47 PM | 04/07/2014 | 4:21 PM |
1574 | 04/07/2014 | 8:22 AM | 04/07/2014 | 4:37 PM | ||
1216 | 04/07/2014 | 8:32 AM | 04/07/2014 | 10:17 AM | ||
4472 | 04/07/2014 | 8:35 AM | 12:20 PM | 1:27 PM | 04/07/2014 | 4:56 PM |
0216 | 04/07/2014 | 8:38 AM | 12:34 PM | 1:04 PM | 04/07/2014 | 5:11 PM |
1261 | 04/07/2014 | 9:08 AM | 1:00 PM | 1:40 PM | 04/07/2014 | 5:10 PM |
1179 | 04/07/2014 | 9:32 AM | 1:19 PM | 2:18 PM | 04/07/2014 | 6:29 PM |
9144 | 04/07/2014 | 10:29 AM | 12:06 PM | 12:34 PM | 04/07/2014 | 3:01 PM |
1174 | 04/07/2014 | 2:32 PM | 10:00 PM | 10:30 PM | 04/08/2014 | 2:58 AM |
4472 | 04/07/2014 | 3:25 PM | 04/07/2014 | 4:45 PM | ||
7299 | 04/07/2014 | 3:32 PM | 11:14 PM | 11:38 PM | 04/07/2014 | 11:58 PM |
7355 | 04/07/2014 | 3:32 PM | 11:14 PM | 11:39 PM | 04/07/2014 | 11:58 PM |
1125 | 04/07/2014 | 3:50 PM | 04/07/2014 | 11:34 PM | ||
6195 | 04/07/2014 | 3:50 PM | 04/08/2014 | 12:00 AM | ||
1117 | 04/08/2014 | 5:00 AM | 1:22 PM | 1:59 PM | 04/08/2014 | 3:01 PM |
4911 | 04/08/2014 | 6:46 AM | 11:14 AM | 11:53 AM | 04/08/2014 | 3:52 PM |
1216 | 04/08/2014 | 6:46 AM | 12:38 PM | 1:15 PM | 04/08/2014 | 2:56 PM |
6279 | 04/08/2014 | 6:53 AM | 11:55 AM | 12:33 PM | 04/08/2014 | 3:15 PM |
1256 | 04/08/2014 | 6:54 AM | 12:18 PM | 1:00 PM | 04/08/2014 | 3:27 PM |
1241 | 04/08/2014 | 6:54 AM | 11:44 AM | 12:35 PM | 04/08/2014 | 3:11 PM |
5177 | 04/08/2014 | 6:55 AM | 11:54 AM | 12:37 PM | 04/08/2014 | 3:30 PM |
8070 | 04/08/2014 | 6:55 AM | 12:41 PM | 12:58 PM | 04/08/2014 | 5:16 PM |
1060 | 04/08/2014 | 6:56 AM | 12:59 PM | 1:27 PM | 04/08/2014 | 3:30 PM |
66 | 04/08/2014 | 6:57 AM | 1:03 PM | 1:26 PM | 04/08/2014 | 3:29 PM |
1200 | 04/08/2014 | 7:00 AM | 12:57 PM | 1:34 PM | 04/08/2014 | 3:36 PM |
4126 | 04/08/2014 | 7:01 AM | 1:14 PM | 1:53 PM | 04/08/2014 | 4:11 PM |
9144 | 04/08/2014 | 7:09 AM | 11:41 AM | 12:36 PM | 04/08/2014 | 2:54 PM |
1195 | 04/08/2014 | 7:22 AM | 1:34 PM | 2:17 PM | 04/08/2014 | 3:45 PM |
0223 | 04/08/2014 | 7:26 AM | 12:04 PM | 12:29 PM | 04/08/2014 | 3:17 PM |
1037 | 04/08/2014 | 7:27 AM | 2:19 PM | 2:48 PM | 04/08/2014 | 3:38 PM |
1141 | 04/08/2014 | 7:33 AM | 1:07 PM | 1:40 PM | 04/08/2014 | 3:49 PM |
23 | 04/08/2014 | 7:39 AM | 11:41 AM | 2:30 PM | 04/08/2014 | 4:32 PM |
7043 | 04/08/2014 | 7:59 AM | 04/08/2014 | 5:58 PM | ||
1178 | 04/08/2014 | 8:00 AM | 1:14 PM | 1:52 PM | 04/08/2014 | 4:25 PM |
1225 | 04/08/2014 | 8:04 AM | 12:01 PM | 12:51 PM | 04/08/2014 | 4:23 PM |
6942 | 04/08/2014 | 8:06 AM | 1:01 PM | 1:32 PM | 04/08/2014 | 3:56 PM |
4472 | 04/08/2014 | 8:20 AM | 12:26 PM | 1:05 PM | 04/08/2014 | 4:30 PM |
1266 | 04/08/2014 | 8:25 AM | 1:36 PM | 2:02 PM | 04/08/2014 | 4:50 PM |
1574 | 04/08/2014 | 8:29 AM | 11:44 AM | 12:52 PM | 04/08/2014 | 4:38 PM |
0216 | 04/08/2014 | 8:55 AM | 12:33 PM | 1:06 PM | 04/08/2014 | 5:40 PM |
1261 | 04/08/2014 | 9:08 AM | 12:54 PM | 1:42 PM | 04/08/2014 | 5:15 PM |
1400 | 04/08/2014 | 9:10 AM | 12:59 PM | 1:26 PM | 04/08/2014 | 4:52 PM |
1179 | 04/08/2014 | 9:39 AM | 1:16 PM | 2:22 PM | 04/08/2014 | 6:37 PM |
1174 | 04/08/2014 | 3:02 PM | 8:50 PM | 9:21 PM | 04/09/2014 | 12:14 AM |
4671 | 04/08/2014 | 3:09 PM | 8:24 PM | 9:30 PM | 04/08/2014 | 11:42 PM |
7299 | 04/08/2014 | 3:36 PM | 11:07 PM | 11:42 PM | 04/08/2014 | 11:58 PM |
7355 | 04/08/2014 | 3:37 PM | 11:08 PM | 11:42 PM | 04/08/2014 | 11:58 PM |
1125 | 04/08/2014 | 3:52 PM | 04/08/2014 | 11:30 PM | ||
6195 | 04/08/2014 | 4:33 PM | 04/08/2014 | 11:21 PM | ||
1117 | 04/09/2014 | 5:12 AM | 1:13 PM | 1:47 PM | 04/09/2014 | 2:57 PM |
1256 | 04/09/2014 | 6:52 AM | 12:04 PM | 12:44 PM | 04/09/2014 | 3:30 PM |
4911 | 04/09/2014 | 6:54 AM | 10:48 AM | 11:32 AM | 04/09/2014 | 3:28 PM |
6279 | 04/09/2014 | 6:55 AM | 11:56 AM | 12:38 PM | 04/09/2014 | 1:50 PM |
1060 | 04/09/2014 | 6:57 AM | 1:02 PM | 1:27 PM | 04/09/2014 | 3:24 PM |
66 | 04/09/2014 | 6:57 AM | 1:02 PM | 1:27 PM | 04/09/2014 | 3:24 PM |
1241 | 04/09/2014 | 6:57 AM | 11:38 AM | 12:34 PM | 04/09/2014 | 3:12 PM |
8070 | 04/09/2014 | 6:59 AM | 12:46 PM | 1:26 PM | 04/09/2014 | 4:09 PM |
1141 | 04/09/2014 | 7:05 AM | 1:29 PM | 2:01 PM | 04/09/2014 | 3:29 PM |
4126 | 04/09/2014 | 7:17 AM | 1:27 PM | 1:59 PM | 04/09/2014 | 3:01 PM |
1195 | 04/09/2014 | 7:18 AM | 12:59 PM | 1:57 PM | 04/09/2014 | 3:36 PM |
9144 | 04/09/2014 | 7:18 AM | 11:40 AM | 12:38 PM | 04/09/2014 | 2:59 PM |
1200 | 04/09/2014 | 7:28 AM | 1:30 PM | 2:04 PM | 04/09/2014 | 3:24 PM |
23 | 04/09/2014 | 7:34 AM | 11:40 AM | 12:37 PM | 04/09/2014 | 4:30 PM |
1037 | 04/09/2014 | 7:36 AM | 1:59 PM | 2:49 PM | 04/09/2014 | 3:21 PM |
5177 | 04/09/2014 | 7:37 AM | 12:00 PM | 12:30 PM | 04/09/2014 | 3:21 PM |
0223 | 04/09/2014 | 7:39 AM | 11:38 AM | 12:08 PM | 04/09/2014 | 3:29 PM |
6942 | 04/09/2014 | 8:02 AM | 1:06 PM | 1:35 PM | 04/09/2014 | 4:32 PM |
7043 | 04/09/2014 | 8:06 AM | 2:05 PM | 2:28 PM | 04/09/2014 | 6:06 PM |
1266 | 04/09/2014 | 8:08 AM | 1:02 PM | 1:31 PM | 04/09/2014 | 4:01 PM |
1178 | 04/09/2014 | 8:09 AM | 1:24 PM | 2:02 PM | 04/09/2014 | 3:34 PM |
1216 | 04/09/2014 | 8:10 AM | 11:55 AM | 12:17 PM | 04/09/2014 | 1:48 PM |
1400 | 04/09/2014 | 8:10 AM | 1:05 PM | 1:25 PM | 04/09/2014 | 3:21 PM |
1225 | 04/09/2014 | 8:12 AM | 12:03 PM | 12:53 PM | 04/09/2014 | 4:31 PM |
1574 | 04/09/2014 | 8:27 AM | 12:00 PM | 12:44 PM | 04/09/2014 | 4:34 PM |
0216 | 04/09/2014 | 8:36 AM | 12:31 PM | 1:02 PM | 04/09/2014 | 5:10 PM |
4472 | 04/09/2014 | 8:37 AM | 1:34 PM | 3:12 PM | 04/09/2014 | 6:10 PM |
1261 | 04/09/2014 | 9:12 AM | 1:03 PM | 1:53 PM | 04/09/2014 | 5:14 PM |
1179 | 04/09/2014 | 9:30 AM | 1:43 PM | 1:50 PM | 04/09/2014 | 1:50 PM |
1264 | 04/09/2014 | 11:34 AM | 11:46 AM | 11:47 AM | 04/09/2014 | 11:51 AM |
1179 | 04/09/2014 | 1:50 PM | 1:50 PM | 2:58 PM | 04/09/2014 | 6:21 PM |
1174 | 04/09/2014 | 2:57 PM | 8:54 PM | 9:29 PM | 04/10/2014 | 2:02 AM |
6195 | 04/09/2014 | 3:27 PM | 04/09/2014 | 8:21 PM | ||
7355 | 04/09/2014 | 3:32 PM | 11:11 PM | 11:38 PM | 04/09/2014 | 11:55 PM |
7299 | 04/09/2014 | 3:32 PM | 11:11 PM | 11:38 PM | 04/09/2014 | 11:55 PM |
4671 | 04/09/2014 | 3:49 PM | 7:47 PM | 8:38 PM | 04/09/2014 | 11:29 PM |
1125 | 04/09/2014 | 4:35 PM | 04/09/2014 | 11:32 PM | ||
1117 | 04/10/2014 | 6:42 AM | 1:06 PM | 1:42 PM | 04/10/2014 | 3:01 PM |
1060 | 04/10/2014 | 6:48 AM | 1:03 PM | 1:28 PM | 04/10/2014 | 3:28 PM |
66 | 04/10/2014 | 6:49 AM | 1:05 PM | 1:28 PM | 04/10/2014 | 3:27 PM |
8070 | 04/10/2014 | 6:49 AM | 12:32 PM | 1:11 PM | 04/10/2014 | 3:33 PM |
4911 | 04/10/2014 | 6:50 AM | 10:44 AM | 11:27 AM | 04/10/2014 | 3:37 PM |
6279 | 04/10/2014 | 6:52 AM | 11:56 AM | 12:37 PM | 04/10/2014 | 3:17 PM |
1256 | 04/10/2014 | 6:53 AM | 11:59 AM | 12:40 PM | 04/10/2014 | 3:26 PM |
1216 | 04/10/2014 | 7:00 AM | 11:01 AM | 11:21 AM | 04/10/2014 | 2:57 PM |
1241 | 04/10/2014 | 7:06 AM | 11:28 AM | 12:16 PM | 04/10/2014 | 3:12 PM |
4126 | 04/10/2014 | 7:08 AM | 1:33 PM | 2:08 PM | 04/10/2014 | 3:49 PM |
1200 | 04/10/2014 | 7:09 AM | 12:04 PM | 12:33 PM | 04/10/2014 | 3:50 PM |
1141 | 04/10/2014 | 7:09 AM | 1:50 PM | 2:25 PM | 04/10/2014 | 4:29 PM |
9144 | 04/10/2014 | 7:11 AM | 11:37 AM | 12:31 PM | 04/10/2014 | 2:51 PM |
5177 | 04/10/2014 | 7:14 AM | 11:55 AM | 12:37 PM | 04/10/2014 | 3:18 PM |
1195 | 04/10/2014 | 7:25 AM | 1:04 PM | 1:56 PM | 04/10/2014 | 4:06 PM |
23 | 04/10/2014 | 7:29 AM | 04/10/2014 | 4:28 PM | ||
0223 | 04/10/2014 | 7:34 AM | 11:56 AM | 12:19 PM | 04/10/2014 | 4:03 PM |
1037 | 04/10/2014 | 7:40 AM | 2:47 PM | 3:30 PM | 04/10/2014 | 4:16 PM |
7043 | 04/10/2014 | 7:56 AM | 3:17 PM | 3:40 PM | 04/10/2014 | 6:04 PM |
1178 | 04/10/2014 | 8:05 AM | 04/10/2014 | 1:57 PM | ||
6942 | 04/10/2014 | 8:07 AM | 1:30 PM | 1:59 PM | 04/10/2014 | 4:31 PM |
1225 | 04/10/2014 | 8:08 AM | 12:50 PM | 12:50 PM | 04/10/2014 | 4:25 PM |
1574 | 04/10/2014 | 8:14 AM | 11:50 AM | 12:28 PM | 04/10/2014 | 4:33 PM |
1266 | 04/10/2014 | 8:20 AM | 1:30 PM | 1:52 PM | 04/10/2014 | 4:26 PM |
0216 | 04/10/2014 | 8:38 AM | 12:30 PM | 1:03 PM | 04/10/2014 | 5:22 PM |
1400 | 04/10/2014 | 8:39 AM | 1:13 PM | 1:38 PM | 04/10/2014 | 4:49 PM |
4472 | 04/10/2014 | 8:43 AM | 12:30 PM | 1:30 PM | 04/10/2014 | 4:51 PM |
1261 | 04/10/2014 | 9:20 AM | 1:06 PM | 1:52 PM | 04/10/2014 | 5:21 PM |
1179 | 04/10/2014 | 9:21 AM | 04/10/2014 | 1:02 PM | ||
1179 | 04/10/2014 | 2:07 PM | 04/10/2014 | 5:29 PM | ||
4671 | 04/10/2014 | 3:00 PM | 5:29 PM | 6:18 PM | 04/10/2014 | 11:20 PM |
1174 | 04/10/2014 | 3:01 PM | 8:28 PM | 8:59 PM | 04/10/2014 | 11:16 PM |
1125 | 04/10/2014 | 3:26 PM | 9:03 PM | 9:34 PM | 04/10/2014 | 11:32 PM |
7299 | 04/10/2014 | 3:34 PM | 11:10 PM | 11:34 PM | 04/10/2014 | 11:56 PM |
7355 | 04/10/2014 | 3:34 PM | 11:09 PM | 11:35 PM | 04/10/2014 | 11:57 PM |
6195 | 04/10/2014 | 3:39 PM | 04/10/2014 | 11:21 PM | ||
6279 | 04/11/2014 | 6:50 AM | 04/11/2014 | 11:24 AM | ||
1060 | 04/11/2014 | 6:51 AM | 1:09 PM | 1:34 PM | 04/11/2014 | 3:29 PM |
66 | 04/11/2014 | 6:51 AM | 1:14 PM | 1:34 PM | 04/11/2014 | 3:29 PM |
1256 | 04/11/2014 | 6:51 AM | 12:04 PM | 12:34 PM | 04/11/2014 | 3:18 PM |
5177 | 04/11/2014 | 6:55 AM | 11:59 AM | 12:39 PM | 04/11/2014 | 2:43 PM |
0223 | 04/11/2014 | 6:56 AM | 11:52 AM | 12:20 PM | 04/11/2014 | 3:24 PM |
1241 | 04/11/2014 | 6:57 AM | 04/11/2014 | 12:39 PM | ||
8070 | 04/11/2014 | 6:58 AM | 12:33 PM | 1:07 PM | 04/11/2014 | 3:37 PM |
4126 | 04/11/2014 | 6:59 AM | 1:17 PM | 1:45 PM | 04/11/2014 | 3:48 PM |
9144 | 04/11/2014 | 7:09 AM | 11:31 AM | 12:28 PM | 04/11/2014 | 2:49 PM |
1117 | 04/11/2014 | 7:10 AM | 1:09 PM | 1:49 PM | 04/11/2014 | 3:00 PM |
1200 | 04/11/2014 | 7:10 AM | 12:51 PM | 1:32 PM | 04/11/2014 | 3:00 PM |
1195 | 04/11/2014 | 7:19 AM | 1:25 PM | 2:23 PM | 04/11/2014 | 3:54 PM |
1141 | 04/11/2014 | 7:34 AM | 1:24 PM | 1:55 PM | 04/11/2014 | 3:06 PM |
1037 | 04/11/2014 | 7:40 AM | 2:40 PM | 3:18 PM | 04/11/2014 | 4:23 PM |
6942 | 04/11/2014 | 7:51 AM | 1:03 PM | 1:37 PM | 04/11/2014 | 3:40 PM |
7043 | 04/11/2014 | 7:56 AM | 2:49 PM | 3:16 PM | 04/11/2014 | 6:57 PM |
23 | 04/11/2014 | 7:59 AM | 04/11/2014 | 4:50 PM | ||
1178 | 04/11/2014 | 8:00 AM | 1:01 PM | 1:31 PM | 04/11/2014 | 2:42 PM |
1266 | 04/11/2014 | 8:03 AM | 1:03 PM | 1:33 PM | 04/11/2014 | 3:37 PM |
1225 | 04/11/2014 | 8:08 AM | 04/11/2014 | 11:59 AM | ||
1574 | 04/11/2014 | 8:24 AM | 12:13 PM | 1:06 PM | 04/11/2014 | 4:21 PM |
1216 | 04/11/2014 | 8:28 AM | 1:10 PM | 1:47 PM | 04/11/2014 | 4:38 PM |
4472 | 04/11/2014 | 8:34 AM | 12:57 PM | 1:24 PM | 04/11/2014 | 4:10 PM |
0216 | 04/11/2014 | 8:47 AM | 12:33 PM | 1:01 PM | 04/11/2014 | 5:17 PM |
1261 | 04/11/2014 | 9:08 AM | 1:00 PM | 1:47 PM | 04/11/2014 | 4:28 PM |
1179 | 04/11/2014 | 9:32 AM | 11:32 AM | 12:52 PM | 04/11/2014 | 7:02 PM |
4671 | 04/11/2014 | 2:39 PM | 5:29 PM | 5:55 PM | 04/11/2014 | 10:16 PM |
1174 | 04/11/2014 | 3:01 PM | 8:04 PM | 8:34 PM | 04/11/2014 | 11:00 PM |
7355 | 04/11/2014 | 3:35 PM | 11:14 PM | 11:42 PM | 04/12/2014 | 12:26 AM |
7299 | 04/11/2014 | 3:36 PM | 11:14 PM | 11:42 PM | 04/12/2014 | 12:26 AM |
1125 | 04/11/2014 | 3:36 PM | 9:09 PM | 9:40 PM | 04/11/2014 | 11:31 PM |
<colgroup><col><col span="6"></colgroup><tbody>
</tbody>