Tuesday, January 19, 2010

Importing legacy timesheet data into TimeControl

Some organizations have had timesheets for years and, when they implement TimeControl, they ask if they can import legacy timesheet data from their older systems into their new timesheet software.

The answer is yes. TimeControl is designed with an Import Timesheet feature that is described in the Reference guide. Unlike the imports for all the TimeControl tables, the Import Timesheet function is a fixed format of import. Several fields are required in a particular order. The TimeControl Import Timesheet function takes a Comma Separated Values (CSV) file which can be created in Excel to bring in the data.

The fields required are:

Field as described in the import tool

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 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”.

For those who are keen to get started, the HMS Technical Support department (Thanks James!) has made a spreadsheet available as a template. To use the spreadsheet, first download the Excel file called tcimporttimesheets.xls 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.

Happy importing!

No comments: