Opened an ACH file and got a wall of text in Excel? Yep, that’s normal. NACHA files are fixed‑width text (94 characters per line). Perfect for banks, not exactly friendly for spreadsheets.

But you still need that data in a clean table to reconcile cash, confirm totals, and load your ERP or BI. Here’s the plan to open a NACHA file in Excel or convert it to CSV/XLSX without mangling amounts or dropping leading zeros: - What matters in a NACHA file (record types 6 and 7) - Quick options: a text preview and a one‑off fixed‑width import - A repeatable Excel Power Query approach to slice by position and join addenda - A fast, audit‑ready conversion using BankXLSX - Gotchas to avoid and simple checks to make sure everything ties out

Follow along and you’ll get a reliable way to turn ACH/NACHA into tidy CSV or Excel—ready for reconciliation and reporting.

Overview: What a NACHA (ACH) file is and why you might want it in Excel/CSV

Wondering how to open a NACHA file in Excel? You’re looking at a text file designed for bank software, not humans. Every line is exactly 94 characters, and the good stuff (actual transactions) sits inside specific “record types,” not columns. That’s why it looks like gibberish at first.

Once you convert ACH NACHA to CSV or XLSX, things click. You’ll get one row per transaction, amounts in dollars (not cents), routing and account numbers preserved as text, plus extras like SEC code or addenda if you want them.

Decide your finish line up front. If you only need a few amounts and trace numbers today, a quick fixed‑width import is fine. If you’ll be doing this every month and need consistent results and an audit trail, build a template or automate it.

Time check: if manual parsing takes ~20–30 minutes per file and you process 10 files a month, that’s 4–5 hours you could get back with a repeatable method—usually from the very first month.

Read this first: Security and compliance when handling ACH data

NACHA files hold sensitive items—routing and account numbers, names, and payment info. Treat them like any financial/PII data. Keep them in encrypted storage, lock down access, and skip email attachments. If someone outside the core team needs a peek, share a secure link with an expiry and watermark.

In Excel, keep routing and account numbers as Text so you don’t lose leading zeros. For broader audiences, consider masking (e.g., show only last four).

Practical controls you can put in place now:

• Create two exports: one with full details for operations, one with masked accounts for analysis.
• Set retention: delete source ACH files after sign‑off and keep the structured outputs and a simple audit log.
• Track chain of custody: who uploaded, who converted, and what totals were checked.

One more tip: keep working files in a secure, short‑lived workspace. Convert, validate, load to ERP/BI. Then archive a read‑only copy and retire the rest. Less clutter, lower risk, audit still happy.

NACHA in plain English: The 94-character fixed-width format

NACHA files are plain text, but each line is exactly 94 characters. The first character tells you what the line is. For daily accounting work, you care most about: - 6 = Entry Detail (the transaction row you actually want) - 7 = Addenda (extra info such as remittance or return reasons) You’ll also see headers and controls: 1 (File Header), 5 (Batch Header), 8 (Batch Control), and 9 (File Control/fillers). No commas or tabs here, so Excel won’t split fields automatically—you must import by fixed position. Think “Excel fixed width import NACHA (94‑character records)” as your go‑to.

A simplified look at type 6 (Entry Detail):

• Positions 4–11: Receiving DFI (first 8 digits of routing)
• Position 12: Check digit (9th digit of routing)
• 13–29: DFI account number (treat as text)
• 30–39: Amount (integer cents; implied decimal)
• 55–76: Individual name
• 80–94: Trace number

Grab batch context, too (from 5 and 8): Company Name/ID, SEC code, Batch Number. These are gold for filtering and for tying batch totals to the controls.

Quick preview method: Open a NACHA file in a text editor

Before you touch Excel, pop the .ach or .txt file into a plain‑text editor with word wrap off. You should see lines starting with 1, 5, 6, 7, 8, or 9. Count a few 6‑records (entries) and see whether they’re followed by 7‑records (addenda). Lots of 7s after each 6? That’s probably a CTX batch with EDI content—good to know ahead of time.

