Employee Import Utility Specification

Introduction

The Employee Import Utility has been provided to allow Employee Information to be added or maintained within IMS Payroll Partner in larger volumes without using the standard application interface. Employee data is imported from a data file and applied programmatically to the payroll company.

The Employee Import option is found on the IMS Payroll Partner Tools > Files Import menu and is only enabled when the payroll period is closed. Access to the import option is restricted if User Security is enabled and the user does not have access to the Payroll Processing section of the program and Employee Maintenance ability.

Detailed Field Specification and Field Description tables accompany this document and must be read in conjunction with it.

This document outlines the requirements for data file preparation and the processing performed as data is imported.

File Creation

File Format

The Employee Import Utility will only read information from data files provided in a CSV (comma-separated-values) format. The format may be either in a quotation delimited format or required quotes may be inserted during the import.

Data files can be generated programmatically, or manually from a spreadsheet or text editing application.

Data Types

String Fields

String (alphanumeric text) field values may or may not be delimited by double quotes depending on your preference; however this option must be standard throughout the file, e.g.

“Accounting Department” or “Oughten, Peter James” or “Manager” with quotes

or

Accounting Department or Oughten, Peter James or Manager without quotes

Whether you are generating data files programmatically or manually, string values and Column Headers may either be enclosed in double-quotes within the data file or not but this must be standard throughout the data file. If not, then the ‘Automatically Embed Double Quotes’ indicator on the import facility must be selected.

If you want to encase string fields in double quotes when using a spreadsheet application to create a data file you must place double-quotes around your text values within the cell. Saving a spreadsheet in Microsoft Excel using the CSV format (for example) does not enclose string values in double quotes. Therefore it is recommended that double quotes are automatically embedded during the import.

Numeric Fields (Integer & Floating Point Decimal)

Numeric (integer and decimal) field values must not contain any formatting characters other than a leading sign (-) when applicable, and for decimal numbers a decimal point (.) e.g.

12.45, -15.1234, 42, -5

Decimal values greater than zero (0) but less than one (1) must contain a leading zero before the decimal point e.g.

0.15

Decimal values less than zero (0) but greater than negative one (-1) must also contain a leading zero before the decimal point e.g.

-0.15

It is not necessary to enclose numeric values in double-quotes.

Date Fields

Date fields should appear in the data file in the format “dd/mm/yyyy”. The slash character is the only position delimiter that will be accepted.

It is not necessary to enclose date values in double-quotes.

Column Specification

There are two ways to specify the columns present in a data file:

  • The “Untitled Columns” method

  • The “Titled Columns” method

Untitled Columns

If you choose to use the “Untitled Columns” method, all fields (as listed in the File Specification document) must be present in the data file in the prescribed order. A Column Header record (see below) is not required in this case and all string fields must be encased in double quotes.

Titled Columns

If you choose the “Titled Columns” method, only the “Required” fields and user-selected optional fields need to be included in the data file. A Column Header record must be included with the column headers with or without double quotes.

Column Header Record

The Column Header record must be the first record in the file and must list the names of the fields for which data is being supplied. The column names listed in the Column Header record can be any combination of those columns listed in the File Specification document within the stated rules for file construction. Column repetition is not acceptable.

An example of a Column Header record is given below:

Employee ID,Employee Number,First Names,Surname,Birth Date,Salary,AL Next Entitlement,AL Due Dat

(without quotes)

“Employee ID”,”Employee Number”,”First Names”,”Surname”,”Birth Date”,”Salary”,”AL Next Entitlement”,”AL Due Date”

(with quotes)

The order and number of fields contained in each data record must match the order, number and data types of the fields specified in the Column Header record. The last column per detail line must contain a E character to indicate the end of detail columns for an employee. This column must be populated whether Untitled or Titled columns (Title for this column is END) are used or not.

Data Validation

Overview

The Employee Import Utility will perform data validation in two stages. The first stage (the File Verification stage) will analyse the structure & content of the file. The second stage (performed during the Data Import process) will apply the same validation that is encountered when creating or maintaining employee’s via the IMS Payroll Partner Employee Information interface.

The File Verification stage will analyse the format and content of the data file. To this end the process will:

  • Determine whether a Column Header record is required, and if so, ensure that the named columns are recognisable.

  • Determine whether each data record contains the correct number of fields.

  • Determine whether string fields (and header columns) contain double quotes depending on the setting of the ‘Automatically Embed Double Quotes’ indicator on the Import options.

  • Determine whether each data field contains an acceptable data type and value.

  • Determine the validity of some basic data combinations.

