You can create an ABA file from an Excel spreadsheet by mapping each payment column to the corresponding Direct Entry field, supplying your bank-issued user details, and exporting the result as a fixed-width .aba file.
Do not simply rename an .xlsx file to .aba. Excel workbooks and ABA files use completely different structures, so the data must be converted.
1. Prepare the Excel spreadsheet
Use one row per payment and one column per field. Clear, unique column headings make mapping easier. A typical worksheet contains:
| Column | Example | Notes |
|---|---|---|
| BSB | 062-000 | Keep leading zeroes and the full six digits. |
| Account number | 001234567 | Format as text if leading zeroes are possible. |
| Account name | Example Supplier | Use the name expected for the destination account. |
| Amount | 1250.50 | Enter dollars and cents consistently. |
| Lodgement reference | INV-1042 | A meaningful reference for reconciliation. |
| Transaction code | 53 | Use the code appropriate to the payment. |
Remove subtotals, blank header rows, merged cells, explanatory notes, and formulas that return errors. If the workbook contains multiple sheets, identify the sheet containing the final payment list.
Account numbers should usually be stored as text. Excel may remove a leading zero or display a long value in scientific notation when it treats the value as numeric.
Westpac also instructs customers preparing spreadsheet payment data to format BSB and account-number columns as text.
2. Collect the details supplied by your bank
The spreadsheet only describes the destination payments. The ABA header and trace fields also require information associated with your Direct Entry facility, which can include:
- financial institution abbreviation;
- Direct Entry user name;
- APCA or Direct Entry user ID;
- file description;
- processing date; and
- trace BSB and trace account number.
Obtain these values from your bank or your organisation's banking administrator. They are not values the converter can infer safely, and they should not be copied from a random sample file.
AusPayNet explains that the six-digit DE User ID identifies an approved Direct Entry user and is issued via the sponsoring financial institution. NAB's file guidance likewise states that Direct Entry files require a DE User ID and recommends using a unique payment identifier in the lodgement reference.
3. Upload the workbook and choose a sheet
Open the Excel to ABA converter and select the workbook. The file is read in your browser. Choose the correct worksheet and confirm which row contains the column headings.
If the preview does not resemble the payment table, go back and select a different sheet or header row before mapping any fields.
4. Map the Excel columns to ABA fields
Match each spreadsheet heading to its intended ABA destination. Check a few rows after mapping, especially where BSBs or account numbers begin with zero.
Some information can be constant for the whole batch. For example, the transaction code may be the same for every payment, while BSB, account number, amount, and reference usually come from individual rows.
5. Review validation errors
Before exporting, resolve errors such as:
- missing or malformed BSBs;
- blank account numbers;
- unsupported characters;
- names or references that exceed the available field width;
- zero, negative, or incorrectly formatted amounts; and
- missing bank-issued header or trace details.
Do not shorten or replace business-critical values without confirming the result. In particular, make sure the lodgement reference still lets the recipient and your finance team identify the payment.
6. Generate and download the ABA file
Once the preview is correct, generate the ABA file and save it in your organisation's approved payment-file location. Keep the original workbook as the source record, subject to your retention policy.
Generating the file does not submit the payments. Upload and authorisation happen separately in your bank's business banking service.
7. Check the generated file
Review the downloaded file before sending it to the bank. Confirm:
- the number of payments;
- individual and total amounts;
- the requested processing date;
- the Direct Entry user ID;
- the trace account; and
- the lodgement references.
For an additional structural check, run the output through the validator before upload.
Common Excel-to-ABA problems
Leading zeroes disappear
Format BSB and account-number columns as text before entering or importing the data. Then confirm the values in the converter preview.
Amounts are 100 times too large or too small
ABA stores amounts as cents, while spreadsheets usually display dollars. Use a converter that handles this transformation and verify the displayed batch total before download.
The bank rejects a file that passes validation
A structurally valid file can still contain an incorrect user ID, trace account, processing date, or facility-specific value. Compare the batch settings with the details issued by your bank and read the bank's rejection message before editing the file.
