Import Time

Privilege Needed: Administrator

This feature allows Administrators to import 'Time' information into the Unanet system.

Within one time import file, an Administrator can import time data for one to many people for different time periods.

The Time Import process accepts time data for a user for any time period for which:

No timesheet already exists for the user. In this case a new timesheet will be created.
A timesheet exists for the user and it is in one of the following statuses: INUSE, SUBMITTED, COMPLETED, DISAPPROVED, APPROVING. In this case the timesheet will be placed in INUSE status.

Should the timesheet already have an entry for a given date and a given project/project type/task/pay code in the import file, the Administrator will be given an option to append or replace the existing entry with the imported time. An append option will be used by default.

The Administrator is given an option to:

Submit the timesheet , thus entering the standard Unanet approval process
Force the timesheet into the COMPLETED status bypassing the approval process
Force the timesheet into the LOCKED status disabling any further manipulation of the timesheet

The table below shows all the fields that can be imported for time.

	Field Name	Required/Description
1	Username	ALWAYS REQUIRED. Valid Unanet username that uniquely identifies a user. If the username you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The username "..." is not valid. Line...
2	Work Date	ALWAYS REQUIRED. A date for which the imported time was reported. This must be a valid date in one of the following two formats: 'yyyy-MM-dd' or a locale specific default short date format. For example, the default short date format for the US is 'MM/dd/yy', the default short date format for most European countries is 'dd/MM/yy'. If the date you are trying to import is not a valid date or is not in one of the formats specified above, the entire record will not be imported. You will see the message: The date "..." is not valid. Line...
3	Project Organization Code	ALWAYS REQUIRED. This code uniquely identifies the Organization to which the project belongs. If the Organization you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The organization "..." is not valid. Line....
4	Project Code	ALWAYS REQUIRED. This project code identifies the project for which the imported time was reported. The project code is unique within an Organization. That is, an Organization can not have two projects with the same Project Code. Different Organizations, however, can have projects with the same Project Code. This is the code that appears on most screens and reports. If the Project you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The project "..." is not valid. Line....
5	Task Name	CONDITIONALLY REQUIRED. The value for this field will be required if the project is set up to allow task level time reporting only. This task name should be unique within a project. That is, a project should not have two tasks with the same Task Name. This Task Name is limited to 50 characters. If a task with this name does not exist for this project, the entire record will not be imported. You will see the message: The task "..." is not valid. Line .... In addition, if the task is a part of a multi-level task hierarchy and has parent task(s), the field is expected to contain a complete comma-separated double-quoted path to the task. For example, if the desired task has a parent, the field should contain the following value: “parent name, child name”.
6	Project Type	This code uniquely identifies the Project Type. If the Project Type you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The project type "..." is not valid. Line.... If no value was provided for this field, the Project Type that was specified for the project by the Project Manager on the Project Page will be used.
7	Pay Code	This code uniquely identifies the Pay Code. If the Pay Code you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The pay code "..." is not valid. Line.... If no value was provided for this field, the import process will utilize the standard Unanet pay code default logic currently available in the Timesheet Edit and Time Populate functions (see Pay Code to learn more about the default logic).
8	Hours	ALWAYS REQUIRED. The number of hours worked. This number will be rounded according to the time increment specified in the user’s profile. Please note that a period is the only acceptable decimal separator.
9	Bill Rate	Bill Rate associated with the time data. This field can be blank.
10	Cost Rate	Cost Rate associated with the time data. This field can be blank. The value for this field will be ignored if the Cost Dilution Unanet function is enabled.
11	Bill Organization Code	Bill Organization code associated with the time data. This field can be blank.
12	Cost Organization Code	Cost Organization associated with the time data. This field can be blank.
13	Labor Category	Labor Category associated with the time data. This field can be blank.
14	Location	Location associated with the time data. This field can be blank.
15	Comments	This field can be used to store user specified comments. This field can store up to 2000 characters and it can be left blank.
16	Change Reason	CONDITIONALLY REQUIRED. The value for this field is only required if the Unanet installation requires compliance with DCAA guidelines and if the imported time data causes a change to an existing timesheet entry. This field can store up to 2000 characters.

Import File Format

The file to import must be saved in a comma delimited format. The fields can be enclosed in double quotes -- which would be particularly necessary should the data being imported contain commas.

Examples:

field1,field2,field3
"field1","field2","field3"
field1,"data containing commas (like this), should be double quoted",field3

Other file format requirements:

Each record must be in the same order as the table above
Note that some fields are always required
If you don't want to import information for a non-required field, just leave this field blank
If a given record does not contain a value for every field, the import will assume the remaining values are blank
The values for Username, Organization, Project, Task Name, Project Type, Pay Code, Bill Organization Code, and Cost Organization Code must match existing entries in Unanet

You can create the comma delimited import file with any number of tools. For those interested in using an Excel spreadsheet to create the file, you can click here to download an Excel Template with predefined headers and required fields.

EXCEL NOTE: the fields that start with ** are required fields.

