EFT Flow

How to Convert Excel to a Bank Payment File

You have payment data in Excel. Your bank wants a CPA-005 file. Here's how to bridge the gap.

The situation: Your accounting software exports to Excel or CSV. Your bank requires a specific format (CPA-005) for batch payments. These aren't the same thing—and your bank won't accept your spreadsheet directly.

Why banks won't accept Excel files

Excel is flexible. Banks need rigid structure.

Excel files

  • Columns can be in any order
  • Different headers, formats, layouts
  • Numbers might include formatting ($, commas)
  • No fixed record structure

CPA-005 bank files

  • Fixed-width format (1464 characters/line)
  • Specific field positions
  • Amounts in cents, no decimals
  • Strict structure: header, details, trailer

This format mismatch is why you can't just upload your spreadsheet to the bank. You need to convert it.

Your options for converting

Option 1: Manual conversion (not recommended)

Open your Excel file, manually restructure to CPA-005 format, export as text.

  • Time-consuming (hours per file)
  • Error-prone (one wrong character = rejection)
  • Requires deep knowledge of CPA-005 spec
  • No validation before upload

Verdict: Possible but painful. Not sustainable for regular payments.

Option 2: Custom scripts/macros

Build Excel macros or scripts to automate the conversion.

  • Can work if you have programming skills
  • Breaks when your export format changes
  • Hard to maintain over time
  • No built-in validation

Verdict: Works short-term but creates technical debt.

Option 3: Conversion software (recommended)

Use dedicated software that reads Excel and outputs CPA-005.

  • Import any Excel/CSV format
  • Map columns once, reuse forever
  • Validates before export
  • Handles all CPA-005 formatting automatically

Verdict: Fastest, safest, most maintainable.

Step-by-step: Excel to bank file with EFT Flow

Here's the actual workflow:

1
Export your payment list
From QuickBooks, Sage, Xero, or any system that exports to Excel/CSV. Include: payee name, amount, and ideally a reference number.
2
Open EFT Flow and import your file
Drag and drop or click to upload. EFT Flow reads .xlsx, .xls, and .csv files.
3
Map your columns (first time only)
Tell EFT Flow which columns contain: payee name, amount, reference, etc. Saved for future imports.
4
Match to vendors
EFT Flow matches payee names to your vendor list (where bank details are stored). First time requires setup; after that, automatic.
5
Validate
EFT Flow checks for errors: invalid routing numbers, missing data, format issues. Fix before export.
6
Export CPA-005 file
Click export. Get a properly formatted file ready for your bank.
7
Upload to your bank
Log into your business banking portal and upload the file.

After first-time setup: Steps 3-4 become automatic. Your workflow becomes: export from accounting software → drop in EFT Flow → click export → upload to bank. Three minutes.

What you need in your Excel file

At minimum, your Excel file should contain:

Required

  • Payee name: Who you're paying
  • Amount: How much (dollars or cents)

Helpful to have

  • Reference/invoice number: For reconciliation
  • Payment date: When to process

Note: You don't need bank account details in the Excel file. Those are stored separately in your vendor list and matched by payee name.

Example Excel layout

Vendor Name Invoice # Amount
ABC Supplies Inc. INV-2024-001 $1,234.56
Smith Consulting INV-2024-002 $5,000.00
Office Plus INV-2024-003 $789.00

This simple layout is enough. EFT Flow handles the complex CPA-005 formatting.

Works with exports from

QuickBooks

Online or Desktop. Export vendor payments, AP aging, or custom reports to Excel.

Sage

Sage 50, Sage 300. Export payment lists or vendor reports.

Xero

Export bills to pay or batch payment lists.

NetSuite

Export payment data from saved searches or reports.

Custom systems

If it exports to Excel or CSV, EFT Flow can read it.

Manual spreadsheets

Even hand-maintained Excel payment lists work.

Common Excel formatting issues

Watch out for these when preparing your export:

Amounts as text

Problem: Excel sometimes stores numbers as text, especially after CSV import.

Solution: EFT Flow handles this—but if you're troubleshooting, check for green triangles in Excel cells indicating text-formatted numbers.

Currency symbols and commas

Problem: "$1,234.56" includes characters that need to be removed.

Solution: EFT Flow strips these automatically during import.

Extra rows or columns

Problem: Headers, footers, totals rows that aren't payment data.

Solution: EFT Flow lets you specify which rows contain actual data. Or clean up the export before import.

Inconsistent payee names

Problem: "ABC Supplies" in one row, "ABC Supplies Inc." in another—same vendor, different names.

Solution: Standardize names in your accounting system, or use EFT Flow's vendor matching to handle variations.

Ready to convert your Excel files?

EFT Flow takes your existing Excel exports and turns them into bank-ready CPA-005 files. Map columns once. Export forever. No per-transaction fees.

Download Free Trial See Pricing