Open navigation

Import Time Data

**Released in Avanti 10**

You can easily create Time Data Entries in Avanti from third-party information using Import Time Data. These entries carry into Payroll Data Entry and eventually into Payroll.

Overtime rules, premium rules, and break rules apply to any unapproved time you import. These rules won’t run on any approved time imported.

If Import Time Data isn’t on your System Menu yet, go to How to Add New Items to Your Avanti Menus for more information.

Important Information
This is only available for Avanti SaaS Clients.

Import File Overview

  • You can import a .CSV file, a CAODC XML file, or an Excel file with an extension .XLS or .XLSX. If you’re importing an excel file, only the first sheet imports.

    If you’re importing a CAODC XML file and need assistance, we’re here to help. Contact Client Care, Care@Avanti.ca, for additional assistance.

  • Avanti imports every row in the import file. If there’s a header row describing the columns in the file, please remove it before importing.

  • Employee Number, Entry Date, Earning Code, Units, and Units of Measure are required to import. Depending on your Time & Attendance setup, some additional information may be required.

    For additional information about creating the file, go to the How to Create the Import File section at the bottom of this article.

  • You can find a sample Excel file attached at the bottom of this article.

  • The column order in the import file must match the format outlined below. If you’re not importing information for a column, leave the column blank.

 Column

Description

Contents and Format

A

Employee Number

The number of an active employee.

B

Entry Date

If importing an Excel file, format the cell using a date format. If you’re importing a .CSV file, the allowed formats are in the How to Create the Import File section at the bottom of this article.

C

Earning Code

The earning code is a three-digit code, from 001-499.

D

Position Code

If this isn’t included in the file, the position defaults to the employee’s primary position.

E

Units

The number of units and the units of measure
Units of measure can be H, D, W, M, Y, P, U or A.

F

Units of Measure

G

Rate

The rate, in dollars, for the time worked. If blank, then the time is paid at the rate in Avanti.

H

GL Type

If blank, defaults to the GL selected on the employee’s earning code. Valid GL Types: B,M,A

  • B – The employee’s GL Option from their Employment Data.

  • M – The earning code GL Option from the employee’s earning.

  • A – The account from column I on the import.

I

GL Account

The full GL account for the time worked. A valid Required when A is entered in column H.

J

GL Benefit Group

A valid ID for the GL Benefit Group. Required when A is entered in column H.

K

Attendance Code

The ID of a valid attendance code.

L

Comment

Any additional information to include on the time data entry.

M

Task ID

The ID of a valid Task ID for the worked time.

N

Shift ID

The ID of the shift with the correct breaks and premiums for the time worked.

O

Vacation Override Code

The ID of a valid Vacation Group to override the group selected on the employee’s profile.

P

WCB Override Code

The ID of a valid WCB Group to override the group selected on the employee’s profile.

Q

Union Override Code

The ID of a valid Union to override the code selected on the employee’s profile.

R

Province of Employment Override

A two-letter province abbreviation to override the province of employment selected on the employee’s profile.

S

Reference Number

A unique identifier for the Time Data Entry ensuring the entry isn’t imported twice by accident.

T

Location Code

A valid location code for the worked time.

U

Approved

No or blank – entry is not approved.
Yes – Entry is approved.

For additional information about creating the file, go to the How to Create the Import File section at the bottom of this article.

How to Import Time Data Entries

Once you’ve updated the format of the file, you can import into Time Data.

Step 1: Open Import Time Data in the Avanti Desktop Application.
By default, this can be found in Time & Attendance >> Time Data.

Additional Information
Can’t find Import Time Data in your Avanti Desktop? Go to How to Add New Items to Your Avanti Menus for more information.

Step 2: Select the type of file you’re importing in Import Type.

Step 3: Select the arrow beside Import File, and locate the file.

Step 4: Select the format of the dates in the file if you’re importing a .csv from:

  • MDY - Select if the dates in the file are Month, Day, then Year.

  • YMD - Select if the dates in the file are Year, Month, then Day.

  • DMY - Select if the dates in the file are Day, Month, then Year.

Additional Information
This is only required if you’re importing a .CSV file.

Step 5: Determine what to do if a duplicate reference number is found.

  • Replace the unapproved time data entry in Avanti by selecting Replace Duplicate Reference Number when importing the file.

  • Skip importing the time data entry into Avanti by deselecting Replace Duplicate Reference Number when importing the file.

Additional Information
This is only required if your import file includes reference numbers in column S.

Step 6: Select OK to import the file.

Important Information
The import begins as soon as you select OK.

