Import Time

This feature allows for the importing of Time detail information into the Unanet system (including both regular time and timesheet adjustments). When importing time to a timesheet that is in the LOCKED or EXTRACTED status, the changes will be handled as Administrative Adjustments (assuming the Allow Adjustments options has been selected). Note that you will not be permitted to alter any timesheets designated as Historical Timesheets (imported via the Time - Historical Import feature).

Note that you cannot currently import Time In/Out entries.

Note that time being imported by the administrator will not be constrained by many of the standard project business validations, e.g. the user does not have to have an assignment for the time to be imported, existing assignment budget amounts are ignored, allow future time restrictions are ignored, timesheets can be posted across legal entities, etc. Further, when using a Resulting Status of SUBMITTED or LOCKED, the time stored procedure validations are not invoked.

Should time be imported to an INUSE status and a user subsequently accesses the timesheet, the normal user interface validations would then apply.

Roles

This screen is available to users having any of the following roles: Administrator and P&R Administrator

License

This feature is available with any Unanet edition.

Performance Tip Note: Depending on the number of users in your installation and other factors such as the overall performance of your platform, the activity of importing one or many files may take up to several minutes. Should this be the case with your installation, you may want to consider running these processes at non-peak times to minimize database contention.

Topics covered on this help page include:

Import File Record Layout (field by field descriptions)
Import File Format (note about csv layout and option to import a subset of columns using header values)
Excel Template (spreadsheet template containing column headings)
Import Screen (invoking the import via the user interface and specifying resulting status)
Processing Rules (important information about handling new vs existing timesheets)
Time Imports & Dilution

Import File Record Layout

IMPORTANT: Note that there are multiple places within this documentation that say you MUST use double quotes for the strings in your import. If you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually. Manually adding double quotes within Excel would result in multiple sets of double quotes in the resulting .csv file and thus cause an error (should you attempt to import that resulting file). Please see Excel Tips for more information.

Field Header Name

Required/Comments

Username

ALWAYS REQUIRED. Unique username (user id), for example, JDoe or jdoe (it does not need to be capitalized.) This value must match an existing Username value in your system. If the Username you are trying to import does not already exist in the database, the entire record will be rejected.

This must be an active user having the timesheetUser role.

Work_Date

ALWAYS REQUIRED. A date for which the imported time was reported.

Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options.

Project_Org_Code

ALWAYS REQUIRED. This code uniquely identifies the Organization to which the project belongs. This value must match an existing Project Organization Code value in your system. If the Organization you are trying to import does not already exist in the database, the entire record will be rejected.

Project_Code

ALWAYS REQUIRED. This project code identifies the project to which this time is being charged. The project code is unique within an Organization. This value must match an existing Project Code value in your system. If the Project you are trying to import does not already exist in the database, the entire record will be rejected.

Task_Name

CONDITIONALLY REQUIRED. The value for this field will be required if the project is set to require task level time reporting.

This value must match an existing Task Name value in your system. If the Task Name you are trying to import does not already exist in the database, the entire record will be rejected.

If a task is not a top-level task (e.g. it has sub-tasks), you must include a comma separated list of each task starting at the top level down. The following is an example of a valid task tree and how you would reflect each task using this import.

Task Tree Access String

1. Car Repair	"Car Repair"
1.1 Tires	"Car Repair,Tires"
1.2 Paint	"Car Repair,Paint"
1.2.1 Prep work	"Car Repair,Paint,Prep work"
1.2.2 Patching	"Car Repair,Paint,Patching"
1.3 Engine	"Car Repair,Engine"
1.3.1 Tune-up	"Car Repair,Engine,Tune-up"
1.3.1 Rebuild	"Car Repair,Engine,Rebuild"

See the Excel Tips regarding the use of double quotes and more.

Project_Type

This code uniquely identifies the Project Type. If the Project Type you are trying to import is not a valid value in the database, the entire record will be rejected.

If no value was provided for this field, the task's default Project Type will be used if one exists, otherwise the project's default Project Type will be used.

Time can be imported using both active and inactive project types.

Pay_Code

