Payment card import formats
Overview
This topic details the formats of the CSV import files which are currently supported by Zetadocs Expenses for importing payment card transactions. It is intended to help identify the fields which you must include when downloading a CSV file from you bank and to troubleshoot format issues when importing a given file.
This section also covers;- •Detailed file formats (available from the Transaction Import File Format option) |
oStandard Format AM1 - (American Express (US)) oStandard Format AP1 - (AirPlus Corporate Card – MasterCard) oStandard Format BC1 - (Barclaycard - MasterCard (UK)) oStandard Format BC2 - (Barclaycard 2 - MasterCard (UK)) oStandard Format HS1 - (HSBC - Visa) oStandard Format LB1 - (Lloyds - MasterCard) oStandard Format NW1 - (NatWest - MasterCard) oStandard Format RB1 - (Royal Bank of Scotland - MasterCard) oStandard Format RB2 - (Royal Bank of Scotland 2 - MasterCard) |
The following items affect how the file is processed:
Property |
Notes |
|---|---|
File format |
“CSV with column header” – file includes a header line, and columns are identified by the header text “CSV with fixed columns” – columns are identified by their position, with first column as number 1 etc. |
Header line number |
The line number for the header line, starting from 1. Lines before that line will be ignored. This is used for file formats which include a title or other information at the top of the file before the start of the transaction data. |
Payment line match string |
The text which is used to identify the monthly card settlement – eg “PAYMENT RECEIVED – THANK YOU”. Lines which contain that text are ignored when importing the file. |
Payment line match field |
The field name containing the payment line match string, typically the vendor name field. |
Line properties
The following data may be read from the file for each transaction.
Property |
Notes |
|---|---|
Card number |
This is typically a masked card number, giving the last few digits of the card number only. The digits are matched to the card suffix specified for each payment method of that type to identify which payment method the transaction is for. |
Cardholder first name |
First name of cardholder |
Cardholder last name |
Last name of cardholder |
Transaction date |
Date when payment transaction occurred. |
Posting date |
Date when transaction was posted (completed by bank). |
Original currency code |
Currency of the original transaction (eg foreign currency for payments made in another country) |
Original currency amount |
Amount in the original currency for the transaction |
Base currency amount |
Amount in the home currency for the card account. The system assumes this is the same as the currency for the Zetadocs Expenses account. Refunds are either indicated with a negative amount or by using a separate Sign indicator field. |
Sign indicator |
This field is used to indicate whether the transaction is a payment (debit) or refunds (credit), for formats which do not use a negative amount for a refund. The field values are specified in the Payment indicator text and Refund indicator text settings. |
Transaction reference |
This is a unique ID allocated by the bank, and may be used to identify the transaction to the bank if required. |
Vendor category |
The category gives the merchant type, typically as a standard 4 digit Merchant Category Code (MCC) or a short text description. |
Vendor name |
Name of merchant or business which received the payment |
Vendor details 1 |
First line of vendor address or other vendor details |
Vendor details 2 |
Second line of vendor address or other vendor details |
Vendor details 3 |
Third line of vendor address or other vendor details |
Vendor details 4 |
Fourth line of vendor address or other vendor details |
Vendor details 5 |
Fifth line of vendor address or other vendor details |
Detailed File Formats
Standard Format AM1
Previous name: American Express (US)
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with fixed columns (no header) |
Header line number |
n/a |
Payment line match string |
PAYMENT RECEIVED - THANK YOU |
Payment line match field |
Vendor name |
The following data may be read from the file for each transaction.
Property |
Column number (LH column = 1) |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Card number |
5 |
XXXX-XXXXXX-12345 |
|||
Cardholder first name |
4 |
John Doe |
|||
Transaction date |
1 |
MM/dd/yyyy ddd |
09/21/2016 Wed |
2 spaces before day name |
|
Base currency amount |
8 |
123.45 |
|||
Vendor name |
3 |
ACME PARKING PHOENIX AZUS |
|||
Vendor details 1 |
3 |
ACME PARKING PHOENIX AZUS |
Standard Format AP1
Previous name: AirPlus Corporate Card – MasterCard
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with column header |
Header line number |
1 |
The following data may be read from the file for each transaction.
Property |
Column heading |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Card number |
Account No |
5587 2121 1234 1234 |
|||
Transaction date |
Sales Date |
dd.MM.yyyy |
23.12.2017 |
||
Posting date |
Invoice Date |
dd.MM.yyyy |
24.12.2017 |
||
Original currency code |
Sales Currency |
AED |
|||
Original currency amount |
Net Amount (SC) |
123.45 |
|||
Base currency amount |
Gross Amount (BC) |
234.56 |
Negative value = credit (refund) |
||
Transaction reference |
Invoice No |
S0 39174206988 |
|||
Vendor name |
Merchant |
Emarat-Bridge(3770)C/s |
|||
Vendor details 1 |
Routing |
Dubai ARE |
Standard Format BC1
Previous name: Barclaycard - MasterCard (UK)
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with column header |
Header line number |
1 |
Payment line match string |
DIRECT DEBIT PAYMENT THANK YOU |
Payment line match field |
Vendor name |
The following data may be read from the file for each transaction.
Property |
Column heading |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Card number |
ACC.ACCOUNT NUMBER |
XXXXXXXX12345678 |
|||
Cardholder first name |
ACC.FIRST NAME |
J P |
|||
Cardholder last name |
ACC.LAST NAME |
DOE |
|||
Transaction date |
FIN.TRANSACTION DATE |
dd/MM/yyyy |
25/02/2020 |
||
Posting date |
FIN.POSTING DATE |
dd/MM/yyyy |
26/02/2020 |
||
Original currency code |
FIN.ORIGINAL ISO CURRENCY CODE SYMBOL |
EUR |
|||
Original currency amount |
FIN.ORIGINAL CURRENCY AMOUNT |
34.00 |
|||
Base currency amount |
FIN.NET TRANSACTION AMOUNT |
30.20 |
This format does not use the sign indicator column, so should only be used if the import file does not include refunds, or if refunds have negative values. Any refunds which have positive values for the amount will be imported as payment transactions. |
||
Vendor name |
FIN.TRANSACTION DESCRIPTION |
WWW.MESSEFRANKFURT.COM |
|||
Vendor details 1 |
MCH.CITY NAME |
FRANKFURT AM |
Standard Format BC2
Previous name: Barclaycard 2 - MasterCard (UK)
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with column header |
Header line number |
1 |
Payment line match string |
DIRECT DEBIT PAYMENT THANK YOU |
Payment line match field |
Vendor name |
The following data may be read from the file for each transaction.
Property |
Column heading |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Card number |
ACC.ACCOUNT NUMBER |
XXXXXXXX12345678 |
|||
Cardholder first name |
ACC.FIRST NAME |
J P |
|||
Cardholder last name |
ACC.LAST NAME |
DOE |
|||
Transaction date |
FIN.TRANSACTION DATE |
dd/MM/yyyy |
25/02/2020 |
||
Posting date |
FIN.POSTING DATE |
dd/MM/yyyy |
26/02/2020 |
||
Original currency code |
FIN.ORIGINAL ISO CURRENCY CODE SYMBOL |
EUR |
|||
Original currency amount |
FIN.ORIGINAL CURRENCY AMOUNT |
34.00 |
|||
Base currency amount |
FIN.NET TRANSACTION AMOUNT |
30.20 |
Payments and refunds both given as positive values. |
||
Sign indicator |
FIN.NET TRANSACTION AMOUNT SIGN |
“D” for payment (debit) “C” for refund (credit) |
D |
||
Vendor name |
FIN.TRANSACTION DESCRIPTION |
WWW.MESSEFRANKFURT.COM |
|||
Vendor details 1 |
MCH.CITY NAME |
FRANKFURT AM |
Standard Format HS1
Previous name: HSBC - Visa
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with column header |
Header line number |
1 |
The following data may be read from the file for each transaction.
Property |
Column heading |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Card number |
Masked Card Number |
4555-98XX-XXXX-1234 |
|||
Cardholder first name |
First Name |
John Mark |
|||
Cardholder last name |
Last Name |
Doe |
|||
Transaction date |
Transaction Date |
yyyy-MM-dd |
2017-02-05 |
||
Posting date |
Posting Date |
yyyy-MM-dd |
2017-02-06 |
||
Original currency code |
Currency |
EUR |
|||
Original currency amount |
Amount |
20.6600 |
|||
Base currency amount |
Consolidated Amount |
17.450000000000 |
Negative value = credit (refund)? |
||
Vendor name |
Merchant Name |
FERMIN BRASERO |
|||
Vendor details 1 |
Merchant City |
MADRID |
Standard Format LB1
Previous name: Lloyds - MasterCard
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with column header |
Header line number |
2 |
The following data may be read from the file for each transaction.
Property |
Column heading |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Card number |
ACC.ACCOUNT NUMBER |
XXXXXXXXXXXX1234 |
|||
Cardholder first name |
ACC.FIRST NAME |
JOHN MARK |
|||
Cardholder last name |
ACC.LAST NAME |
DOE |
|||
Transaction date |
FIN.TRANSACTION DATE |
dd/MM/yyyy |
21/05/2018 |
||
Posting date |
FIN.POSTING DATE |
dd/MM/yyyy |
22/05/2018 |
||
Base currency amount |
FIN.TRANSACTION AMOUNT |
Negative value = credit (refund) |
|||
Vendor category |
MCH.MCC DESCRIPTION |
||||
Vendor name |
MCH.MERCHANT NAME - ORIGINAL |
||||
Vendor details 1 |
MCH.CITY NAME |
||||
Vendor details 2 |
MCH.EMR.D&B STATE / PROVINCE |
||||
Vendor details 3 |
MCH.EMR.D&B POSTAL CODE |
||||
Vendor details 4 |
MCH.COUNTRY CODE |
Standard Format NW1
Previous name: NatWest - MasterCard
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with column header |
Header line number |
2 |
Payment line match string |
(This is set to a value which is specific to a single customer) |
Payment line match field |
Cardholder name |
The following data may be read from the file for each transaction.
Property |
Column heading |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Card number |
ACC.ACCOUNT NUMBER |
XXXXXXXXXXXX1234 |
|||
Cardholder first name |
ACC.ACCOUNT NAME |
JOHN DOE |
|||
Transaction date |
FIN.TRANSACTION DATE |
MM/dd/yyyy |
04/28/2020 |
||
Posting date |
FIN.POSTING DATE |
MM/dd/yyyy |
04/29/2020 |
||
Base currency amount |
FIN.TRANSACTION AMOUNT |
123.45 |
|||
Vendor name |
MCH.MERCHANT NAME |
ACME PARKING |
|||
Vendor details 1 |
MCH.CITY NAME |
BASINGSTOKE |
|||
Vendor details 2 |
MCH.REGION/COUNTY |
GBR |
|||
Vendor details 3 |
MCH.MERCHANT POSTCODE |
RG24 8NE |
|||
Vendor category |
MCH.MERCHANT CATEGORY CODE (MCC) |
7523 |
Standard Format RB1
Previous name: Royal Bank of Scotland - MasterCard
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with column header |
Header line number |
1 |
The following data may be read from the file for each transaction.
Property |
Column heading |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Transaction date |
Transaction Date |
dd/MM/yyyy |
26/08/2016 |
||
Posting date |
Posting Date |
dd/MM/yyyy |
27/08/2016 |
||
Base currency amount |
Billing Amount |
123.45 |
This format does not use the sign indicator column, so should only be used if the import file does not include refunds, or if refunds have negative values. Any refunds which have positive values for the amount will be imported as payment transactions. |
||
Transaction reference |
Reference Number |
'12345678912345678912345 |
|||
Vendor name |
Merchant Name |
CAZOOMI |
|||
Vendor details 1 |
Merchant Town |
4154123456 |
|||
Vendor details 2 |
Merchant County |
CA |
|||
Vendor details 3 |
Merchant PostCode |
94115 |
|||
Vendor category |
SICVendor category |
4816 |
Standard Format RB2
Previous name: Royal Bank of Scotland 2 - MasterCard
The following items affect how the file is processed:
Property |
Details |
|---|---|
File format |
CSV with column header |
Header line number |
1 |
The following data may be read from the file for each transaction.
Property |
Column heading |
Required? |
Format |
Example |
Notes |
|---|---|---|---|---|---|
Transaction date |
Transaction Date |
dd/MM/yyyy |
26/08/2016 |
||
Posting date |
Posting Date |
dd/MM/yyyy |
27/08/2016 |
||
Base currency amount |
Billing Amount |
123.45 |
Payments and refunds both given as positive values. |
||
Sign indicator |
Debit/Credit Flag |
“D” for payment (debit) “C” for refund (credit) |
D |
||
Transaction reference |
Reference Number |
'12345678912345678912345 |
|||
Vendor name |
Merchant Name |
CAZOOMI |
|||
Vendor details 1 |
Merchant Town |
4154123456 |
|||
Vendor details 2 |
Merchant County |
CA |
|||
Vendor details 3 |
Merchant PostCode |
94115 |
|||
Vendor category |
SICVendor category |
4816 |