How to Create the Import File

To create the import file, you can copy each column into a new excel file. The columns of the file must be in the order below. If you’re not importing information in a column, leave the column blank in the excel file.

Employee Number - Required
The first column is the employee number of an active employee in Avanti. If you allow alphanumeric employee numbers, make sure you include leading zeros.

If you plan on saving this as an Excel file, you’ll need to change the column format to a text field; otherwise, Excel removes the leading zeros when the file is saved.

Entry Date - Required
If you’re importing an Excel file, the cell needs to be formatted as a Date.

If you’re importing a .CSV file, all dates must be in an accepted format. Don’t forget to select the correct date format in Avanti when you import the .CSV file.

YY/MM/DD

YY-MM-DD

YY.MM.DD

MM/DD/YY

MM-DD-YY

MM.DD.YY

DD/MM/YY

DD-MM-YY

DD.MM.YY

YYYY/MM/DD

YYYY-MM-DD

YYYY.MM.DD

MM/DD/YYYY

MM-DD-YYYY

MM.DD.YYYY

DD/MM/YYYY

DD-MM-YYYY

DD.MM.YYYY

Earning Code - Required
Enter a valid earning code between 001 and 499 in column C. The earning code must be active on the Employee’s Profile.

Position Code - Optional
If the employee didn’t work their primary position, enter the ID for the position worked in Column D. If the column is blank, Avanti assumes the employee worked their primary position.

The position must be on the employee’s profile if Only allow defined Employee Positions is selected on the Time Data Entry tab in Canadian Payroll >> Installation and Maintenance >> Payroll Parameters.

Units, UOM - Required
The Units and Unit Code is required in columns E and F.

The unit code is a single letter abbreviation of H, D, W, M, Y, P, U, or A.

  • H is for hours.

  • D is for days.

  • W is for weeks.

  • M is for months.

  • Y is for years.

  • P is for periods.

  • U is for units.

  • A is for an amount.

For example, if the employee worked for 7.5 hours, then column E is 7.5, and column F is H.

Rate - Optional
If the time worked should be paid at a different rate, you can enter the rate in column G. If this is left blank, the employee is paid at Avanti’s rate.

GL Type
Enter the GL for the entry in column H. If the column’s blank, the GL defaults to the selection on the employee’s earning code. Valid values in this column are B, M or A.

  • B – The employee’s GL Option from their Employment Data.

  • M – The earning code GL Option from employee’s earning.

  • A – The account from column I on the import.

GL Account, GL Benefit Group
These are required when A is entered for GL Type in column H.

Ensure a valid, complete GL account is entered in column I, and a valid GL Benefit Group is included in column J.

Attendance Code - Optional
You can enter the ID a valid attendance code in column K.

Comment - Optional
Column L can contain any additional comments for the time worked.

Task ID - Optional
You can enter a valid Task ID in column M.

This may import into a field with a different label than Task ID. It’ll import into the field specified in Task ID on the Add’l Field Names tab in Canadian Payroll >> Installation and Maintenance >> Payroll Parameters.

Shift ID - Optional
You can include a Shift ID in column N of the import file. The shift must be valid for the employee’s workgroup and the day of the week.

If a shift isn’t assigned to the time data, then premium rules, minimum/maximum hours, and break rules won’t apply to the time worked unless the shift is added before the time data entry is approved.

Vacation, WCB, Union, and Province of Employment Override - Optional
The next four columns, column O through R on the excel file, are optional overrides for the employee vacation group, WCB group, union and province of employment, respectively.

For the Vacation Code, WCB group and Union Code, the column should be the code’s ID. For the Province of Employment, the import file should contain the two-letter abbreviation for the province.

If these are blank, the employee’s profile information determines the vacation group, WCB group, union code and province of employment.

Reference Number - Optional
The reference number ensures an entry isn't accidentally imported as two entries. If there’s a unique number for every entry in the import file, you can include it in column S on the excel file.

When there’s a duplicate number in the import, you can choose to either:

  • Replace the unapproved time data entry in Avanti by selecting Replace Duplicate Reference Number when importing the file.

  • Skip importing the time data entry into Avanti by deselecting Replace Duplicate Reference Number when importing the file.

Location Code - Optional
In Column T, you can enter a location code ID for the time worked.

Approved - Optional
The final column, column U, determines whether the time data entry is approved.

  • If the column has Yes, approved time imports.

  • If the column is blank or No, the time imported is unapproved.

Overtime rules, premium rules, and break rules only apply to unapproved time imported. These rules won’t run on any approved time imported.

M
Marlo is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.