This code uniquely identifies the Pay Code. If the Pay Code you are trying to import is not a valid value in the database, the entire record will be rejected.

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 to derive a value (see Pay Code to learn more about the default logic).

Note that an administrator can import time to any pay code regardless of the project level pay code restrictions (much like the administrator can make time adjustments to any pay code).

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.

This field does not accept negative hours.

Records having a zero value will not result in the creation of a timesheet cell.

This field accepts a numeric value up to 13 positions to the left of the decimal and 2 positions to the right.

Bill_Rate

Bill Rate to be associated with the time entry. This field can be blank.

If you leave this value blank, the systems default 'rating' process will be triggered and the resulting timesheet entry will include the bill rate derived from that process (i.e. the rate will come from either the assignment, or labor category or person profile per the normal rating process depending on how the project and assignments are configured).

If you provide a value, this value will be used (i.e. the default rating process will not be triggered). Do note, however, that the next time any activity is done that may trigger a re-rate of un-extracted time for this user (for this timesheet period), the systems default rating logic will re-calculate the rate based on the current rate information stored in the system as of that time. Activities that may trigger this re-rating include items such as editing a timesheet, modifying a person's rate or classification, manipulating assignments, changing a project or master level labor category rate, etc.

Note: Without actually supplying a bill rate in the import file, the only way to accomplish using multiple labor categories with distinct bill rates is to create arbitrary tasks and manage at the task level.

If a different labor category is used in the import file than in the assignment or if the assignment is simply BLANK, the labor category will populate from the import file, but the associated bill rate will be incorrect. It will default to the first bill rate in the list of labor categories, even though the labor category imported properly.

This field does not accept negative values.

This field accepts a numeric value up to 10 positions to the left of the decimal and 5 positions to the right.

Cost_Rate

Cost Rate to be associated with the time entry. This field can be blank.

If you leave this value blank, the systems default 'rating' process will be triggered and the resulting timesheet entry will include the cost rate derived from that process (ie the rate will come from either the assignment, or labor category or person profile per the normal rating process, considering pay code factors, etc., depending on how the projects, assignments, labor categories, pay codes, etc. are configured).

If you provide a value, this value will be used (ie the default rating process will NOT be triggered -- pay code cost factors will be ignored, assignment cost rates will be ignored, etc.).

Do note, however, that the next time any activity is done that may trigger a re-rate of un-extracted time for this user (for this timesheet period), the systems default rating logic will re-calculate the rate based on the current rate information stored in the system as of that time). Activities that may trigger this re-rating include items such as editing a timesheet, modifying a person's rate or classification, manipulating assignments, changing a project or master level labor category rate, etc.
The value for this field will be ignored for exempt users if the Cost Dilution Unanet functionality is enabled (it will be used for non-exempt and non-employees).
Note: Calculation of effective cost rate is: (cost rate * dilution factor if applicable) + additional pay rate.

This field does not accept negative values.

This field accepts a numeric value up to 10 positions to the left of the decimal and 5 positions to the right.

Project_Org_Override

Project Organization code associated with the time data. A value in this field will override the Project Organization that the project actually belongs to. This field can be blank.

Person_Org_Override

Person Organization code associated with the time data. A value in this field will override the Person Organization that the person actually belongs to. This field can be blank.

Labor_Category

CONDITIONALLY REQUIRED. The value for this field is only required if the project to which the time is being associated is configured to Require Labor Category. When not required, this field can be blank.

The value provided must match an existing Labor Category value as defined via the Admin >> Setup >> Labor Category screen.

If the labor category is not supplied, the following rules apply for the system deriving the labor category:

Hide Labor Category on Timesheet Edit (unatime.timesheet.hide.labor_category) property	Project setting for requiring labor categories	Result
if unchecked	if set to not require or optionally require	no labor category will be derived
if unchecked	if set to require	time import will get an error because labor category is required
if checked	if set to require	labor category will be derived from system

Location

Location associated with the time data. This field can be blank. This value must match an existing Location value in your system. If the project is configured to require a Location and none is supplied on this import file, the system will use the Location Defaulting Rules to provide a value.

