After a set of timesheet records have been exported as a database export (txt) file, the text can be imported into another application or database through that system's import utility. The text is set in a fixed format, with all unique identifiers for the timesheet records and their associated billing codes provided within the data for each record.
Each line of the flat file data provides the information for a distinct timesheet entry, which is the intersection on a timesheet grid of the billing codes and the day of the week for an employee's timesheet. A distinct timesheet entry exists for each entry of hours on a timesheet.
The timesheet to which a given timesheet entry belongs can be identified by the timesheet Id column included in the flat file output, with the employee related to that timesheet identified by their employee number. A specific timesheet entry can be identified by the timesheet Id, project, activity and task billing codes, billable status and the day Id. Billing rates are applied to each entry as defined by the rates registered for each user and project (as described for Invoice Reports).
The format of the flat file data, with the first character on the left of a printed line listed as Position 1, is as follows:
|1-20||Employee Number||The employee number of the employee corresponding to the timesheet entry record.|
|21-30||Username||The username registered for the employee.|
|31-50||First Name||The first name of the employee.|
|51-70||Last Name||The last name of the employee.|
|71-170||E-Mail Address||The email address of the employee.|
|171-195||Phone||The work phone number and extension (if any) of the employee.|
|221-225||Administrator flag||A boolean value indicating whether or not the employee is an administrative user. This value is represented by either of the words 'true' or 'false'.|
|226-230||Active flag||A boolean value indicating whether or not the employee is an Active user. This value is represented by either of the words 'true' or 'false'.|
|231-241||Timesheet Id||The uniquely identifying Timesheet Id assigned to the timesheet.|
|242-266||Week Ending Date||The Week Ending Date corresponding to the timesheet. This date is presented in the form "DD-MM-YYYY HH:MM:SS", with the hours, minutes and seconds all set to 0.|
|267-291||Submitted Date||The Submitted Date corresponding to the timesheet. This date and time value is presented in the form "DD-MM-YYYY HH:MM:SS"|
|292-341||Project||The Project billing code corresponding to the timesheet entry. The billing code is represented as the unique code corresponding to the project of the timesheet entry.|
|342-391||Activity||The Activity billing code corresponding to the timesheet entry. The billing code is represented as the unique code corresponding to the project and activity on the timesheet entry.|
|392-441||Task||The Task billing code corresponding to the timesheet entry. The billing code is represented as the unique code corresponding to the project, activity and task on the timesheet entry.|
|442-461||Hours||The hours entered for the timesheet entry.|
|462-472||Day of the Week||An integer value indicating the day of the week which corresponds to the timesheet entry. Days of the week correspond with these numbers as follows:
|473-492||Billing Rate||The hourly billing rate to be applied to the hours of the listed timesheet entry.|
|493-503||Comment Size||An integer value indicating the size of any comment text which is associated with the timesheet entry. If there is no comment text for the entry, this value will be set to 0.|
|504-5503||Timesheet Comment||The comment text which has been associated with the timesheet entry. Up to 5000 characters can be entered for a timesheet entry comment, with the number of characters stored for the comment indicated in the Comment Size attribute preceding this data. If there is no comment for a timesheet entry, this area will contain 5000 space characters.
As a timesheet comment is right-padded with space characters to bring it to a fixed length of 5000 characters, the comment itself can be extracted from this block of text by fetching a sub-string from position 504 to the length of text indicated in the Comment Size attribute.
Carriage returns within the comment are indicated by a "¶" character and can be replaced with the receiving database's character combination for carriage returns as desired.
|5504-5513||Entry Date||The Date for which the timesheet entry was recorded (this can also be determined from the Week Ending Date and Day of the Week attributes). This date value is presented in the form "DD-MM-YYYY".|
|5514-5524||Billable Status||An integer value indicating whether the time entry is for a billable or non-billable item of work. The int values for that billable status are as follows:
0 Non-billable work
1 Billable work