 |
Technical Bulletin 032 |
|
|
|
TNA Generic
Import Format
Applicable
version: 5.01 and later
TNA 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.
Automatically Importing Clockings and other
Data
|
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 Configuration module.)
If the company does not exist then it
will be created on the fly in the TNA
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 Configuration module.) If
the branch does not exist then it will
be created on the fly in the TNA 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
Configuration module.) If the
department does not exist then it will
be created on the fly in the TNA system. |
|
13 |
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
Configuration module.) If the category does not exist then it will be
created on the fly in the TNA system.
|
|
14 |
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 Configuration
module.) If the cost centre does not exist
then it will be created on the fly in
the TNA 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 Configuration module.) If
the scale does not exist then it will be
created on the fly in the TNA 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 Configuration module.) If the
type does not exist then it will be
created on the fly in the TNA 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 Configuration
module.) If the workgroup does not
exist then it will be created on the fly
in the TNA 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 Configuration module.) If the
job does not exist then it will be
created on the fly in the TNA 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 system, but can be included on
reports. |
|
22 |
Comment |
250 |
Optional. Anything you want. Not used by
the TNA system, but can be included on
reports. |
|
23 |
Clocking Type |
1 |
Optional. The type of clocking imported e.g. use A for TNA clockings. The clocking types are:
A - TNA
B - Access
C - TNA, Access
D - Job
E – TNA, Job
F – Job, Access
G – TNA, Job, Access
H – Info
I – Info, TNA
J – Info, Access
K – TNA, Info, Access
L – Info, Job
M – TNA, Info, Job
O – TNA, Info, Job, Access
|
|
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 Configuration module.)
If the company does not exist then it
will be created on the fly in the TNA
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 Configuration module.) If
the branch does not exist then it will
be created on the fly in the TNA 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
Configuration module.) If the
department does not exist then it will
be created on the fly in the TNA system. |
|
13 |
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
Configuration module.) If the category does not exist then it will be
created on the fly in the TNA system.
|
|
14 |
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 Configuration
module.) If the cost centre does not exist
then it will be created on the fly in
the TNA 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 Configuration module.) If
the scale does not exist then it will be
created on the fly in the TNA 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 Configuration module.) If the
type does not exist then it will be
created on the fly in the TNA 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 Configuration
module.) If the workgroup does not
exist then it will be created on the fly
in the TNA 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 Configuration module.) If the
job does not exist then it will be
created on the fly in the TNA system. |
|
19 |
ID Number |
20 |
Optional. A national identity number for
the employee. For example a passport
number, or social security number. |
|
20 |
Work Email |
50 |
Optional. Employee work email address |
|
21 |
Home Email |
50 |
Optional. Employee home email address |
|
22 |
Termination Date |
10 |
Optional. Employee termination dates
used to update terminations. Format dd/mm/yyy |
|
23 |
Access Rights |
2 |
Optional. Employee access rights to
areas |
|
Field |
Name |
Max Length (Chars) |
Description |
|
1 |
Record Type |
2 |
CU for customer |
|
2 |
Customer Code |
10 |
Required. The code used to identify the
customer. If the
customer does not exist then it will be
created on the fly in the TNA system. |
|
3 |
Company |
40 |
Optional. The company name. |
|
4 |
Contact |
20 |
Optional. The name of the contact person
at the company. |
|
5 |
Phone |
20 |
Optional. The customer's contact number. |
|
6 |
Fax |
20 |
Optional. The customer's fax number. |
|
7 |
Email |
255 |
Optional. The customer's email address. |
|
Field |
Name |
Max Length (Chars) |
Description |
|
1 |
Record Type |
1 |
J for customer |
|
2 |
Job Code |
10 |
Required. This is the job code used to
recognize the particular job. |
|
3 |
Job Description |
30 |
Optional. The description of the
job. |
|
4 |
Open Date |
10 |
Optional. The date the job is added to
the system. In the format dd/mm/yyyy. |
|
5 |
Expected Finish Date |
10 |
Optional. The date the job is expected
to be completed. In the format dd/mm/yyyy. |
|
6 |
Finished Date |
10 |
Optional. The actual date when the job
is finished. In the format dd/mm/yyyy. |
|
7 |
Closed Date |
10 |
Optional. The date when the job is
closed on the system. In the format dd/mm/yyyy. |
|
8 |
Job Status |
8 |
Optional. The current status of the job.
Options are: OPEN, STARTED, ON HOLD, FINISHED, CLOSED. |
|
9 |
Expected Cost |
11 |
Optional. A number valid to 2 decimal
places. |
|
10 |
Total Cost |
11 |
Optional. A number valid to 2 decimal
places. |
|
11 |
Unit |
10 |
Optional. |
|
12 |
Expected Quantity |
11 |
Optional. A number valid to 2 decimal
places. |
|
13 |
Quantity |
11 |
Optional. A number valid to 2 decimal
places. |
|
14 |
Customer |
10 |
Optional. The customer code. |
|
15 |
Order Number |
20 |
Optional. |
|
16 |
Comment |
40 |
Optional. Any other information. |
|
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 |
Requested On |
10 |
Optional. The date day on which the
employee requested leave. In the format dd/mm/yyyy. |
|
8 |
Paid Leave Days |
6 |
Optional. A number valid to 2 decimal
places. This will affect his accrued
paid leave. |
|
9 |
Paid Sick Days |
6 |
Optional. A number valid to 2 decimal
places. This will affect his accrued
sick leave. |
|
10 |
Approved |
7 |
Optional. Valid options are Yes, or Y,
No or N, Pending or P. |
|
11 |
Approved By |
20 |
Optional. The name, or number of the
employee who approved the leave. |
|
12 |
Approved On |
10 |
Optional. Date of approval in dd/mm/yyy
format |
|
13 |
Reason |
40 |
Optional. Additional reason information
for the leave. |
|
14 |
Comment |
40 |
Optional. Any other information. |
|
Field |
Name |
Max Length (Chars) |
Description |
|
1 |
Record Type |
1 |
R for rostering |
|
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 of 7 for which the daily shift rosters must
be imported. In the format dd/mm/yyyy. |
|
5 |
Day 1 Daily Shift Code |
6 |
The daily shift code to roster the
employee on. If left blank, any daily
shift rosters that already exists for
this day will be removed. Use
Custom as the daily shift code to import
custom start or end times. |
|
6 |
Day 1 Custom Start Time |
5 |
The start time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
7 |
Day 1 Custom End Time |
5 |
The end time for a Custom roster.
In the format hh:mm.
|
|
8 |
Day 2 Daily Shift Code |
6 |
The daily shift code to roster the
employee on. If left blank, any daily
shift rosters that already exists for
this day will be removed. |
|
9 |
Day 2 Custom Start Time |
5 |
The start time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
10 |
Day 2 Custom End Time |
5 |
The end time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
11 |
Day 3 Daily Shift Code |
6 |
The daily shift code to roster the
employee on. If left blank, any daily
shift rosters that already exists for
this day will be removed. |
|
12 |
Day 3 Custom Start Time |
5 |
The start time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
13 |
Day 3 Custom End Time |
5 |
The end time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
14 |
Day 4 Daily Shift Code |
6 |
The daily shift code to roster the
employee on. If left blank, any daily
shift rosters that already exists for
this day will be removed. |
|
15 |
Day 4 Custom Start Time |
5 |
The start time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
16 |
Day 4 Custom End Time |
5 |
The end time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
17 |
Day 5 Daily Shift Code |
6 |
The daily shift code to roster the
employee on. If left blank, any daily
shift rosters that already exists for
this day will be removed. |
|
18 |
Day 5 Custom Start Time |
5 |
The start time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
19 |
Day 5 Custom End Time |
5 |
The end time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
20 |
Day 6 Daily Shift Code |
6 |
The daily shift code to roster the
employee on. If left blank, any daily
shift rosters that already exists for
this day will be removed. |
|
21 |
Day 6 Custom Start Time |
5 |
The start time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
22 |
Day 6 Custom End Time |
5 |
The end time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
23 |
Day 7 Daily Shift Code |
6 |
The daily shift code to roster the
employee on. If left blank, any daily
shift rosters that already exists for
this day will be removed. |
|
24 |
Day 7 Custom Start Time |
5 |
The start time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
|
25 |
Day 7 Custom End Time |
5 |
The end time for a Custom roster. Use
with the Custom daily shift code or
leave the field blank.
In the format hh:mm. |
- 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, i.e. if the
line starts with a , then the line is
ignored. This allows for comment records in
the file.
- You can download a How to and example
files for the Rostering import from the
Documentation page.
- You can configure TNA to automatically
import these files. See
Technical Bulletin 51.
|
|