Two fast sanity checks: - Amount: pick a 6‑record and look at positions 30–39. 0000001234 means $12.34 after the implied decimal. - Linking: later, you’ll join entry detail sequence number to trace number. The addenda’s sequence matches the last 7 digits of the 6‑record’s trace number.

Use a fixed‑width font (Consolas works) so 94‑character lines line up nicely. If lines don’t look 94 characters long, you may have encoding or wrapping issues. Fix that first so you don’t chase ghosts in Excel.

One-off method: Import a NACHA file into Excel using fixed-width

For a single file, the fixed‑width import does the job. In Excel, go to Data > Get Data > From Text/CSV. If you don’t see the fixed‑width wizard, enable the legacy Text Import Wizard. Set the breaks by position for type 6 fields—routing (8+1), account, amount, name, trace. Bring type 7 addenda into another sheet.

Two things that prevent most headaches: - Format routing and account as Text so Excel keeps leading zeros. - Convert the amount from cents by dividing by 100—easy to forget and you’ll be off by a factor of 100.

Example: Amount “0000001234” equals 12.34, not 1,234.00. Add a calculated column: =VALUE(AmountRaw)/100 and format as currency.

For addenda joins, create a key using the last 7 digits of the trace on the entries sheet and link to the entry detail sequence number on the addenda sheet with XLOOKUP. Small files? This is quick and clear. If there are multiple addenda per entry (CTX/IAT), either stack addenda as separate rows or combine them in one cell with line breaks—whichever your downstream process prefers.

Repeatable method: Build a Power Query to convert NACHA to a table

Power Query turns that manual setup into a refreshable routine. Import the file as lines of text, add a RecordType column (first character), then branch into two queries: one for 6‑records (entries) and one for 7‑records (addenda). Use Text.Range to slice fixed positions—Power Query NACHA to table is basically careful cutting.

Pattern to follow: - Entries (type 6): pull transaction code, routing (8+1), account (text), amount (cents), name, addenda indicator, trace. Convert amount = Number.From(AmountRaw)/100. - Addenda (type 7): grab addenda type, payment‑related info, addenda sequence, entry detail sequence. - Merge: match Right(trace, 7) to the addenda’s entry detail sequence. Expand or aggregate addenda based on your reporting needs.

Keep batch‑level context (SEC, Company ID) as columns for filtering. Parameterize the file path so teammates can swap files without touching the query. Extra credit: store a “LineHash” for each 94‑char string. If the source changes, an anti‑join quickly shows which lines differ—handy for audit and explanations.

Automated, audit-ready conversion with BankXLSX

When you need speed and consistency, automate. BankXLSX reads NACHA files, parses record types 1/5/6/7/8/9, and outputs a clean table you can drop straight into your ERP or BI. As a NACHA to XLSX converter, it handles implied decimals, keeps routing/account numbers as text, and links addenda—even when there are many per entry.

Typical flow:

• Upload one or many files, preview a few rows, and pick the columns you want (amount, routing, account, name, SEC, batch, trace, addenda).
• Choose to combine addenda into one cell or keep one row per addenda.
• Validate: built‑in checks compare your sums to batch and file control totals and flag anything odd.
• Export CSV or XLSX, with batch identifiers and optional source‑file metadata.

Process files from multiple banks? Save a mapping profile and batch convert NACHA files to CSV/XLSX in one go. Also useful: exceptions on a separate tab—malformed lines, mismatched counts, that sort of thing—so you can fix issues before the data hits your ledger.

Field mapping cheat sheet: What to extract for analysis and reconciliation

For reconciliation, focus on fields you can match every time and the ones that support controls. From type 6 (Entry Detail), pull:

• Transaction code (tells you debit/credit and account type)
• Receiving DFI + check digit (routing number)
• DFI account number (text)
• Amount (store as dollars, not cents)
• Individual ID/Name
• Addenda indicator
• Trace number (unique key)

