Astute Generic Timesheet Importer

External Data Timesheet Importer

The generic timesheet importer imports shift based data where each row of the imported .csv file represents a single shift. The importer can adapt to many external timesheet formats by simply ensuring the header row has valid column names.

IMPORTANT: Before you use the generic timesheet importer, please contact the Astute Payroll Support Team to ensure that your portal has been configured correctly and that the timesheet data can be exported accurately.

We also recommend uploading your timesheet data in a sandbox environment before importing timesheets into your live portal. 

Minimum Requirements

To import a shift from a timesheet the following information must be determined from each row. 

Job	There must be some way to identify the job (Placement) that the row represents. There are many ways to do this.
Shift date	This is the date the shift began. Note that shifts over midnight need the day that the shift started.
Shift start time	The time that the shift started on the above data.
Shift end time	The time that the shift finished. When this is earlier than the start time, we assume the shift went past midnight.

These four pieces of data make up the entire requirement of each row. A shift can also include data such as breaks or projects and activities, but these are not required.

Deriving the Job

To find the job for each row, we start by looking at the provided Job fields. If we cannot find a job using those fields, we will use a combination of the Employee, Workplace and Customer fields, looking for a single job that is active on the shift start date.

Deriving the Shift Date

Where the shift date is not provided in its own field, we look to the shift start field. If that field contains a date, we will use that as the shift date.

Available Fields

In all cases, these fields must be provided with the correct header row, as shown in the first column. This is all in lowercase with an underscore between words. 

Employee Fields

Field	Description	Example
employee_external_timesheet_id	The primary key representing the employee in an external timesheet system.	34283476
employee_rms_id	The primary key representing the employee in an external RMS system.	AA099828236761
employee_astute_id	The primary key for the employee in the Astute system.	323
employee_first_name	The first name of the employee completing the work.	John
employee_last_name	The surname of the employee.	Smith
employee_full_name	The first name of the employee, a single white space, then the surname of the employee.	John Smith John SMITH
employee_sort_name	The surname of the employee, a comma and a single white space, then the first name.	SMITH, John Smith, John

Job Fields

Field	Description	Example
job_external_timesheet_id	The primary key representing the job (Placement) in an external timesheet system.	34283476
job_rms_id	The primary key representing the job (Placement) in an external RMS system.	AA099828236761
job_astute_id	The primary key for the job in the Astute system.	323

Customer Fields

Field	Description	Example
customer_external_timesheet_id	The primary key representing the customer in an external timesheet system.	98374593
customer_rms_id	The primary key representing the customer in an external RMS system.	EEA0928713982
customer_astute_id	The primary key for the customer in the Astute system.	33

Workplace Fields

Field	Description	Example
workplace_external_timesheet_id	The primary key representing the workplace in an external timesheet system.	98374593
workplace_rms_id	The primary key representing the workplace in an external RMS system.	EEA0928713982
workplace_astute_id	The primary key for the workplace in the Astute system.	33

Shift Fields

Field	Description	Example
shift_date	Date the shift started. Many formats can be parsed. In the case of a date such as 1/2/2018, we will assume 1 February rather than 2 January.	2018-02-01 1/2/2018 1 February 2018
shift_start	The time the shift started. This could be a time or a date-and-time. If there is no shift_date field, we will look at this field for a date, otherwise the date is ignored.	09:00 9:00am 1 Feb 2018, 9:00am
shift_finish	The time the shift finished. This could be a time or a date-and-time. If there is no date and the time is earlier than the start, we assume work over midnight.	17:00 5:00pm 1 Feb 2018, 5:00pm

Additional Data Fields

Shift Fields

Field	Description	Example
break	Any time between the shift start and shift finish that was taken as a break. This value should be provided in minutes. If not provided, the employee is assumed to have worked the whole shift.	30

If your portal is configured to allow multiple breaks to be entered for each time block or shift, these breaks can be entered using the following column headings:

• break1_start
• break1_finish
• break2_start
• break2_finish
• break3_start
• break3_finish

Upon import, the system will validate that the breaks do not overlap and fall within the start and finish time of the shift. 

You'll also receive a validation error if both types of break have been entered in a single line (ie a minutes break and a start/finish break), or if a break is entered for an employee timesheet where breaks have not been configured. 

Project Fields

If the employee worked on a project that has been configured in Astute, these fields will be used to identify the project and activity. These fields apply to the whole shift.

Field	Description	Example
project_astute_id	The primary key for the project in the Astute system.	12
activity_astute_id	The primary key for the activity in the Astute system.	23
project_name	The name of the project in the Astute system. Matching is not case sensitive but the same spaces and any punctuation will need to match.	House remodelling
activity_name	The name of the activity in the Astute system. Matching is not case sensitive but the same spaces and any punctuation will need to match.	Plastering

Note that there will soon be additional fields added to the above to identify the project and activity in external systems: project_external_timesheet_id and activity_external_timesheet_id.

Defaulting Data

Importers created in Astute can default the Customer and Workplace to help identify the correct job for each row. In cases where more than one job matches, we will not import any data. These defaults help reduce the chance of multiple jobs matching.

Sample Import Files

Three examples of import .csvs have been attached to this article. You can use these as a basis for your own import.

• The file Astute Generic Timesheet Importer - Sample requires the break taken to be entered in the relevant column, entered in minutes.
• The file Astute Generic Timesheet Importer (multiple breaks) - Sample accommodates the entry of multiple breaks with a start and finish time.
• The file Astute Generic Timesheet Importer (no break) - Sample does not require the break taken to be entered.

If you use the templates, please ensure that any sample data is removed from your version before you upload anything into your portal.

Prior to uploading for the first time, or to request a sandbox, please contact our Support Team.