Introduction

TNA 2000 supports a variety of import formats for importing clockings. This one is known as the TNA Generic Import format. the main advantage of this format is that it can be used to import items other than just clockings. It is a CSV (Comma Separated) format. The first field on each row identifies the type of record being imported. For example C is for clockings, L is for leave, E is for Employee and so on.

 

 If the program exporting the data can be configured, then this is the format you should use.

 

A Sample Excel Spreadsheet, which can generate this CSV format can be found here. Remember that when saving from Excel only the active Sheet will be saved, and you need to select CSV from the "Save As Type" list.

Related Articles

Automatically Importing Clockings and other Data

Clocking Records

Field	Name	Max Length (Chars)	Description
1	Record Type	1	C for clockings
2	Site	4	Optional. Usually left blank. If blank then the site code at the place of import is used.
3	Card Number	10	The Card Number for the employee. This is optional if the Employee Number is used.
4	Employee Number	10	If omitted then the employee assigned to the card gets the clocking. Either the Card Number, or Employee number (or both) must be used. If both are used then the Employee Number will override the Card Number, but the card number is stored with the clocking.
5	Date	10	Required.  dd/mm/yyyy
6	Time	8	Optional. If empty then the clocking will apply from 0:00. hh:mm:ss or hh:mm
7	Direction	1	Either I for in or O for out. Required if the clocking is a payroll clocking, but not required if it's just a change of classification.
8	Device	8	Optional. Usually a reference to the clock being used. In the case of an import you may want to use this field to identify imported clockings.
9	Authorised	1	Y if the clocking is valid, or N, or D, if it still requires further authorisation. If omitted then Y is assumed.
10	Company	30	Optional. Places the employee in a particular company, from the time/date entered above. You can use either the company number, or the company name (as set in the TNA 2000 Configuration module.)  If the company does not exist then it will be created on the fly in the TNA 2000 system.
11	Branch	30	Optional. Places the employee in a particular branch, from the time/date entered above. You can use either the branch number, or the branch name (as set in the TNA 2000 Configuration module.) If the branch does not exist then it will be created on the fly in the TNA 2000 system.
12	Department	30	Optional. Places the employee in a particular department, from the time/date entered above. You can use either the department number, or the department name (as set in the TNA 2000 Configuration module.)  If the department does not exist then it will be created on the fly in the TNA 2000 system.
13	Cost Centre	30	Optional. Places the employee in a particular cost centre, from the time/date entered above. You can use either the cost centre number, or the cost centre name (as set in the TNA 2000 Configuration module.) If the cost centre does not exist then it will be created on the fly in the TNA 2000 system.
14	Category	30	Optional. Places the employee in a particular category, from the time/date entered above. You can use either the category number, or the category name (as set in the TNA 2000 Configuration module.)  If the category does not exist then it will be created on the fly in the TNA 2000 system.
15	Scale	30	Optional. Places the employee in a particular scale, from the time/date entered above. You can use either the scale number, or the scale name (as set in the TNA 2000 Configuration module.)  If the scale does not exist then it will be created on the fly in the TNA 2000 system.
16	Type	30	Optional. Places the employee in a particular type, from the time/date entered above. You can use either the type number, or the type name (as set in the TNA 2000 Configuration module.)  If the type does not exist then it will be created on the fly in the TNA 2000 system.
17	Workgroup	30	Optional. Places the employee in a particular workgroup, from the time/date entered above. You can use either the workgroup number, or the workgroup name (as set in the TNA 2000 Configuration module.)  If the workgroup does not exist then it will be created on the fly in the TNA 2000 system.
18	Job	30	Optional. Places the employee in a particular job, from the time/date entered above. You can use either the job number, or the job name (as set in the TNA 2000 Configuration module.)  If the job does not exist then it will be created on the fly in the TNA 2000 system.
19	Customer	10	Optional. Use this only if you are creating a new Job, via field 18 above. If the job already exists (and is assigned to a customer) then this field is ignored.
20	Quantity	10	Optional. The quantity of items completed.
21	Reference	10	Optional. Anything you want. Not used by the TNA 2000 system, but can be included on reports.
22	Comment	250	Optional. Anything you want. Not used by the TNA 2000 system, but can be included on reports.

Employee Records