From type 7 (Addenda), grab:

• Addenda type
• Payment‑related information
• Addenda sequence and entry detail sequence

From headers/controls: company name/ID, entry description, effective entry date, batch number, SEC code, and control totals. With these, you can parse ACH record type 6 and 7 in Excel and keep a tidy audit trail.

Bonus columns that pay off: - Debit/Credit sign from the transaction code (set debits negative, credits positive) - FileName and BatchNumber for lineage - ShortMaskAccount = last 4 of account (easy on the eyes) - TraceSuffix = last 7 of trace (handy for joins and quick checks)

That model gives you an ERP‑ready table for both reconciliation (match to cash) and analysis (by SEC, bank, customer/vendor).

Handling variations: SEC codes, addenda types, and returns

Not every batch behaves the same. PPD and CCD typically have zero or one addenda per entry (often type 05 with free‑form remittance). ACH PPD vs CCD addenda handling stays simple—just bring that text in as a column and move on. CTX (Corporate Trade Exchange) is different: lots of addenda carrying EDI 820 segments.

If you need invoice‑level detail, output one row per addenda. If not, concatenate addenda into one field and store a count. For IAT (international ACH), expect multiple required addenda with structured fields—don’t drop them.

Returns and NOCs ride in as entries with specific transaction codes and addenda holding reason codes. Include the reason (e.g., R01 insufficient funds, R03 no account) so you can pivot on it.

For ACH CTX addenda EDI 820 to CSV, a good compromise is two columns: one with the raw addenda text, one with a few parsed bits (invoice number, amount). You keep detail for audit and still get quick reporting without building a full EDI parser on day one.

Validating your output: Totals, counts, and integrity checks

Before you trust the numbers, validate. Start with counts: number of type 6 entries per batch should match the Batch Control record. Then roll up to file level and check against the File Control. Next, amounts: sum debits and credits separately by transaction code and tie them to the controls. In Excel, a pivot or a few SUMIFS does the trick.

Add a few guardrails:

• Unique traces: no duplicates inside a file.
• Addenda completeness: if addenda indicator = 1, there should be at least one matching 7‑record.
• Every line is 94 characters: catches broken uploads or weird encoding.

One more check: “block count” sanity. NACHA files use 10‑record blocks and pad with 9s. You don’t need to rebuild blocks, but logging the number of 9‑records can surface truncation. If totals don’t tie, isolate by batch—most issues come from one mis‑parsed batch, not the whole file.

Common pitfalls (and how to avoid them)

The usual suspects: - Amounts off by 100: amounts are in cents—divide by 100 and format as currency. - Leading zeros vanish: routing and account must be Text, period. - Fixed‑width breaks off by one: one shift and everything looks wrong; verify with a known good line.

Other gremlins: - Byte order mark (BOM): strip it so the record type sits at character 1.

- Line wrapping in editors: turn wrap off and use a fixed‑width font. - Mixed SEC codes: CTX/IAT can carry multiple addenda—don’t assume just one.

Tip: add a “RawLineLen” column during import. If any line isn’t 94, push it to an exceptions tab. That tiny check can save hours of head‑scratching later.

Working at scale: Multiple files, schedules, and downstream systems

Scaling up is mostly about consistency. In Power Query, point to a folder instead of a single file and append everything with the same schema. Parameterize paths or date subfolders for monthly runs. Start with a simple ingestion step (one column of raw lines), then apply the same transformations—this approach survives minor bank quirks.

If you want hands‑off runs, batch convert NACHA files to CSV/XLSX and feed your downstream systems on a schedule.

Operational tips:

• Add a SourceFile column so every row can be traced to its original file.
• Keep a file checksum (SHA‑256 is fine) to avoid re‑posting the same file twice.
• Version your mappings. If you change columns mid‑quarter, bump a MappingVersion number for audit clarity.

