|
Export Formats Explained |
  
|
|
Export Formats Explained |
|
This page explains the export file created by e-PayDay® when a pay is finalised. It is relevant to Sage Pastel Evolution Accounting, Sage Line 50, Sage Pastel Partner 2005 and Sage Pastel Xpress 2005.
For information on how to configure e-PayDay® to export to your accounting package please refer to Creating Payrun Export File
Sage Pastel Evolution Accounting
Export File name – PAYDAYEX.TXT
File Format - Comma Separated, Quote Delimited text (.txt) file
Balancing – File totals should balance to Zero (0)
Below is an extract of the PAYDAYEX.TXT export file e-PayDay® creates when a pay is finalised.
Each Cost Centre/Location combination will have separate entries; eg, in the following example there are Two Cost Centre/ Location combinations with hours allocated for Award Rate; note field two, 1 1 1 and 2 2 1.
PAYDAYEX.TXT
Field |
Name |
Notes |
*Ref |
1 |
Date |
Character, DD/MM/YYYY |
A |
2 |
Description |
Cost Center ID, Location ID and Pay Type ID or Total (Tax, Net, SGC). Print the relevant report in e-PayDay® to find the description of the ID # |
B |
3 |
Reference |
'PAYROLL' |
C |
4 |
Amount |
Numeric |
D |
5 |
Use Tax |
Y/N, 'Y'For Tax Totals, 'N' for all others |
E |
6 |
Tax Type |
W2 for Tax Totals, W1 for all others |
F |
7 |
Tax Account |
Always Zero (0) |
G |
8 |
Tax Amount |
Always Zero (0) |
H |
9 |
Project Character |
Alpha Numeric 20 Characters |
I |
10 |
Account |
GL Account Number Alpha Numeric, 41 Characters |
J |
11 |
Debit or Credit |
Y=Debit, N=Credit |
K |
* Ref: The Letters in the description table above match the column headers in the pictured .CSV file for your reference.
# Field 2 Description, Cost Center ID, Location ID and Pay Type ID or Total (Tax, Net, SGC).
Print the relevant report in e-PayDay® to find the description of the ID
Cost Centre List Location / Department List; Pay Rates List; Deductions List; Allowance List
To test if Sage Pastel Evolution Accounting export file balances
View the file in Excel: in L1 enter '=IF(K1="N",D1,-D1)' and replicate for each row, then sum the results.
Then sum column L, it should be zero or very small (much less than 1 cent).
Sage Line 50
Export File name – PAYDAYEX.CSV
File Format - Comma Separated Quote Delimited .csv file
Balancing – File totals should balance to Zero (0)
Below is an extract of the PAYDAYEX.CSV export file e-PayDay® creates when a pay is finalised.
"JC","52134655","52134655",999,"01/11/2006","Salary","Weekly November Salary",817,"T41",0
"JD","12345678","12345678",999,"01/11/2006","Salary","Weekly November Salary",171,"T42",0
"JD","12345678","12345678",999,"01/11/2006","Salary","Weekly November Salary",646,"T90",0
"JD","12345678","12345678",999,"01/11/2006","Salary","Weekly November Salary",73.53,"T90",0
"JC","12345678","12345678",999,"01/11/2006","Salary","Weekly November Salary",73.53,"T90",0
PAYDAYEX.CSV File Structure
Field |
Name |
Notes |
Ref* |
1 |
Transaction Type |
JC (Journal Credit) or JD (Journal Debit) |
A |
2 |
Bank Account Ref |
General Ledger Account Number (8 Numeric Characters) |
B |
3 |
Nominal Code |
General Ledger Account Number (8 Numeric Characters) |
C |
4 |
Department Number |
Integer 1-999 |
D |
5 |
Date |
Transaction Date, DD/MM/YYYY E |
E |
6 |
Transaction Reference |
'Salary' |
F |
7 |
Transaction Details |
Pay Frequency, Month, Reference |
G |
8 |
Net Amount |
Amount, Numeric |
H |
9 |
Tax Code |
T41 – Gross Wages, T42 - PAYG, T90 - Rest |
I |
10 |
Tax Amount |
Always Zero (0) |
J |
* Ref: The Letters in the description table above match the column headers in the pictured .CSV file for your reference.
To test if Sage line 50 export file balances.
View the file in Excel; in K1 enter '=IF(A1="JC",H1,-H1)' and replicate for each row, then sum the results.
The sum of column K it should be zero or very small (much less than 1 cent)
Sage Pastel Partner 2005
Sage Pastel Xpress 2005
Export File name – PAYDAYEX.TXT
File Format - Comma Separated Quote Delimited text (.txt) file
Balancing – File totals should balance to Zero (0)
Below is an extract of the PAYDAYEX.TXT export file e-PayDay® creates when a pay is finalised
Each Cost Centre/Location combination will have separate entries; eg, in the following example there are Two Cost Centre/ Location combinations with hours allocated for Award Rate.
1,"24/01/2007","G","12345678","Logisoft","Award Rate $18.709788ph",-748.39,0,0,"A","12345","0000000",0,0,0,0,0,-748.39
1,"24/01/2007","G","12345678","Logisoft","Car Allowance",-.55,0,0,"A","12345","0000000",0,0,0,0,0,-.55
1,"24/01/2007","G","12345678","Logisoft","Overtime Meal Allowance",-12.5,0,0,"A","12345","0000000",0,0,0,0,0,-12.5
1,"24/01/2007","G","12345678","Logisoft","MBF Single",10,0,0,"A","12345","0000000",0,0,0,0,0,10
1,"24/01/2007","G","12345678","Logisoft","Sal. Sac. - Sun S",18.71,0,0,"A","12345","0000000",0,0,0,0,0,18.71
1,"24/01/2007","G","12345678","Logisoft","Union Fees, BLF",2.95,0,0,"A","12345","0000000",0,0,0,0,0,2.95
1,"24/01/2007","G","12345678","Logisoft","Net",729.78,0,0,"A","12345","0000000",0,0,0,0,0,729.78
1,"24/01/2007","G","12345678","Logisoft","Tax",224.5,0,0,"A","12345","0000000",0,0,0,0,0,224.5
1,"24/01/2007","G","12345678","Logisoft","SGC",67.35,0,0,"A","12345","0000000",0,0,0,0,0,67.35
1,"24/01/2007","G","12345678","Logisoft","Contra SGC",-67.35,0,0,"A","12345","0000000",0,0,0,0,0,-67.35
PAYDAYEX.TXT File Structure
Field |
Name |
Notes |
*Ref |
1 |
Period |
Numeric, 1-13 eg 5 |
A |
2 |
Date |
Character, DD/MM/YYYY |
B |
3 |
G |
General Ledger |
C |
4 |
Account Number |
7 Characters |
D |
5 |
Reference |
Logisoft', 8 Characters |
E |
6 |
Description |
Character, Pay Type (eg Award Rate), 'Tax', 'SCG' or 'Net' |
F |
7 |
Amount |
12.2 characters, negative value is Cr, positive is Dr |
G |
8 |
Tax Type |
Numeric, Always Zero (0) |
H |
9 |
Tax Amount |
Numeric, Always Zero (0) |
I |
10 |
Open Item Type |
Character, Always 'A' |
J |
11 |
Cost Code |
Alpha Numeric 5 Characters |
K |
12 |
Contra Account |
Character, Always Zero's (0000000) |
L |
13 |
Exchange Rate |
Numeric, Always Zero (0) |
M |
14 |
Bank Exchange Rate |
Numeric, Always Zero (0) |
N |
15 |
Batch ID |
Numeric, Always Zero (0) |
O |
16 |
Discount Tax Type |
Numeric, Always Zero (0) |
P |
17 |
Discount Amount |
Numeric, Always Zero (0) |
Q |
18 |
Home Amount |
Numeric,12.2 characters same as item 7 - Amount |
R |
8* Ref: The Letters in the description table above match the column headers in the pictured .CSV file for your reference.
To test if Sage Pastel Partner/Xpress 2005 export file balances
Change the name of the paydayex.txt file to paydayex.csv and open it Excel.
The sum of column G it should be zero or very small (much less than 1 cent)
This information and all further technical advice is based on our present knowledge and experience. However, it implies no liability or other legal responsibility on our part, including with regard to existing third party intellectual property rights, especially patent rights. In particular, no warranty, whether express or implied, or guarantee of product properties in the legal sense is intended or implied. We reserve the right to make any changes according to technological progress or further developments. The customer is not released from the obligation to conduct careful inspection and testing of incoming
information. Performance of the advice described herein should be verified by testing, which is the sole responsibility of the customer. Reference to trade names used by other companies is neither a recommendation, nor does it imply that similar products could not be used.