Skip to content
logo Knowledgebase

What is the file layout for batch and clocking card import files using Normal Batches?

Created on  | Last modified on 

Summary

This article provides you with the file layout that is required to used when creating a clocking card or batch import file using Normal Batches for Sage VIP Classic, Sage VIP Premier and Sage Business Cloud Payroll Professional.

Description

If you make use of a clocking system or you wish to use data compiled in an Excel sheet, you can follow the guidelines provided here on how to import those hours or amounts into the payroll system.

The process saves users from having to manually capture the data per employee record. Import the data easily as long as it is in the correct file layout.

Resolution

 NOTE: A batch layout must be defined prior to importing data to your payroll. Click here for more details. 

In order to import data from an Excel sheet or from a clocking system, the import file has to be in a very specific format.

File type

  • The file type of the import file must be .TXT or .ASC

File layout

  • Detail Records
Field Description Used for From - To Length Numeric / Alphanumeric Value
Record ID To indicate a Detail record 01 - 01 1 AN D
Company Code To link to correct Company 02 - 04 3 N  
Batch Number To link to correct Batch Layout defined in Payroll 05 - 05 1 AN  
Employee Code To link to correct Employee 06 - 13 8 AN  
Value 1 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 14 - 24 11 N  
Sign 1 For Value 1, negative (-) or positive (+) 25 - 25 1 AN + / -
Value 2 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 26 - 36  11 N  
Sign 2 For Value 12 negative (-) or positive (+) 37 - 37 1 AN + / -
Value 3 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 38 - 48 11 N  
Sign 3 For Value 12 negative (-) or positive (+) 49 - 49 1 AN + / -
Value 4 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 50 - 60 11 N  
Sign 4 For Value 12 negative (-) or positive (+) 61 - 61 1 AN + / -
Value 5 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 62 - 72 11 N  
Sign 5 For Value 12 negative (-) or positive (+) 73 - 73 1 AN + / -
Value 6 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 74 - 84 11 N  
Sign 6 For Value 12 negative (-) or positive (+) 85 - 85 1 AN + / -
Filler Not used 86 - 98 12 AN Spaces
Record Terminator To indicate End of Record 99 - 99 1 AN Z

 

  • Trailer Record
Field Description Used for From - To Length Numeric / Alphanumeric Value
Record ID To indicate a Detail record 01 - 01 1 AN T
Filler Not used 02 - 13 12 AN Spaces
Total 1 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 14 - 24 11 N  
Sign 1 For Value 1, negative (-) or positive (+) 25 - 25 1 AN + / -
Total 2 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 26 - 36  11 N  
Sign 2 For Value 12 negative (-) or positive (+) 37 - 37 1 AN + / -
Total 3 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 38 - 48 11 N  
Sign 3 For Value 12 negative (-) or positive (+) 49 - 49 1 AN + / -
Total 4 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 50 - 60 11 N  
Sign 4 For Value 12 negative (-) or positive (+) 61 - 61 1 AN + / -
Total 5 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 62 - 72 11 N  
Sign 5 For Value 12 negative (-) or positive (+) 73 - 73 1 AN + / -
Total 6 Financial value / Units / Hours and minutes (no decimal sign) e.g 10.50 will reflect as 00000001050 74 - 84 11 N  
Sign 6 For Value 12 negative (-) or positive (+) 85 - 85 1 AN + / -
Filler Not used 86 - 98 12 AN Spaces
Record Terminator To indicate End of Record 99 - 99 1 AN Z

 

 EXAMPLE: See below an example of the import file: 

Image