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:
This screen is available to users having any of the following roles: Administrator and P&R Administrator
This feature is available with any Unanet edition.
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)
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 |
||||||||||||||||
1 |
A |
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:
|
||||||||||||||||
2 |
B |
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. |
||||||||||||||||
3 |
C |
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. |
||||||||||||||||
4 |
D |
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. |
||||||||||||||||
5 |
E |
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
See the Excel Tips regarding the use of double quotes and more. |
||||||||||||||||
6 |
F |
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. |
||||||||||||||||
7 |
G |
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. |
||||||||||||||||
8 |
H |
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. |
||||||||||||||||
9 |
I |
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. |
||||||||||||||||
10 |
J |
Bill_Amount |
CONDITIONALLY REQUIRED. Bill Amount 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. This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right. |
||||||||||||||||
11 |
K |
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. |
||||||||||||||||
12 |
L |
Cost_Amount |
CONDITIONALLY REQUIRED. Cost Amount 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 16 positions to the left of the decimal and 2 positions to the right. |
||||||||||||||||
13 |
M |
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. |
||||||||||||||||
14 |
N |
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. |
||||||||||||||||
15 |
O |
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. |
||||||||||||||||
16 |
P |
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). |
||||||||||||||||
17 |
Q |
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 |
||||||||||||||||
18 |
R |
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. |
||||||||||||||||
19 |
S |
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. |
||||||||||||||||
20 |
T
|
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. |
||||||||||||||||
21 |
U |
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.
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.
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 indicate what data is contained in each column). The specific Header Names for each column are listed in the table above.
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.
See the Excel Tips regarding the use of double quotes and more.
Depending on your system properties, the Import Historical Time screen may look like:
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 Import results page. The rejected record file name will be the import name (from the Type of Import drop-down on the Admin>> Import page) , concatenated with a space and "Errors.csv" (e.g., Item - Master Errors.csv, Alternate.csv, etc.). 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). You can also click on the Download the error file link at the bottom of the Import results page (sample image follows).