Entry of certain values via the payroll Employee Information interface initiate dialogs that require a user response. When applying values to fields that require interaction of this nature, the import process will perform the equivalent of a user response as specified in an ‘Action Value’ field in the data record. Action Value fields are identified by an ‘A’ in the Field Type column of the File Specification document (valid values are D - Data and A - Action).

For example, when applying a Finish Date to an employee the field Finish Action will determine whether the employee will be ‘Finished’ or ‘Terminated’.

Validation Rules

Employee Creation

When creating a new employee via the Employee Import Utility:

  • The Employee ID field in the data record must be blank (whether you are using the Titled or Untitled Column methods).

  • The Employee Number field in the data record must be blank, or contain a previously unassigned, valid Employee Number.

  • The combination of values contained in the Surname, First Names must be unique and unmatchable against the payroll company in question.

  • Fields listed in the “Required New Employee” column of the File Specification document must be present in the file and contain valid values (see 5.).

  • The requirements prescribed in the “Max Width”, “Min Value”, “Max Value”, “Values – New Employee” and “Notes” columns must be observed and adhered to when applying values to the data record.

It is important to note that many fields in the File Specification are marked as optional due to default values being applied by IMS Payroll Partner when a new employee record is created and populated. An example of this default action would be the assignment of the Annual Leave Type:

When you assign the value ‘F’ (Permanent Employment with Set Hours of work) to the Annual Leave Type, the system will, by default:

  • Set the “AL Full Entitlement” and “Next Entitlement” values to 20.

  • Set the “Annual Leave Units” value to ‘D’ (Days) and the “AL Due Date” to the next (future) anniversary of the employee Start Date.

  • Set the “AL % of Gross” to 8 % and the “AL For” value to 52 (weeks).

  • Set the “Sick Leave Units” value to ‘D’ (Days) and the “SL Full Entitlement” and “SL Next Entitlement” values to 5.

  • Set the “SL Due Date” to [the employee Start Date + 6 months] or the next (future) anniversary of [the employee Start Date + 6 months].

  • Set the “SL Months” to 12 and the “SL Maximum” to 5.

In spite of the default actions (as in the example above), you can apply values to the optional fields and the system will interpret and apply them as required.

Employee Maintenance

There are three methods of identifying an existing employee within the data file:

  • By internal Employee ID

  • By user-defined Employee Number

  • By Surname and First Names

The order of the methods (as displayed above) also implies the sequence in which the employee identification process will attempt to match data records with employees in a specific payroll company.

1. Employee ID

This method uses the identifier assigned by IMS Payroll Partner (when an employee is created) to match a data record to an employee. The Employee ID for a particular employee is specific to a payroll company and may only be the same in another payroll company by coincidence.

The Employee IDs of existing employees in a payroll company can be found on the Find Record window (click the button when in the Employee Information section of IMS Payroll Partner).

Alternatively you can use the “Files Export – Nimbus” option on the Tools menu that will generate three text files (within the Cloud environment you will be able to indicate where these files are to be generated whereas the destination will be in the payroll company directory within the Desktop environment). One of the files created is entitled “Employee.txt” and contains the Employee ID, Surname and First Names of each employee in the company (as a CSV file).

If you provide an Employee ID, the verification process will attempt to match it to an existing employee in the payroll company. If a match cannot be made, an error will be generated as the assignment of Employee ID’s is entirely controlled by the database manager employed by IMS Payroll Partner. If a match is made, any other data values held in the data record will be applied to the employee record with the matching Employee ID.

2. Employee Number

This method employs the user-defined Employee Number (a valid number between 1 and 999999) to match employees. If the Employee Number is specified with no Employee ID, and it cannot be matched to an existing employee, a new employee will be created. If a match is made, any other data values held in the data record will be applied to the employee record with the matching Employee Number.

The Employee Numbers of existing employees are listed in the Employee Listing report generated from the IMS Payroll Partner Sundry Reports screen.

3. Surname, First Names

If no match is made based on options 1 and 2, or if the Employee ID and Employee Number fields are not included in the data file, then the matching process will compare the capitalised Surname and First Names held in the data file against the capitalised Surname and First Names of the existing employees in the payroll company.

If a match is made, any other data values held in the data record will be applied to the employee record with the matching Surname, First Names.

Employee Number Allocation

It is recommended that Employee Numbers of the same length be used for all employees. This recommendation is made to ensure optimum lookup speed when using the Employee Number Lookup Box in the payroll application. For example, if you expect to have between 100 and 900 employees on your payroll, start allocating your Employee Numbers at 100 so that every employee has a number that is three digits long.

Applying Finish Dates and Due Back Dates

If the Finish Date field included in any data record contains a non-blank value, the Finish Action column must be present in the file and must be populated for the record in question.

If the employee already has a Finish Date recorded and a different value is present in the data file, the field will be updated and the Finish Action will be ignored.