Administration Guide
Appendix I - Timesheet Database Flat File

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:

Position Data Description
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.
196-220 Unused Unused
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:

0 Sunday
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
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