Comments

This field can be used to store user specified comments. This field can be left blank.

Maximum Length: 2000 characters

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.

Maximum Length: 2000 characters

Cost_Structure

CONDITIONALLY REQUIRED. This field is used in conjunction with the Cost_Element field. Both of these fields must be supplied if the intention is to provide values for Cost_Structure/Cost_Element in the import file.

If no value is supplied, the system will attempt to populate an appropriate cost structure based on governing Assignment or if not present the person's default cost structure.

This field can be blank.

This value must match an existing Cost Structure code value in your system. If the Cost Structure you are trying to import does not already exist in the database, the entire record will be rejected.

Cost_Element

CONDITIONALLY REQUIRED. This field is used in conjunction with the Cost_Structure field. Both of these fields must be supplied if the intention is to provide values for Cost_Structure/Cost_Element in the import file.

If no value is supplied, the system will attempt to populate an appropriate cost element based on governing Assignment or if not present the person's default cost element.

This field can be blank.

This value must match an existing Cost Element code value in your system (currently associated with the Cost Structure specified in the above field). If the Cost Element you are trying to import does not already exist in the database, the entire record will be rejected.

Time_Period_Begin_Date

This field is used when importing time for a 'Weekly Overlap' time period for helping to determine which timesheet the hours should be applied to when importing details for an "overlap day". This field will represent the begin date of the time period to which the time should be applied. Without including this additional column, any time imported to an "overlap day" will be applied to the second of the two time periods.

This field is ignored when importing time into other time period types.

Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options.

Post_Date

This field is only used when importing time adjustments to LOCKED or EXTRACTED timesheets (otherwise ignored). This date must be >= the work date of the timesheet entry being adjusted. If not supplied, the current system date will be used for stamping post date on imported adjustment entries.

Note: This Post Date does not refer to the date assigned in the Financial Labor Cost Post or the Financial Billing & Revenue Post. See Post Date for additional information.

Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options.

Additional_Pay_Rate

Additional Pay Rate to be associated with the time entry. This field can be blank.

If you leave this value blank, the systems default 'rating' process will be triggered and the resulting timesheet entry will include the additional pay rate derived from that process (ie the rate will come from the pay code entity per the normal rating process).

If you provide a value, this value will be used (ie the default rating process will NOT be triggered).

