Applies to versions: 2.2, 2.3, 2.4, 2.5, 3.0, 3.1, 3.2
Importing/Exporting Benefits
TimeIPS can export benefit data to a spreadsheet file. The data can then optionally be edited and imported back into the system, using the Employee Import/Export optional module.
Using the left pane Main Menu » Administration, click on: Benefits » Import/Export
Exporting
- From the Export Targets drop down list, select the Employee or group to be included in the export.
- Select either a Horizontal or Vertical Format from the Export drop down list, then click the Export Data button to export the current benefits information for the employee(s) selected.
- You may generate a Template file or use the Benefits Import File Layout to create your own file. The following must be met:
- The file must be a comma separated value (CSV) file. (i.e. Excel worksheet, OpenOffice Spreadsheet, or ASCII Comma Delimited)
- The field names (column names) must be exact. The first column must be "Employee". The second column must be "Benefit Type". Column names are not case sensitive and ignore extra spaces.
The report contains each employee's name, start date, tenure, and hours worked, as well as the available, earned, used, and lost hours for each benefit type. This file may be modified and imported using the "Benefits Import/Update" section of the page.
TIP: To edit a .csv file, we recommend a spreadsheet application such as Excel™ or
OpenOffice.org Calc.
Importing
You may use an exported Benefits file to enter your Benefits information or use the Benefits Import File Layout to create your own file. The following must be met:
The file must be a comma separated value (CSV) file. (i.e. Excel worksheet, OpenOffice Spreadsheet, or ASCII Comma Delimited)
The field names (column names) must be exact. You may delete unneeded columns. The first column must be "Employee". If you have multiple employees with the same name, use a "Badge Number" column as the unique identifier for employees on the import. (If present, the "Badge Number" field will be given priority over the "Employee" field to identify the employee on the row.) The remaining columns may be in any order. Column names are not case sensitive and ignore extra spaces.
To identify an employee on import, the system looks for three fields in order:
- "SSN" field. If present, the SSN will be given first priority in identifying the employee for the benefit import. This is not on the export for security reasons, but can be added to the import to ensure the correct identification of an employee when multiple employees have identical names.
- "Badge Number" field. If present, the Badge Number will be given the next priority in identifying the employee for the import.
- "Employee" field. If SSN and Badge Number are blank or match no employees, then the name will be used to try to identify the employee for the import. TimeIPS will attempt to match the Employee's last name and first name exactly and middle name if specified.
If the employee cannot be identified, an error will be shown.
IMPORTANT: Even if identifying employees by SSN or Badge Number, the "Employee" column must be the first column in order for the file to be recognized as a valid benefits import (or IAPI transaction).
All the other columns on the export, such as worked hours, total used, lost, etc. are optional, only informational and are not used by TimeIPS when importing.
When importing benefit levels, only the "Available" columns (for example, "Sick Available" and "Vacation Available") are used. For each "Available" column, a corresponding "Effective" column must also be filled with a date (for example 10/1/2009) that tells TimeIPS when the new benefit total becomes effective. The Available and Effective pair is equivalent to a Benefit Reset option, meaning that you have created a set-point where accruals will begin at the level specified on the date effective and calculate forward from that point.
On systems with IPSBEN, additional benefit types will be exported/imported in sets of columns similar to Sick and Vacation
Import/Update
- Select either Horizontal Format, Vertical Format, or Benefit Usage from the Benefits Import/Update File drop down list.
- Click the Browse button and select the .csv file with new or updated benefits data.
- If you have created a Preprocessor for Benefits and would like to use it, select it from the drop down list. (See the Import Preprocessors article for more information on creating a preprocessor.)
- Click the Import/Update Benefits button.
- Excel™ Compatibility: If you are using Microsoft Excel™ to modify the import file and you receive errors about "incorrect number of columns," enable this option by checking the box.
NOTE: The Import/Update Feature will log all changes to benefit levels as if the changes were made on the benefits page by the administrator running the import.
Information