This article explains the validation rules that apply when you create a timesheet import batch, how to format data from third-party systems so it can be read correctly, and how to structure rows for grouped data and complex shifts.
📌 Note: The Timesheet Import module validates the import file structure before it creates a batch. If the file does not meet the rules below, you cannot queue it for processing.
Validate and Format Timesheet Import Files
When you create a timesheet import batch, the system validates the import file to make sure the Timesheet Import module can read and interpret all data correctly.
Required file structure
Make sure every import file meets these rules:
The first row must contain column headers, and each header must be unique.
Fields must be separated by a single delimiter character (for example, comma, tab, or pipe) that you specify at the time of import.
The file must not contain grouping rows, such as rows that show summed totals.
Each row must contain data that can be imported independently of any other row (for example, a single row cannot contain both an attendance item and an additional item).
For XLS/XLSX files, the first completely blank row signals the end of the file. No data after that row is imported.
All dates must use the format
dd/mm/yyyy.All times must use 24-hour format
hh:mm.
Apply Formatting to Third-Party Export Files
Timesheet data exported from third-party applications is often not in a format that Timesheet Import can read directly. You usually need to reformat the data before you upload it.
You can apply this formatting:
Manually in a spreadsheet editor such as Microsoft Excel.
By using a macro script to automate the formatting for repeat imports.
Macro scripts can be:
Recorded using Record Macro in Microsoft Excel for simple transformations.
Written manually for more complex formatting rules.
Import File Examples
Incorrect example with grouping rows
The following example shows an incorrectly formatted import file. It includes blank “grouping” rows and TOTAL rows with summed values, which are not allowed.
Contract | PO Number | Item | Contractor Name | Service | WorkDay | Qty | Unit | Rate | Net Value |
123015011 | 78932 | 50 | Bloggs, Joe |
|
|
|
|
|
|
|
|
|
| 3000456 | 01/01/2011 | 8 | HR | 88.25 | 706 |
|
|
|
| 3000456 | 02/01/2011 | 8 | HR | 88.25 | 706 |
|
|
|
| 3000456 | 03/01/2011 | 8 | HR | 88.25 | 706 |
|
|
|
|
| TOTAL: | 24 |
|
| 2118 |
123005111 | 84527 | 30 | Smith, John |
|
|
|
|
|
|
|
|
|
| 3000458 | 01/01/2011 | 10 | HR | 90.25 | 902.5 |
|
|
|
| 3000458 | 02/01/2011 | 10 | HR | 90.25 | 902.5 |
|
|
|
|
| TOTAL: | 20 |
|
| 1805 |
This structure fails validation because:
Some rows exist only to group data (for example, the header row for each contractor).
Rows with TOTAL values do not represent individual timesheet lines.
Corrected example without grouping rows
The next example shows the same data formatted correctly, with all grouping and total rows removed. Each row can now be imported independently.
Contract | PO Number | Item | Contractor Name | Service | WorkDay | Qty | Unit | Rate | Net Value |
123015011 | 78932 | 50 | Bloggs, Joe | 3000456 | 01/01/2011 | 8 | HR | 88.25 | 706 |
123015011 | 78932 | 50 | Bloggs, Joe | 3000456 | 02/01/2011 | 8 | HR | 88.25 | 706 |
123015011 | 78932 | 50 | Bloggs, Joe | 3000456 | 03/01/2011 | 8 | HR | 88.25 | 706 |
123015111 | 84527 | 30 | Smith, John | 3000458 | 01/01/2011 | 10 | HR | 90.25 | 902.5 |
123015111 | 84527 | 30 | Smith, John | 3000458 | 02/01/2011 | 10 | HR | 90.25 | 902.5 |
In this format:
There are no blank grouping rows.
There are no TOTAL rows.
Each row describes a single timesheet item that can be imported on its own.
Shifts with Multiple Project Codes and Breaks
A shift in Time and Attendance can:
Be allocated to one or more project codes, and
Include one or more breaks.
To support this, Timesheet Import allows up to ten sets of work start and end times, break start and end times, and project codes on a single row.
Single shift with one project code and one break
The example below shows a single shift where all time is allocated to the same project code, with one unpaid break.
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 | 01/10/2015 | Day Shift | 09:00 | 12:30 | 13:00 | 17:00 | 12:30 | 13:00 | ABCD | ABCD |
Here:
Start Time 1 and End Time 1 represent work from 09:00 to 12:30.
Break Start 1 and Break End 1 represent a break from 12:30 to 13:00.
Start Time 2 and End Time 2 represent work from 13:00 to 17:00.
The same project code (ABCD) is recorded in both Project Code 1 and Project Code 2, so all hours are allocated to that project.
Shift where the last hour uses a different project code
If the last hour of the shift is allocated to a different project code, you add another set of start and end times.
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 | 12:30 | 13:00 | ABCD | XYZ1 |
In this case:
The time from 09:00 to 16:00 is allocated to project code ABCD.
The time from 16:00 to 17:00 is allocated to project code XYZ1.
Shift with an additional break
If there is an additional 30-minute break at 15:30, you add a second set of break start and end times (Break Start 2 and Break End 2) to the same row.
For example:
Break Start 1 / Break End 1 = 12:30–13:00 (lunch break).
Break Start 2 / Break End 2 = 15:30–16:00 (afternoon break).
The shift still uses multiple start and end times to represent periods worked before, between, and after the breaks, and you continue to record project codes in the same pattern.
📌 Note: You can continue this pattern to define up to ten sets of work start/end times and breaks on a single row, as long as each column maps to a defined field in Timesheet Import.