Do note, however, that the next time any activity is done that may trigger a re-rate of un-extracted time for this user (for this timesheet period), the systems default rating logic will re-calculate the rate based on the current rate information stored in the system as of that time). Activities that may trigger this re-rating include items such as editing a timesheet, modifying a person's rate or classification, manipulating assignments, changing a project or master level labor category rate, etc.
The value for this field can be applied to all user classification types (with or without the Cost Dilution Unanet functionality enabled.
Note: Calculation of effective cost rate is: (cost rate * dilution factor if applicable) + additional pay rate.

This field accepts a numeric value up to 10 positions to the left of the decimal and 5 positions to the right.

Note: Check out the Unanet Data model for specific field data types, lengths, and other attributes.

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.

Importing a Sub-Set of Columns using a Field Header Record

If you are not using the default column layout sequence as defined above, you must include a header record (prefixed with an asterisk *), containing the column Header Names for those columns you are including in the import file (to indicated what data is contained in each column). The specific Header Names for each column are listed in the table above.

Excel Template

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 download an Excel Template with predefined headers and required fields noted.

See the Excel Tips regarding the use of double quotes and more.

Import Screen

Depending on your system properties, the Import Time screen may look like:

Field Descriptions:

Field	Description
Type of Import	Select the appropriate import type from the list of available types of imports.
File to Import	Enter (or browse for) the file that you would like to import.
Output Options	Specify the level of detail included in the output.
Cell Update Method	The Administrator will be given an option to control what behavior should occur if time already exists in the system for a particular timesheet. Should the timesheet already have an entry for a given date and a given project/project type/task/pay code/labor category in the import file, the Administrator will be given an option to append or replace the existing entry with the imported time. The default update method will be Append. This option applies to both adjusting time entries as well as non-adjustment (ie regular time) entries.
Timesheets	This option will allow an administrator to instruct the import to completely delete an entire timesheet associated with a time entry being imported prior to importing any rows, essentially allowing for a complete replacement of a timesheet. This feature is different than the Cell Update Method of Replace -- which only replaces the contents of a single cell -- where this feature completely deletes a timesheet prior to importing the new cell entries. This option is only allowed for timesheets that have yet to be LOCKED or EXTRACTED and thus does not apply when importing timesheet adjustments.
Resulting Status	The Administrator will be given an opportunity to trigger the timesheet submit process or force the timesheet status to become COMPLETED or LOCKED potentially bypassing the regular approval process. The default Resulting Status will be INUSE. Adjusted timesheet entries will remain in the same EXTRACTED or LOCKED status regardless of the value supplied in this control. Time cannot be charged against projects having an inactive Owning Org nor against inactive Person Orgs. Note that if your site is configured to use a custom stored procedure to validate saved or submitted time, that special validation will not apply to time loaded via the time import.
Adjustments	This option will allow the time import to generate adjustment records for time imported to LOCKED or EXTRACTED timesheets. Note that a single import file can contain both original time entries and adjusting time entries. If this option is not checked, time entries associated with an existing LOCKED or EXTRACTED timesheet will be ignored. Adjustment hour values should be greater than or equal to zero. Time adjustments included in the import file should reflect the desired end result. That is, if you are attempting to update an existing entry from 8 hours to 4 hours, you would import 4 hours. If you were attempting to move hours from one project to another, you would import 0 hours for one project and the desired number of hours for the new project. Adjusted timesheets will remain in the same EXTRACTED or LOCKED status, and like Administrative Timesheet Adjustments entered via the user interface, no approvals will be necessary. Note that if the timesheet against which adjustments are attempted already has Pending End User Adjustments, the imported adjustments will be ignored.

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 Temp Directory (Fully Qualified Directory Name) (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 (unless the Allow Adjustments option has been selected).
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 imported/processed. It will then go through the regular timesheet approval process as defined in the Unanet installation.
- If the Resulting Status of COMPLETED or LOCKED was selected, the timesheet will be placed in the requested status bypassing the regular approval process when all line items for the timesheet have been imported.

If a time entry being imported is associated with an Historical Timesheet (that is, a timesheet having the "Historical" indicator set), the imported line item will be rejected.

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/labor category 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.

Zero and Negative Entries

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.

Note regarding re-importing time or importing historical time: On occasion, you may attempt to export then re-import time for various reasons (e.g. attempting to set old time periods to COMPLETE, or re-dilute old time periods or simply importing historical entries). When attempting this action, be aware of the following potential issues:

1) Assignment Changes -- If assignments have been added, removed or changed since the original time was completed, exporting and then re-importing that time may result in different rates, labor categories, etc. or even possibly prevent the addition of the time during the re-import.

2) Project Attributes -- Be aware that certain project attributes may have changed since the original time was entered and thus you may encounter issues when attempting to re-import the time. For example, if the Require Task or Enforce Project/Task Dates attribute were not originally set, but are now in affect, this could prevent time from being re-imported.

3) Inactive Users -- Note that if you have users that are now inactive, you will not be able to re-import their old time.

4) Approval Requirements -- If your system is configured to require approvals, be sure the time being imported belongs to users associated with an Approval Groups having at least one active approver.

Note regarding importing large volumes: Depending on the performance of your equipment and network, importing very large numbers of records can sometimes take very long. In these cases, to avoid having your import 'timeout', you could consider running the import using the "Output Option = All", so that feedback continues to be written to the screen throughout the import and thus keep the browser session active (avoiding any timeout issue).

Approval History and Notification

An approval history entry will be generated for each timesheet status change triggered by the import process. All email 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.

Time Imports & Dilution

When importing Time data having a resulting status of COMPLETED or LOCKED, the dilution logic will be triggered. Importing any adjustments will also result in dilution being recalculated for the adjusted timesheets. Dilution will not be triggered for any other resulting statuses.

See Dilution for additional information about this feature.