When you attempt to create a timesheet manual item import batch the system performs validation to ensure that the Timesheet Import Interpreter Review module can read the contents of the import file.
...
the first row must consist of column headers and each column header must be unique
individual fields within the file must be separated by a delimiter character, such as a comma, tab, pipe or some other type of character that must be specified at the time of import.
no grouping rows, such as summed totals, can exist in the file
each row must contain data that can be imported independently of any other row (e.g. a single row cannot contain both an attendance item and an additional item)
the first row that contains no data signals the end of the file (for XLS or XLSX files only) all dates must be in the format dd/mm/yyyy
all times must be in 24 hour format hh:mm.
Applying Formatting to Import Files
Timesheet data Data that is output by a third-party application may not be in a format that is readable by the Timesheet Interpreter Manual Item Import module. Therefore, an import file will usually require some level of formatting before it can be uploaded into Timesheet Import. The formatting can be applied manually or via an automated macro script.
...
The example below shows an example of an incorrectly formatted import file:
Contract | PO Number | Pay Item | Bill Item | Contractor Name | Service | Week End Date | WorkDay | Pay Qty | Unit | Pay RateNet Value | Bill Qty | Bill Rate |
123015011 | 78932 | 50 | 60 | Bloggs, Joe | ||||||||
3000456 | 09/01/2022 | 03/01/20112022 | 8 | HR | 88.25 | 7068 | 105.90 | |||||
300045602 | 09/01/2022 | 04/01/20112022 | 8 | HR | 88.25 | 7068 | 105.90 | |||||
3000456 | 0309/01/2022 | 05/01/20112022 | 8 | HR | 88.25 | 8706 | 105.90 | |||||
TOTAL: | 24 | 211824 | ||||||||||
123005111 | 84527 | 30 | 25 | Smith, John | ||||||||
3000458 | 09/01/2022 | 03/01/20112022 | 10 | HR | 90.25 | 10 | 902112.582 | |||||
3000458 | 0209/01/2022 | 04/01/20112022 | 10 | HR | 90.25 | 10 | 902112.582 | |||||
TOTAL: | 20 | 201805 | 225.64 |
In the example above, the import data is not formatted correctly because the data includes grouping rows with summed totals. The next example shows the same data but this time formatted correctly to remove the grouping and grouped totals:
Job Order
Payee Name
Date
Attendance Name
Start Time 1
End Time 1
Start Time 2
End Time 2
Break Start 1
Break End 1
Project Code 1
Project Code 2
123456789
Bloggs, Joe
Contract | PO Number | Pay Item | Bill Item | Contractor Name | Service | Week End Date | WorkDay | Qty | Unit | RateNet | ValueBill Qty | Bill Rate |
123015011 | 78932 | 50 | 60 | Bloggs, Joe | 3000456 | 09/01/2022 | 03/01/20112022 | 8 | HR | 88.25 | 8706 | 105.90 |
123015011 | 78932 | 50 | 60 | Bloggs, Joe | 300045602 | 09/01/2022 | 04/01/20112022 | 8 | HR | 88.25 | 7068 | 105.90 |
123015011 | 78932 | 50 | 60 | Bloggs, Joe | 300045603 | 09/01/2022 | 05/01/20112022 | 8 | HR | 88.25 | 7068 | 105.90 |
123015111 | 84527 | 30 | 25 | Smith, John | 3000458 | 09/01/2022 | 03/01/20112022 | 10 | HR | 90.25902 | 10 | 112.582 |
123015111 | 84527 | 30 | 25 | Smith, John | 3000458 | 0209/01/2022 | 04/01/20112022 | 10 | HR | 90.25902.5 |
Shifts with Multiple Project Codes & Breaks
A shift on a timesheet in Time and Attendance can be allocated against one or more project codes and can include one or more breaks. To support this, Timesheet Import provides the ability to define up to ten individual work start and end times, break start and end times and project codes that each line within the import file can be allocated to. For example, consider the shift information in the table below.
10 |
Day Shift
09:00
12:30
13:00
17:00
12:30
13:00
ABCD
ABCD
In the example above there is one shift from 12:30 until 13:00. Therefore, there are two sets of start and end times (Start Time 1, End Time 1, Start Time 2, End Time 2) to represent time worked before and after the break respectively. In this case the total time worked during the shift is attributable to project code ABCD. Note that because there are two sets of start and end times the project code is defined twice (Project Code 1, Project Code 2), with each instance being applicable to the time prior the break and after the break respectively.
However, if the last hour of the shift was attributable to project code XYZ1 instead of ABCD, the row of the import file would be as shown in the table below.
...
Job Order
...
Payee Name
...
Date
...
Attendance Name
...
Start Time 1
...
End Time 1
...
Start Time 2
...
End Time 2
...
Start Time 3
...
End Time 3
...
Break Start 1
...
Break End 1
...
Project Code 1
...
Project Code 2
...
123456789
...
Bloggs, Joe
...
01/10/2015
...
Day Shift
...
09:00
...
12:30
...
13:00
...
16:00
...
16:00
...
17:00
...
13:30
...
13:00
...
ABCD
...
ABCD
If there was an additional 30 minute break at 15:30, the row of the import file would be as shown in the table below.
Job Order
Payee Name
Date
Attendance Name
Start Time 1
End Time 1
Start Time 2
End Time 2
Start Time 3
End Time 3
Break Start 1
Break End 1
Break Start 2
Break End 2
123456789
Bloggs, Joe
01/10/2015
Day Shift
09:00
12:30
13:00
13:30
16:00
17:00
13:30
13:00
15:30
16:00
112.82 |