One of the questions we're asked most frequently at ACH Pro is if we can convert Excel files to NACHA format. The answer is yes!
In this guide, we will walk you through how to convert an Excel (or CSV) file to a NACHA file using ACH Pro. We'll explain what fields are required to create a NACHA file, how to prepare your Excel file, and how to use the ACH Pro import tool.
Prerequisites
If you want to follow along in ACH Pro, you will need to be signed up for at least a free Starter plan.
A basic understanding of how ACH transfers work and the information required to create a transaction
Familiarity with Microsoft Excel (or other spreadsheet editors like Google Sheets) and spreadsheet files
What Info Is Required For An ACH Transaction?
There are 6 fields required to create an ACH transaction. You should already be collecting the first four fields from receivers as part of your ACH signup or authorization process. Amount and Entry Type will be determined by the purpose of the transaction such as payroll, accounts receivable, loan payment, etc.
Required Field | Description |
Account number | The account number for the receiver's bank account |
Routing number | The nine-digit routing number for the receiver's bank account |
Account Type | The type of the receiver's account: CHECKING, SAVINGS or LOAN |
Legal Name | The legal name of the receiver |
Amount | The amount of the transaction |
Entry Type | Should the receiver's account balance increase (CREDIT) or decrease (DEBIT) |
There are also 3 optional fields that you can use in specific cases:
Optional Field | Description |
Unique ID | A unique ID you can associate with a transaction in a NACHA file for your own reporting purposes. Up to 15 characters. |
Addendum | Up to 80 characters of remittance information for the receiver |
Batch ID | (Advanced) Used to group transactions into different batches within the NACHA file. For example, transactions with batch ID "payroll" would be in one batch, and transactions with batch ID "AP" would be in another. |
Preparing the Excel file for conversion to NACHA
Now that we have an idea of what info is required, we can create an Excel file. You can create this file from scratch or use an existing file containing the required fields above.
A couple things to note when creating the Excel file:
The file can have header and footer rows, you will be able to skip these during the import process later.
Amounts are in dollars and cents (for example. 100.75).
Some routing numbers have leading zeros like in our example file below. Excel displays a green arrow in the top left corner of these cells indicating they are stored as text. This is important, otherwise the leading zeros would be dropped and the routing numbers would be incorrect.
Account Type must be one of CHECKING, SAVINGS or LOAN (case sensitive).
Entry Type must be one of CREDIT or DEBIT (case sensitive).
Example Excel File
Import the file into ACH Pro
If you have already signed up for ACH Pro, login, click Create File in the left menu and then click From Import. If you don't have an ACH Pro account, you can sign up for a free ACH Pro account here.
If you've used ACH Pro before, you may have existing import templates available. Import templates allow you to quickly import files that have the same column structure. If you're new or don't have any templates, click + Import in the top right corner.
Now select the file you would like to import and click Upload.
The data in your file will be displayed, including any errors. ACH Pro will guess which columns in your file apply to the fields we discussed above. Review each column to make sure it is mapped to the correct field.
A couple notes about the import tool:
Import fields are mapped using the dropdowns at the top of each column.
Required fields have an asterisk after their name and will be listed beside Missing fields if not selected for any column.
Icons may appear in cells or besides rows, indicating either an error or pending validation. You can hover over the icon with your mouse to see its message.
You can filter rows by selecting an option in the Show dropdown. For example, to view only rows with errors, select Errors from the list.
You can Skip a record by checking the box in the Skip column. This is useful for headers and footers, as well as any other records you don't want to include.
You can edit a cell by double clicking it. After making changes a question mark icon will appear indicating that it hasn't been validated. Click the Validate button after you have made changes, this will check your changes and let you know if any errors exist.
The Submit button won't appear until you have clicked Validate at least once. When you've finished making changes and run validation, you can submit the import.
After submitting the import, you will be presented with a results screen. Click Finish creating ACH file to move on to the last step.
You can also:
View the number of records that were successful, skipped or that failed.
View a list of errors or warnings from the import.
Create or update an import template using the columns you just mapped.
Finalize the ACH File
Now that the transactions are imported, we need to add a few more details to complete the file:
Enter a name for the ACH file.
Select an ODFI. This is the bank you will upload the file to execute the transfers.
Select a Sending Company. In most cases this will be your organization.
Enter an Effective Date for the transactions.
Enter an Entry Description
Select an SEC Code. PPD for transactions to individuals and CCD for transactions to businesses.
When you've entered all the required details, click Create File in the bottom left corner.
On the Success page, you can download the NACHA file by clicking Download. You can also download a PDF report summary and a prenote version (all amounts zero) of the file.
Example NACHA File
Wrapping Up
You've successfully converted an Excel file to a NACHA file using ACH Pro! Besides Excel to NACHA conversion, ACH Pro also allows you to:
Recipient, company and ODFI management
ACH templates
Automatic SFTP upload of NACHA files to your bank
Full suite of APIs