Overview

Importing ACH transactions from Excel into ACH Universal Online is simplified by the mapping process. This guide walks you through best practices for mapping, required and optional fields, and tips to ensure smooth ACH file creation. Whether you're processing payroll, vendor payments, or collections, proper mapping ensures accuracy and compliance with Nacha standards.

Table of Contents

1. Common Mapping Practices and Tips
2. Commonly Mapped Fields if Profiles are Not Stored in ACH Universal Online
3. Mapping the Amount of the Transaction
4. Mapping Fields if Profiles are Stored in ACH Universal Online

1. Common Mapping Practices and Tips:

*Not all columns in your Excel/CSV import file need to be mapped.

For example, if your source file has additional fields (such as address, phone numbers, etc.), they can be excluded from the ACH file by not selecting a mapping header (Skip-Not Needed).

*The order of the columns/fields does not matter.

*The header row can be included. It will be stripped out and marked as invalid. 

*Columns of data in your source file containing bank account numbers should be formatted as 'text' to allow for leading zeros. 

2. Commonly Mapped Fields if Profiles are NOT Stored in ACH Universal Online:

Required fields

• Name 

This is the ACH recipient that you are paying or collecting from. This name does not need to be an exact match to the bank account name.

• ABA Routing Number 

The 9-digit number assigned to each bank.  By default, the system performs a checksum verification on each ABA number and transactions with invalid numbers are excluded from the file.
* If Excel is dropping the leading zeros in your routing number column, our software will automatically restore them as routing numbers are always 9 digits. 

• Bank Account Number

This is the bank account number of the party that you are paying or collecting from.  Do not include spaces, dashes or other non-numeric data.  If leading zeros are included, please including them on the import file by formatting the column as 'text.'

• Amount (Please refer to Amount section #3 below)

Optional Fields:

• Date - You can map the effective/transaction date.  If the date is not mapped, ACH Universal Online will use the Effective Date setting in the ACH Setup>Advanced Setup (default is next business day).
Notes: 

If you do not include a date on your import file, ACH Universal Online will take into account weekends, but not holidays.
If you map an invalid date, for example, February 30th, the transaction will be excluded from the file's record.

• Checking / Savings - The system defaults to checking if not mapped.  If you choose to map this field, ACH Universal Online recognizes C, CHK, Checking for Checking - and S, SAV, Savings for Savings.

• ID Number - Certain banks require an ID Number (JP Morgan Chase). This is a 15-character alphanumeric field that usually is shown on recipients' bank statements. 

• SEC Code - If this code is not mapped, the system will use the default SEC Code from ACH File Setup, with the default typically being PPD (personal/payroll) or CCD (corporations/vendors).  

• Transaction Code - This is a two-digit code identifying the various types of debit and credit entries.
Note: Do not map if you are using Amount_Reverse_Sign. 
The most common examples include: 22 (Credit checking account), 27 (Debit checking account), 32 (Credit savings account), 37 (Debit savings account).

• Addenda Record - Payment related information up to 80 characters to be included in a single addenda record (705) such as CCD and PPD (requires our corporate license). 

Note: 

While ACH Universal will create addenda record in accordance with Nacha rules, this information will not show on a typical business or personal (recipient's) bank statement.  

If you want to send remittance information, we recommend using our email feature.

• Email - Remittance information will be emailed to this address (requires our corporate license). 

Note: 

The email address is not included in the file.  Setup of our email feature is required.

3. Mapping the Amount of the Transaction

Golden Rule:

Importing negative amounts decreases YOUR bank account because you are paying funds out (crediting vendors or employees)

Importing positive amounts increases YOUR bank account because you are collecting funds (debiting customers).

If you are importing a file with BOTH credits and debits, map the field as 'Amount.'

If your import file lists all amounts as positive values, and you are paying funds out (crediting), select 'Amount Reverse Sign' for the header mapping in ACH Universal Online. This automatically converts the amounts to the correct negative values, so you don’t need to modify your Excel file.

4. Mapping Fields if Profiles ARE Stored in ACH Universal Online

If you have stored profiles in ACH Universal Online, the transaction import will be very simple. The only required fields are an amount and a lookup field. This lookup field is either the name or the ID number.

Here is an example of a transaction import file when profiles are stored in ACH Universal:

*NOTE: The Names on the transaction import will need to match EXACTLY with your stored profile names. 

When importing this Excel file into ACH Universal Online, the header fields will need to be mapped differently.

Instead of selecting 'Name' to map the header fields, select 'Profile Name:'

*Note: If your preferred look up field is the ID Number, you will need to change the settings from ACH Set Up (side bar)>Advanced Set Up >Import Preferences>Profile Lookup>Select ID Number.

Frequently Asked Questions (FAQs)