Field	Name	Max Length (Chars)	Description
1	Record Type	1	E for employees
2	Site	4	Optional. Usually left blank. If blank then the site code at the place of import is used.
3	Employee Number	10	Required. Each employee on the site needs a unique 10 character, alphanumeric identifier.
4	Pay Number	20	Optional. The identifier used by the payroll system, if it's different from the employee number.
5	Card Number	10	Optional. The Card Number that is associated with this employee.
6	First Name	20	Optional. The first name, or initial, of the employee.
7	Last Name	20	Optional. The last name of the employee.
8	Date Hired	10	Optional. In dd/mm/yyyy format
9	Shift	6	Optional. The shift code, of the shift, that the employee should be processed with.
10	Company	30	Optional. Places the employee in a particular company, from the time/date entered above. You can use either the company number, or the company name (as set in the TNA 2000 Configuration module.)  If the company does not exist then it will be created on the fly in the TNA 2000 system.
11	Branch	30	Optional. Places the employee in a particular branch, from the time/date entered above. You can use either the branch number, or the branch name (as set in the TNA 2000 Configuration module.) If the branch does not exist then it will be created on the fly in the TNA 2000 system.
12	Department	30	Optional. Places the employee in a particular department, from the time/date entered above. You can use either the department number, or the department name (as set in the TNA 2000 Configuration module.)  If the department does not exist then it will be created on the fly in the TNA 2000 system.
13	Cost Centre	30	Optional. Places the employee in a particular cost centre, from the time/date entered above. You can use either the cost centre number, or the cost centre name (as set in the TNA 2000 Configuration module.) If the cost centre does not exist then it will be created on the fly in the TNA 2000 system.
14	Category	30	Optional. Places the employee in a particular category, from the time/date entered above. You can use either the category number, or the category name (as set in the TNA 2000 Configuration module.)  If the category does not exist then it will be created on the fly in the TNA 2000 system.
15	Scale	30	Optional. Places the employee in a particular scale, from the time/date entered above. You can use either the scale number, or the scale name (as set in the TNA 2000 Configuration module.)  If the scale does not exist then it will be created on the fly in the TNA 2000 system.
16	Type	30	Optional. Places the employee in a particular type, from the time/date entered above. You can use either the type number, or the type name (as set in the TNA 2000 Configuration module.)  If the type does not exist then it will be created on the fly in the TNA 2000 system.
17	Workgroup	30	Optional. Places the employee in a particular workgroup, from the time/date entered above. You can use either the workgroup number, or the workgroup name (as set in the TNA 2000 Configuration module.)  If the workgroup does not exist then it will be created on the fly in the TNA 2000 system.
18	Job	30	Optional. Places the employee in a particular job, from the time/date entered above. You can use either the job number, or the job name (as set in the TNA 2000 Configuration module.)  If the job does not exist then it will be created on the fly in the TNA 2000 system.
19	ID Number	20	Optional. A national identity number for the employee. For example a passport number, or social security number.

Leave Records

Field	Name	Max Length (Chars)	Description
1	Record Type	1	L for leave
2	Site	4	Optional. Usually left blank. If blank then the site code at the place of import is used.
3	Employee Number	10	Required. Each employee on the site needs a unique 10 character, alphanumeric identifier.
4	From Date	10	Required. The first day on which the employee is on leave. In the format dd/mm/yyyy.
5	To Date	10	Required. The last day on which the employee is on leave. In the format dd/mm/yyyy.
6	Type	30	Required. The type of leave that the employee is on. Leave Types must be pre-configured in the Configuration module.
7	Paid Leave Days		Optional. A number valid to 2 decimal places. This will affect his accrued paid leave.
8	Paid Sick Days		Optional. A number valid to 2 decimal places. This will affect his accrued sick leave.
9	Approved	7	Optional. Valid options are Yes, or Y, No or N, Pending or P.
10	Approved By	20	Optional. The name, or number of the employee who approved the leave.
11	Approved On	10	Optional. Date of approval in dd/mm/yyy format
12	Reason	40	Optional. Additional reason information for the leave.
13	Comment	40	Optional. Any other information.

Notes

• All fields can be entered with trailing spaces clipped, or with trailing spaces included.
• Commas are not allowed in any fields.
• Duplicate clockings are not imported. A clocking is a duplicate if it has the same Site, Employee Number (or CardNumber), Date, Time and Direction as an existing clocking. In other words only one In, Out and Job clocking is allowed at the same time.
• If the first column is blank, ie if the line starts with a , then the line is ignored. This allows for comment records in the file.