EXCEL NOTE: Excel may add the double quotes around fields for you. If using the excel spreadsheet template -- note that excel will automatically put double quotes around fields that contain commas. Manually adding double quotes within excel would result in two sets of double quotes in the .csv file and thus cause an error on import.

EXCEL NOTE: When creating a comma delimited file using Excel, the saved ASCII text file will contain a header row. This row will be ignored by the import process and will not create any import warnings. The import process will ignore the header (as well as any other rows) that begin with: #USERNAME# or **

Import Time Screen

The Import Time screen looks like:

Import File: Here the Administrator specifies the file name to be imported.

Resulting Status: In addition to specifying the file name, the Administrator will be given an opportunity to trigger the timesheet submit process or force the timesheet status to become COMPLETED or LOCKED bypassing the regular approval process. The Administrator’s ability to do so will be controlled by a new property unatime.import.timesheet_submit.display. If the property is set to true, a group of radio boxes allowing the change of the timesheet status will be added to the page. The default Resulting Status will be INUSE. By default the property will be set to false and the radio buttons will not be displayed on the page. In that case, the timesheets created or updated by the import process will remain in the INUSE status.

Update Method: Additionally, should the timesheet already have an entry for a given date and a given project/project type/task/pay code in the import file, the Administrator will be given an option to append or replace the existing entry with the imported time. To facilitate that, another group of radio buttons, Update Method, will always be shown on the screen. The default update method will be Append.

The import process is triggered by pressing the Import button. All Warnings and Error messages are written to the screen and can then be saved if desired. While some line items may be found invalid and rejected, the remaining line items will be successfully imported and saved in the database. The rejected line items will be written out to a file on the Unanet server as well as displayed at the bottom of the Time Import user feedback screen. The rejected record file name will be UnanetTimeImportErrorxxx, where xxx is a randomly generated number. The file will be placed in the Unanet temporary directory (which is defined by the unanet.temp_directory Unanet property).

Processing Rules

New timesheet

If no timesheet containing the imported work date was found, a new timesheet will be created for the user based on the time period specified in their profile. All valid entries found in the import file for the time period corresponding to the new timesheet will be imported. In the event that no valid entries were found, the timesheet will remain in the database with zero hours and INUSE status. If at least one invalid entry was found for the timesheet, the timesheet will be left in the INUSE status regardless of the Resulting Status option specified by the Administrator.

If all line items for the timesheet were processed successfully, the import process will place the timesheet in the status specified by the Administrator on the import screen.

If the INUSE status was selected, the timesheet will remain in INUSE status.

If the SUBMITTED status was selected, the timesheet will be submitted. It will then go through the regular timesheet approval process as defined in the Unanet installation. Based on the definition, it may result in the timesheet being sent to one or more Approvers, or, if no Approvers were designated, it may cause the timesheet to be moved to COMPLETED status.

If the COMPLETED or LOCKED statuses were selected, the timesheet will be placed in the requested status bypassing the regular approval process.

Existing timesheet

If an existing timesheet containing the imported work date was found, its status will be examined first. If the timesheet is in EXTRACTED or LOCKED status, the imported line item will be rejected. Otherwise, the status will be reset to INUSE initially. If the Resulting Status of SUBMITTED was chosen, the timesheet will be submitted when all line items for the timesheet were processed. It will then go through the regular timesheet approval process as defined in the Unanet installation. If the COMPLETED or LOCKED statuses were selected, the timesheet will be placed in the requested status bypassing the regular approval process when all line items for the timesheet were processed.

By default the Time Import process will add the hours and concatenate the comments specified in the import file with any existing time data reported by the user on a given date to a given project/project type/task/pay code. However, if the Update Method of Replace was selected by the Administrator on the import screen, the existing hours and comments will be replaced with the values specified in the import file.

Please note that the same rule will apply to the multiple entries for the same person/date/project/project type/task/pay code found in a file. If multiple entries were found in the same file and no replace option was specified, the hours will be added and the contents of the comments field concatenated. If the replace option was selected, only the contents of the last line item found in the file will remain in the database.

General

The net result of the changes applied to a single timesheet cell cannot be negative.

If the net result of the changes applied to a single timesheet cell is zero, the timesheet cell will be removed or not added to the timesheet. However, if the Unanet installation is set up to require compliance with DCAA guidelines, the zero timesheet cell will remain present in the database for audit purposes.

Approval History and Notification

An approval history entry will be generated for each timesheet status change triggered by the import process. All e-mail notifications available and enabled in the Unanet installation and associated with the timesheet status changes triggered by the import process will be sent out.

If the Unanet installation is set up to require compliance with DCAA guidelines, an audit trail will be generated for each change to an existing timesheet cell. Value provided in the Change Reason import file field will be used as a Audit Trail comment. If no value for the Change Reason field was provided, the line item will be rejected.

Command Line Import

As an alternative to using the Import Screen described above, you can also invoke imports using the command line import functionality. This functionality allows you to invoke an import without accessing the Unanet system application (user interface). This may be useful if you would like to write an external process to extract data from an upstream system and programmatically load it into Unanet (without user intervention).

Return to Unanet Table of Contents page