Delivery matters too. Some teams drop CSVs into a secure landing zone for nightly ERP ingestion. Others ship XLSX for review, then a final CSV for integration. Either way, define one “source of truth” for the approved file so there’s no shadow editing.

Troubleshooting: Quick answers to frequent issues

Everything shows in one column? You probably used a delimited import. Use fixed‑width and set breaks. If Power Query still smooshes fields, make sure you imported lines as Text and turned off auto‑detect.

Addenda not linking? Join the addenda entry detail sequence to the last 7 digits of the trace. If you’ve got multiple addenda per entry, decide: multiple rows or a single combined field. Mixing both causes duplicates.

Control totals don’t tie? Check at the batch level first. Compare entry counts and debit/credit sums to the Batch Control, and confirm every line is exactly 94 characters. Also watch for shifted positions caused by a stray character.

Weird characters at the top of the file? Remove a UTF‑8 BOM so the first character is your record type. CR vs CRLF line endings can also throw counts off—normalize them before import. When stuck, test with a tiny sample (one batch, two entries—one with addenda, one without). If that works, scale up piece by piece.

Choosing the right approach: Decision guide

Quick rule of thumb. One‑off file and you only need amounts and trace numbers? Fixed‑width import is fine. Recurring files and you want a clean process that joins addenda correctly? Spend an hour and build a Power Query NACHA to table you can refresh. Lots of files, tight deadlines, or mixed SEC codes like CTX/IAT? Automate so you can convert ACH NACHA to CSV the same way every time.

Think about: - Volume and frequency - Addenda complexity and SEC mix - Risk if a number is wrong - Who owns and maintains the workflow

Pro move: define your “target schema” first (exact columns and types your ERP/BI expects). Then make any method—manual, Power Query, or automation—output that schema. If you switch methods later, your downstream integrations won’t care.

Next steps: Implement your workflow and accelerate reconciliation

Pick your path and write it down. For fixed‑width imports, keep screenshots of column breaks and list the fields you pull. For Power Query, save a template workbook, parameterize the source path, and add a validation tab with counts and totals. Trying automation? Run a pilot with last month’s files and compare line by line against your current process.

A simple checklist: - Secure intake and storage for ACH files. - Define a target schema (columns, data types, masking). - Implement conversion (fixed‑width, Power Query, or a NACHA to XLSX converter). - Validate counts/totals and route exceptions. - Load to ERP/BI, archive final outputs and logs.

Ready to lock it in? Set up a watched folder, schedule conversions, and get a daily exceptions report. You’ll cut manual work, shorten close, and keep a clean audit trail—so you can focus on analysis, not wrangling fixed‑width text.

Key Points

• NACHA files are fixed‑width (94 characters per line). Import by position in Excel and focus on record types 6 (Entry Detail) and 7 (Addenda).
• For Excel: do a one‑off fixed‑width import or build a refreshable Power Query. Keep routing/account as Text, convert cents to dollars, and link addenda by matching the trace’s last 7 digits to the entry detail sequence.
• Validate before using the data: match entry counts and debit/credit totals to batch/file controls, ensure unique trace numbers, and confirm every line is 94 characters. Watch for SEC variations (PPD/CCD vs CTX/IAT).
• At scale, automate: BankXLSX converts NACHA to CSV/XLSX with proper formatting, addenda handling, batch processing, and control‑total checks for audit.

Conclusion

NACHA files are fixed‑width and a bit fussy. To use them in Excel, import by position, focus on record types 6 and 7, keep leading zeros, convert cents to dollars, link addenda by the trace suffix, and verify counts and totals. For repeat work, Power Query makes a solid “NACHA to table” flow. For volume and accuracy, automation wins. Want to save time and lower risk? Use BankXLSX to convert ACH NACHA to CSV/XLSX in seconds with built‑in validation. Upload a sample or grab a quick demo and get your reconciliation workflow humming.