It’s not uncommon for a new TimeControl client to ask if it is possible to preserve their existing timesheet data in TimeControl so they can have long-term reports.

It is, of course.

TimeControl’s Link function has a special import type to import timesheets from an external source.  We designed this as a one-time migration rather than something that might be run on a regular basis.

The timesheet import function can receive timesheet data from any of numerous commercial or even internally customized timesheet sources.  There are, however, some rules about importing such data.

The TimeControl Import Timesheet function takes a Comma Separated Values (CSV) file which can be created in Excel to bring in the data.

First of all, to support TimeControl’s referential integrity, you will have to ensure that any Project codes, Charge codes, Employee Codes and Rate codes already exist in each of those related tables.  Failure to find a related reference will mean the import is rejected.  This probably means you’ll need to do this exercise in two parts, first making sure all the supporting tables are updated then importing the legacy records.

The import format is a flat (CSV) file with a fixed format.

The fields required are:

	Database Field name	Example Value
Week Ending Date	TSH_WEDATE	20080321
Timesheet Header Type	TSH_TYPE	NORM
Timesheet Header Employee	EMP_CODE	GJ018
Timesheet Header Owner	USR_CODE	ADMIN
Project Name	TSD_PRJ_NAME	DATA
Charge Code	TSD_CHR_CD	Data.2.3
Rate Code	TSD_RAT_CD	STANDARD
Hours Monday	TSD_CMON	8
Hours Tuesday	TSD_CTUE	8
Hours Wednesday	TSD_CWED	8
Hours Thursday	TSD_CTHU	8
Hours Friday	TSD_CFRI	8
Hours Saturday	TSD_CSAT	0
Hours Sunday	TSD_CSUN	0

There are a few constraints to be aware of:

• First of all each of the values that are being used to create new Timesheet Headers and Timesheet Details records must already exist in the system. So if you are creating a timesheet for Employee code GJ018 then that employee code must already be in the Employee table. Same goes for the User, the Rate code, the Project name and the Charge code.
• “NORM” is a normal timesheet. That’s distinct from a DBCR (Debit/Credit) entry that TimeControl might store internally.
• The ANSI date type is year/month/day in the format 20080321 which is March 21, 2008.
• Once the import is complete, you’ll need to check the log to make sure everything you were trying to import arrived.
• The timesheets, once created, will be in draft status and owned by the “Timesheet Header Owner” listed in the import record. In the example above that’s user “ADMIN”.

The HMS Technical Support department has a spreadsheet available as a template. To use the spreadsheet, first download the Excel file called tcimporttimesheets.xlsx from the TimeControl website. You’ll find the same data as is displayed in the table above. Enter your data into the appropriate columns then delete the first column and the first three rows which include descriptive header data and the one example record that is listed above.