NACHA File Format: Record Layout, Field Positions, and How to Open a NACHA File in Excel

The six NACHA record types

A NACHA file is a strict hierarchy, and the order is not optional. One File Header opens the file, then one or more batches, then one File Control closes it. Inside every batch, a Batch Header opens the group, an Entry Detail record represents each payment, an optional Addenda record follows an entry that carries extra remittance data, and a Batch Control closes the group with its totals. Read down the first character of every line and you should see 1, then 5, then a run of 6 and 7, then 8, repeating for each batch, and finally 9. Out of sequence records or a missing mandatory record cause the bank to reject all or part of the file.

Files are also blocked in groups of ten records, which is why the File Header carries a blocking factor of 10 and why real files end with lines of nothing but the digit 9 repeated. Those filler lines are padding to reach a multiple of ten, not data, and they should be dropped before analysis.

NACHA File Header record layout (record type 1)

The first line of the file. Immediate Destination is the receiving bank routing number and Immediate Origin is normally your company identification, both ten characters wide with a leading space or zero.

NACHA Batch Header record layout (record type 5)

This is the record that decides how the whole batch is treated. Service Class Code says whether the batch holds credits, debits or both, and the SEC code at 51-53 states the authorization type, which is what determines the return window for every entry in the batch.

NACHA Entry Detail record layout (record type 6)

The record you actually want in a spreadsheet. One line is one payment. This layout covers the common SEC codes including PPD, CCD, WEB and TEL. CTX and IAT entries use variations on it, so check the field positions against the SEC code in the batch header before you parse a corporate or international file.

NACHA Addenda, Batch Control and File Control layouts (records 7, 8 and 9)

The addenda record hangs off the entry above it and its Entry Detail Sequence Number at 88-94 is what ties the two together. The two control records are where a file proves it balances, and they are the first thing the bank checks.

The Entry Hash is the sum of every Receiving DFI Identification in the batch, truncated to its rightmost ten digits. It is a checksum, not a dollar figure, and it is the field most often computed wrong by a homegrown generator. Our guide on validating a NACHA file before sending it to the bank walks through the checks that catch a bad hash or an unbalanced batch before the bank does.

ACH transaction codes in the entry detail record

Positions 02-03 carry the transaction code, and it is the only thing in the file that tells you whether money is moving in or out. Nothing is stored as a negative number, so summing the amount column without splitting on this code gives you a total that means nothing.

Codes 21 and 31 carry a Notification of Change, where the receiving bank is telling you an account or routing number has moved. Those arrive back from the bank rather than going out in your file, and they need acting on or the next batch fails the same way.

SEC codes in the batch header

The Standard Entry Class code at positions 51-53 of the batch header states how the payment was authorized. It is not cosmetic. The SEC code sets the return window and the documentation you have to keep, and using the wrong one is a rules violation even if the money moves.

The full list also covers ARC, BOC, POP and RCK for check conversion, plus COR for notifications of change and ADV for accounting advices. The full ACH SEC codes list covers when each one applies, and what to put in the company entry description covers the field right next to it.

How to open a NACHA file in Excel by field position

You can do this by hand if you only need to eyeball one batch. Open the file with Excel set to All Files, and when the Text Import Wizard appears choose Fixed width rather than Delimited. Then place break lines at the character positions from the entry detail table above, and set the account number, routing number and trace number columns to Text before you finish, or Excel destroys them.

The break positions for an entry detail line are 1, 3, 11, 12, 29, 39, 54, 76, 78, 79. That gives you the eleven fields in order. Two things still need fixing afterward: divide the amount column by 100 to restore the decimal, and add a column that translates the transaction code into a direction so the debits can carry a negative sign.

The catch is that this only works cleanly if you first split the file so that only type 6 lines are in the sheet. Import the whole file at those break positions and the header, batch and control records get sliced at meaningless points, because they hold entirely different fields at those same offsets. That is the main reason people give up on the manual route on a real multi batch file and use the NACHA file to Excel converter instead, which reads each record type on its own layout in one pass. The step by step manual method is written up in how to open a NACHA ACH file in Excel.

What a NACHA file does not contain

This trips up people who are handed one expecting a statement. A NACHA file is a payment instruction: it tells the bank what to do. It has no opening balance, no closing balance, no running balance and no record of anything that was not originated in this file. It will not tell you whether a payment settled, was returned, or bounced. For that you need the statement or a returns file coming back from the bank.

If you are trying to reconcile, remember the whole ACH batch usually lands on the bank statement as a single summarized line, not as one line per payment. Matching the two means totaling the file by settlement date and comparing that to the statement line, which reconciling an ACH file against your bank statement works through. When a payment comes back, the reason arrives as a return code, and the ACH return codes list from R01 to R85 decodes them.

Related bank file formats

NACHA is one of several fixed layouts a US business gets from its bank, and they are easy to confuse because several are positional text. BAI2 is the balance reporting format and, like NACHA, stores amounts as whole cents, so the BAI to Excel converter covers that one. MT940 is tag based and comes from SWIFT, handled by the MT940 to Excel converter, and camt.053 is the ISO 20022 XML equivalent read by the camt.053 to Excel converter. If you are not sure which one you were sent, the bank file format converter compares them side by side, and the plain bank statement converter handles the PDF statement itself. Once the payments are in a sheet, teams that also want the receipts and card spend behind those payments read automatically tend to pair this with automated expense capture.