Import File Format Validation

When you attempt to create a timesheet import batch the system performs validation to ensure that the Timesheet Import module can read the contents of the import file.

The following conditions must apply to 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 that is output by a third-party application may not be in a format that is readable by the Timesheet 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.

Import File Examples

The example below shows an example of an incorrectly formatted import file:

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:

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.

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.

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.