Bulk Transaction Processing
An article reviewing PayConex bulk transaction processing
The PayConex bulk transaction processing service provides merchants or software vendors who need to process a large number of transactions on a specific date/time the capability to do so. Rather than issuing several API requests for each transaction and waiting for the API response merchants (or their software vendor) can create a bulk transaction file for processing to save time and resources.
The bulk transaction processing feature involves creating and uploading a CSV file to Bluefin's SFTP service. PayConex retrieves the file from the SFTP directory, processes the transactions, and generates a response file. This file is then uploaded via SFTP, allowing the merchant or their software to retrieve the results.
Getting Started
To upload a CSV file for bulk processing, users must log into Bluefin's SFTP system at the following URL:
Getting an SFTP account
If you need help setting up your SFTP account or integrating to utilize bulk transaction processing, please contact [email protected].
On the SFTP dashboard, users can access their directories by clicking on the Folders link in the left navigation bar.
For users set up for bulk transaction processing, the following directories will typically be available:
/Batch-Cert: This directory is used for test files./Batch-Prod: This directory is used for production files.
Each of these directories contains two subdirectories:
IN: Contains directories named according to the PayConexaccount_idthat will process the transactions.OUT: Contains directories named according to the PayConexaccount_idthat processed the transactions.
To simplify the directory structure description a bit further:
/Batch-Env/IN/account_numberdirectory is where a merchant can upload request files./Batch-Env/OUT/account_numberdirectory is where a merchant can retrieve response files.
Note
/Batch-Envis not a valid directory.Envis taking the place ofCertorProdfor the sake of the example above.
Certification Testing
Before attempting to use the bulk processing system in the production environment, it is recommended to use/test the system using a PayConex certification account. This allows merchants to familiarize themselves with the process or software vendors to test/develop their software to automate generating/uploading files without processing live transactions.
To upload a CSV file for bulk processing via a PayConex certification/test account, use the directory structure:
/Batch-Cert/IN/account_number
To access the response file for a certification/test account, use the directory structure:
/Batch-Cert/OUT/account_number
Production Processing
After successful testing in the PayConex certification environment, the following directory structures are used for live operations:
To upload a CSV file for bulk processing via a PayConex account, use the directory structure:
/Batch-Prod/IN/account_number
To access the response file for an account, use the directory structure:
/Batch-Prod/OUT/account_number
Request File Format
The file must be a comma-separated value (CSV) file.
Note on valid file extensions
To enhance security, the merchant (or software) can PGP encrypt the CSV file prior to uploading. Accepted extensions for encrypted files include .pgp, .gpg, and .asc. Although encryption is not compulsory, it is highly recommended as a best practice. If PGP encryption is not used then the file extension should be .csv.
The file structure must contain the following rows;
- Header row: The header row must be the first row of the submitted CSV file.
- Transaction row(s): The transaction rows follow after the header row.
- Footer row: The footer row must be the last row of the submitted CSV file.
Header Variables
The following columns are required to be included in the header row of a submitted file:
| Variable Name | Max Length | Type | Required | Description |
|---|---|---|---|---|
row_type | 3 | Alphabetic | Yes | This is the type of data in the row. For the header row, this value must be HDR |
account_id | 12 | Numeric | Yes | This is the Payconex account number that is issued after an account has been set up. |
api_access_key | 32 | Alphanumeric | Yes | This is the secret key provided after a Payconex account has been set up. |
batch_date | 10 | Date | Yes | Date the batch was generated. MUST be in YYYY-MM-DD format. |
Transaction Variables
The request CSV file must have a column present for each transaction variable. The variable table below lists the transaction variable associated with each column of a transaction row.
Some variables in these columns aren't required and do not need a value to be provided but for the system to properly validate the file that is uploaded a column for them must be included.
Note
If the file structure is not correct, or the file contains invalid values for a given variable then the file will be rejected and none of the transactions in it will be processed.
| Variable Name | Max Length | Type | Required | Description |
|---|---|---|---|---|
row_type | 3 | Alphabetic | Yes | This is the type of data in the row. For transaction rows, this value must be TX. |
uid | 40 | Alphanumeric | Yes | This is a unique identifier. Each row in the file MUST be unique. |
transaction_type | N/A | Enumerated | Yes | This is the type of transaction you are requesting. The following enumerated values are allowed: AUTHORIZATION: authorizes the funds on the card, but does not transfer the funds. SALE: authorizes the funds on the card and marks the transaction to be captured for settlement at the next settlement time. REFUND: refund a previous sale. If the transaction has not yet been settled, this results in a void. Otherwise, it results in a credit back to the card or bank account. Requires token_id.CREDIT: puts money onto a card or into a bank with no offsetting sale. CAPTURE: flags a previous authorization to be captured for settlement. Requires token_id.STORE: stores credit card/ach account info for later use. FORCE: forces through a transaction. A 6-digit authorization_code must also be provided. |
token_id | 12 | Numeric | Conditional | 12-digit transaction_id of a previous transaction. The token_id is used for reissues, refunds, and recurring transaction creation.Required if reissue is 1 (or true). |
method | 1 | Alphabetic | Yes | This is the method you wish to perform, with valid values being: C - Credit Card A - ACH T - Token |
tender_type | N/A | Enumerated | Yes | This is the payment tender type that you are submitting. The following enumerated values are allowed: CARD: credit, debit, or check ACH: ACH, EFT, or electronic check EBT: USDA FNS, Electronic Benefits Transfer GIFT: gift card or stored value card |
card_number | 16 | Numeric | Conditional | The card number with no spaces, dashes, or hyphens. Required if method is C. |
card_expiration | 4 | Numeric | Conditional | The card expiration date in the format of MMYY. No hyphens, dashes, spaces, or slashes. Required if method is C. |
bank_routing_number | 9 | Numeric | Conditional | The bank routing (ABA) number. Required if method is A. |
bank_account_number | 26 | Numeric | Conditional | The bank account (DDA) number. Required if method is A. |
check_number | 15 | Numeric | No | The number of the check used for electronic checks that are processed via ACH. |
ach_account_type | N/A | Enumerated | No | This is the type of bank account for ACH/electronic check transactions. It will default to checking if none is specified. The allowed values are: CHECKING: checking account SAVINGS: savings account |
ach_sec_code | N/A | Enumerated | No | The Standard Entry Class (SEC) code required for ACH/echeck transactions. If no SEC code is provided, the default that is set up for the account is used. Please note that if you provide an SEC Code here that the account is not underwritten for, the bank will decline the transaction. Acceptable values are: CCD: Cash Concentration or Disbursement. This is the default type for corporations. Requires a signature. PPD: Prearranged Payment & Deposit. Requires a signature. WEB: Web-originated, e-commerce transactions. TEL: Telephone-initiated transactions. Voice recording is required. POP: Point-of-Purchase, in-person transaction. ARC: Accounts Receivable. This is for converting a check into an electronic ACH transaction. RCK: Re-presented Check. This is used to present a declined check an additional time. DEF: This can be sent to tell PayConex to use the default ACH SEC code. Sending nothing will result in the same action. |
ach_opcode | N/A | Enumerated | No | For processor-specific ACH features. 01, 02, 03, S, R |
amount | 9 | Numeric with decimal | Yes | This is the dollar amount of the transaction. Only numbers and a single decimal are allowed. Commas are NOT allowed. The maximum amount is 999999.99 (decimal is counted in the max size). Values with no decimals and no cents are allowed. Values with only a single number after the decimal are allowed and will be assumed to have a trailing 0. |
authorization_code | 6 | Alphanumeric | Conditional | For credit/debit cards, this is the 6-digit authorization code from a previously authorized transaction. Required when transaction_type is FORCE. |
reissue | 1 | Boolean | No | If set to 1, this creates a new transaction based on the cardholder data from a previous transaction. The transaction_id from the transaction that you would like to use as a template for the reissue is required to be sent through the token_id variable. The dollar amount can be changed as well as the expiration date, name, description, and other variables. |
payment_type | N/A | Enumerated | No | Type of payment being processed. Valid values are: INSTALLMENTRECURRINGECOMMERCEMOTO |
payment_number | 100 | Numeric | No | This is the payment number. Indicating which payment in a series of payments this transaction represents. Only applicable if payment_type is INSTALLMENT for the transaction. |
total_payments | 100 | Numeric | No | This is the total number of payments. Only applicable if payment_type is INSTALLMENT for the transaction. |
group | 12 | Alphanumeric | No | Groups are flexible groups that can be used for various reasons, including: To assign transactions to a specific group for reporting/tracking. or Directing transactions to separate back-end merchant accounts or depository accounts. |
cashier | 100 | Alphanumeric | No | This is the name or id of the cashier that is submitting the transaction. This is shown within the PayConex transaction details. You may use any designation you wish. Good choices are the user name of the POS clerk, email address, or the name of the application that is connecting. |
order_id | 25 | Alphanumeric | No | This variable is used to specify an order number that can be used to identify the purchase or service. |
level2_tax | ?? | Numeric with decimal | No | Used exclusively for level 2 transactions. REQUIRED for level 2 transactions. For Visa cards, the value must be expressed as greater than 0.00, for Mastercard value may be expressed as 0.00 if desired. |
level2_zip | 10 | Numeric with hyphen | No | Used exclusively for level 2 transactions. For transactions to qualify for level 2 processing this variable is required. This value is not validated like the zip variable, any alphanumericvalue up to 10 digits is valid. |
level2_orderid | 10 | Numeric | No | Used exclusively for level 2 transactions. The merchant determines any custom alphanumeric value. |
level2_merchant_reference | 15 | Alphanumeric | No | Used exclusively for level 2 transactions. Required for level 2 transactions. The merchant determines the value. |
first_name | 50 | Alphabetic | Conditional | For credit card transactions this is the first name of the cardholder as it appears on the front of the card. It is not required for cards. For ACH transactions this is the first name of the account holder as it appears on the front of the check or bank statement. It is required by NACHA to provide first and last name. |
last_name | 50 | Alphabetic | Conditional | For credit card transactions this is the last name of the cardholder as it appears on the front of the card. It is not required for cards. For ACH transactions this is the last name of the account holder as it appears on the front of the check or bank statement. It is required by NACHA to provide first and last name. |
street_address1 | 50 | Alphanumeric | No | Street address of the card or account holder. |
street_address2 | 50 | Alphanumeric | No | Suite, apartment, or other portion of an address. |
city | 100 | Alphabetic | No | The city portion of the cardholder or accountholder address. |
state | 2 | Alphabetic | No | The two-digit state code of the cardholder or account holder's address. |
zip | 10 | Numeric with hyphen | No | The 5-digit format (or 5+4) formatted zip code of the cardholder or accountholder. Only numbers and a hyphen are allowed. For example, 12345 or 12345-1234. INTERNATIONAL: Can contain any combination of letters or numbers, and either a space or a hyphen. |
country | 2 | Alphabetic | No | This is the two-character country code for the cardholder/account holder. See iso.org for valid values. |
phone | 20 | Alphanumeric | No | This is the phone number for the cardholder/account holder. It is not sent to the processor and is stored only for your reporting use. |
email | 100 | Alphanumeric | No | This is the email address of the cardholder/account holder. It does not expect any specific format, is not sent to the processor, and is stored only for your reporting use or sending email receipts. |
ip_address | 15 | NNN.NNN.NNN.NNN | No | IP address of the client that initiated the transaction. |
custom_id | 50 | Alphanumeric | No | This is a custom identifier that can be used for any purpose you wish. For some processors (such as Paymentech), this ID is passed through to the processor and available in their reporting. This can ease syncing up reporting. |
transaction_description | 256 | Alphanumeric | No | A description of the payment. This is an open field that can be used to send any information that you wish. |
custom_data | 256 | Alphanumeric | No | An open variable. Many developers use this variable to transmit structured data formats or serialized variables. You can send through many variable=value pairs through this single variable. |
Footer Variables
The following columns are required to be included in the footer row of a submitted file:
| Variable Name | Max Length | Type | Required | Description |
|---|---|---|---|---|
row_type | 3 | Alphabetic | Yes | This is the type of data in the row. For the footer row, this value must be FTR |
batch_count | 6 | Numeric | Yes | This is the total number of transactions specified in the file. |
batch_total | 9 | Numeric with decimal | Yes | This is the total dollar amount of all transactions listed in the file. Only numbers and a single decimal are allowed. Commas are NOT allowed. The maximum amount is 999999.99 (decimal is counted in the max size). Values with no decimals and no cents are allowed. Values with only a single number after the decimal are allowed and will be assumed to have a trailing 0. |
Response File
There are two types of response files that can be returned by the system:
- If a CSV file is uploaded, validated, and processed successfully a transaction response file is placed in the corresponding
/Batch-Xxxx/OUT/account_numberdirectory. - If the CSV file is uploaded and cannot be validated an error response file is placed in the corresponding
/Batch-Xxxx/OUT/account_numberdirectory following the name conventionerror_{original file name}.csv
In this section, the focus will be on the transaction response file. In the following section, an example error file is provided so that you can see what an error file might look like.
Note on transaction response files
Receiving a response file does not necessarily mean that all transactions in the original request file were authorized. Some may be approved while others are declined so reviewing this response file is a good practice that allows a merchant or software vendor to determien if transactions were approved or declined.
The variables and values returned in the file will follow the list below:
| Variable Name | Max Length | Type | Description |
|---|---|---|---|
id | 12 | Numeric | The transaction id for the new transaction. When using tokenization, this is the transaction_id that you submit as the token_id. |
batch_id | 12 | Numeric | Will be returned for refunds. This is the original transaction_id of the Sale transaction that was refunded. |
uid | 40 | Alphanumeric | The value that was submitted as part of the request file for the given transaction row. |
transaction_timestamp | 19 | YYYY-MM-DD HH:MM:SS | This is the timestamp of the newly created transaction. |
transaction_id | 12 | Numeric | The transaction id for the resulting transaction. |
original_transaction_id | 12 | Numeric | Will be returned for refunds. This is the original transaction_id of the Sale transaction that was refunded. |
tender_type | n/a | Enumerated | This will be the same as the tender_type provided in the request. |
transaction_type | n/a | Enumerated | This is the type of transaction requested. |
card_brand | n/a | Enumerated | VISA, MASTERCARD, AMERICAN EXPRESS, DISCOVER, ACH, EBT |
transaction_amount | 9 | Numeric with decimal | The same value submitted in the request. If a partial auth is valid, this represents the actual amount authorized. |
last4 | 4 | Numeric | The last four digits of the card number or Primary Account Number (PAN). For ACH, it is the last four digits of the account number. |
card_expiration | 4 | Numeric | The month and year of the card expiration date in the format MMYY. For example, 0135 for January 2035. |
authorization_code | 6 | Alphanumeric | This is the auth code returned by the processor. |
authorization_message | 50 | Alphanumeric | APPROVED or the auth message from the processor (e.g. AUTH DECLINED 200). |
avs_response | 1 | Enumerated | A single letter address verification response. DFJMQVXY - Address and ZIP code match LWZ - ZIP code match, address is wrong ABOP - Address match, ZIP code is wrong KN - No match, address and ZIP is wrong U - No data from issuer/bank R - AVS System unable to process S - Issuing bank does not support AVS E - Error, AVS not supported for your business C - Invalid address and ZIP format (International) I - Address not verifiable (International) G - Global non-verifiable address (International) ? - Unrecognized codes (none of the above) _ - No AVS data (blank) |
custom_id | 50 | Alphanumeric | The value that was submitted as part of the request file for the given transaction row. |
error | 1 | Boolean | True for error conditions or decline. |
error_code | 5 | Numeric | 0 for no error, > 0 for error number. |
error_message | ? | Alphanumeric | DECLINED or textual description of other API errors (e.g., “Must send card_number” or “Invalid bank_account_number”). |
transaction_approved | 1 | Boolean | True for approved. |
ip_address | 15 | NNN.NNN.NNN.NNN | The value that was submitted as part of the request file for the given transaction row. |
first_name | 50 | Alphabetic | The value that was submitted as part of the request file for the given transaction row. |
last_name | 50 | Alphabetic | The value that was submitted as part of the request file for the given transaction row. |
custom_data | 256 | Alphabetic | The value that was submitted as part of the request file for the given transaction row. |
transaction_description | 256 | Alphabetic | The value that was submitted as part of the request file for the given transaction row. |
Example Request and Response Files
Request File Example
This is an example of what a request file might look like. Notice the empty columns for the optional variables that are still present as part of the transaction rows.
HDR,230614966801,32d42f0f0b6c89616d42aed8c96801e6,2022-11-30
TX,Nov3020223-01,STORE,,A,ACH,,,123123123,123456789,,,,,0.00,,,,,,,,,,,,,Test,Test,,,,,,,,,,,,
TX,Nov3020223-02,SALE,,C,CARD,4159288888888882,1230,,,,,,,5.00,,,,,,,,,,,,,Test,Test,,,,,,,,,,,,
TX,Nov3020223-03,SALE,000000214606,T,CARD,,,,,,,,,2.00,,,,,,,,,,,,,Test,Test,,,,,,,,,,,,
FTR,3,7.00
Response File Example
Below are two example response files, one where the original file was processed successfully and another where the file did not pass validation due to an incorrect value in one of the variables.
Successful File
id,batch_id,uid,transaction_timestamp,transaction_id,original_transaction_id,tender_type,transaction_type,card_brand,transaction_amount,last4,card_expiration,authorization_code,authorization_message,avs_response,custom_id,error,error_code,error_message,transaction_approved,ip_address,first_name,last_name,custom_data,transaction_description
6,135646,,,,,,,,,,,,,,,1,20005,"EXCEPTION Account: 220614966801 - Transaction ID: 0",0,,,,,
53426,135646,Nov3020223-02,"2023-05-30 16:20:02",000000214626,,CARD,SALE,VISA,5.00,8882,1230,948129,APPROVED,,,0,0,,1,,Test,Test,,
53446,135646,Nov3020223-03,"2023-05-30 16:20:05",000000214646,,CARD,SALE,VISA,2.00,8882,1225,948133,APPROVED,,,0,0,,1,,Test,Test,,
Error File
id,batch_id,uid,field_name,message,row_number
0000003826,0000135606,Nov3020223-02,General,"UID is duplicated, value must be unique.",4
Timing
The system checks the IN folders for new files every minute however the time it takes to complete the entire batch file process can vary greatly and involves many factors such as:
- The number of transactions in the file.
- The back-end processor used for authorizing the transactions.
- The tender type of the transaction(s) (whether they are credit card or ACH transactions)
- How busy the server actually is when processing the files. If you upload several at a time, the server will attempt to process them at the same time and thus might be a bit slower in accomplishing the task.
Currently, from rough estimates, 1000 credit card transactions take approximately 20 minutes to process, and 1000 ACH ones take approximately 7 minutes to process.
Updated over 1 year ago