Import Time - Historical

This feature allows for the importing of Historical Time Entries into the Unanet system. The feature is very similar to the Time Import with respect to the details being imported, however, there are several key differences with this facility. Timesheets created as a result of this historical time import will be designated "historical" which will mean they are not subject to any further changes via the user interface. The values imported are the values that will be stored with the time (that is, the system will bypass normal defaulting and data validation routines and insert the values as they are presented in this file). Further, time designated as historical, will not be modified by any regular system re-rating activities (i.e. frozen as is).

Considerations when running this import:

All timesheets imported via this mechanism will have a status of LOCKED (regardless of whether or not your system is configured to utilize the LOCKED status).
- While the status is LOCKED, changes can be made to historical time entries by running another Historical Time Import file for the same timesheets (assuming the time has not been included in a Labor Cost Post or Billing and Revenue Post).
- No approvals apply to these historical timesheet entries (ie they will immediately be stamped with a LOCKED status)
The Timesheet Audit functionality is not applicable to historical timesheets.
Effective Quantity, Effective Bill Rate and Effective Cost Rate fields will match the regular quantity, bill rate and cost rate values upon successful import.
You cannot import Time In/Out entries for historical timesheets.
Historical time will not be constrained by many of the standard project business validations.
- For example, 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, entries may be imported against inactive attributes, etc.
The time stored procedure validations are not invoked.
Negative hours, rates and amounts are allowed.
Dilution will not be triggered to run against historical time.
Empty rate values can be back-calculated from supplied amounts.

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)

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 user:

historical time can be imported to both active and inactive users
must have the timesheetUser role
must be associated with a time period

Work_Date

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

This date must not be earlier than the user's earliest Person Profile > Rate > Begin Date.

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.

Historical time can be imported to both active and inactive Organizations.

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.

Historical time can be imported to projects having both active and inactive project types.

Task_Name

This value identifies the task to which the time is to be associated. The value must match an existing Task Name value in your system for the project. If the Task Name you are trying to import does not already exist in the database, the entire record will be rejected.

Providing a Task Name is not required (even if the project is configured to require tasks).

Historical time can be imported to both active and inactive tasks.

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

ALWAYS REQUIRED. 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.

Historical time can be import using both active and inactive project types.

Pay_Code

ALWAYS REQUIRED. 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.

Historical time can be imported to any pay code regardless of the project level pay code restrictions.

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.

Negative hours are allowed.

If the resulting value of a timesheet entry is zero hours, no entry will be saved for that timesheet cell (and any existing entry will be removed).

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

Bill_Rate

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

Bill_Amount and Bill_Rate cannot both be empty (at least one of them must be provided). If Bill_Amount is specified and Bill_Rate is empty, the Bill_Rate will be derived based on the Bill_Amount provided value and the required Hours value. If both are provided, the supplied Bill_Amount will be ignored.

Negative values are allowed.

Tip: For Cost Plus projects, this value is typically set to $0 as the system will interpret the cost rate values as billable in the Project Accounting Reports.

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

Bill_Amount

CONDITIONALLY REQUIRED. Bill Amount to be associated with the time entry. This field can be blank.

Negative values are allowed.

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

Cost_Rate

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

Cost_Amount and Cost_Rate cannot both be empty (at least one of them must be provided). If Cost_Amount is specified and Cost_Rate is empty, the Cost_Rate will be derived based on the Cost_Amount provided value and the required Hours value. If both are provided, the supplied Cost_Amount will be ignored.

Negative values are allowed.

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

Cost_Amount

CONDITIONALLY REQUIRED. Cost Amount to be associated with the time entry. This field can be blank.

Negative values are allowed.

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

Project_Org_Override

REQUIRED ON ADD. Project Organization code associated with the time entry. A value in this field will override the Project Organization that the project actually belongs to.

Historical time can be imported to both active and inactive Organizations.

Person_Org_Override

REQUIRED ON ADD. Person Organization code associated with the time entry. A value in this field will override the Person Organization that the person actually belongs to.

Historical time can be imported to both active and inactive Organizations.

Labor_Category

Labor Category associated with the time entry.

When provided, the value must match an existing Labor Category in your system.

Providing a Labor Category is not required (even if the project is configured to require Labor Categories). Further, a value provided is not restricted to any current list of project level labor categories.

Location

Location associated with the time entry.

When provided, the value must match an existing Location in your system.

Providing a Location is not required (even if the project is configured to require a Location).

Comments

Comments associated with the time entry. This field can be left blank.

On Append, append the comments to existing comments. On Replace, replace the comments with the new comments.

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.

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.

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 can be used when importing time for a 'Weekly Overlap' or 'Every Two Weeks 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.

This field can be left blank. Without supplying a value for this column, any time imported to an "overlap day" will be applied to the second of the two time periods.

When supplied, the Work Date provided must fall within the time period identified by this entry, otherwise the record will be rejected.

The Time Period supplied must not be earlier than the user's earliest person profile > Rate > Begin Date.

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

Post_Date

The Post Date to be associated with the time entry. This field can be left blank.

If not provided, the Work_Date value will be used for Post_Date.

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

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 Historical 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	This control will determine if imported records are intended to be appended to (ie added to) existing entries, or if they are to replace existing values. Matching entries will be those records having the same project org/ project code/ project type/ task/ pay code/ labor category/ location/ work date and user. The default update method will be Append. When Replace is the selected option, the Hours and Comments will be replaced with the values provided in the import file. When Append is the selected option, the Hours provided in the import file will be added to the existing hours and the Comments will be appended to any existing Comments. If the total length of the appended comments exceeds 2000 characters, the record will be rejected.
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 feature can only be used against existing timesheets that were previously loaded using the Historical Time Import (ie only those having the Historical Timesheet Indicator set). Deleting a Historical Timesheet If your goal is to remove all time entries previously imported using the historical timesheet import, you will need to create an import file having this layout and supplying a value of 0 in the Hours field (and then select this purge option when running that file). The zero hour records will identify for the import which timesheets to purge, and then when processing the record as a potential replacement, the insert will be ignored due to the zero hour total. Do note, that the timesheet itself will remain in the system though it will not have any timesheet cell entries.

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 Historical Time Import user feedback screen. The rejected record file name will be UnanetHistoricalTimeImportErrorxxx, 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).