Convert TXT to OFX: Plain Text Bank Exports

To convert TXT to OFX, first work out how the bank laid out its text file: separated by tabs or commas, padded into fixed-width columns, or a bank format such as BAI2 under a .txt name. Rebuild the rows as a CSV with date, description and signed amount, then run that CSV through an OFX converter and test the result.

What kind of TXT bank export is this?

A .txt extension says only that the file is plain text. Banks put three quite different things inside one. Open the file in a text editor such as Notepad or TextEdit and look at the first ten lines. Their shape tells you which kind you have, and that decides every later step.

• Delimited text. Each line is one transaction, with fields separated by a tab, a comma, a semicolon or a pipe character. This is a CSV in all but name.
• Fixed-width text. Each line is one transaction, but the fields are padded with spaces so they line up in columns, like a printed report.
• A bank file format. The lines start with record codes or tags rather than dates. Business banks sometimes deliver BAI2 files with a .txt name; East West Bank's BAI2 reporting guide gives sample file names that end in .txt. If your lines begin with codes such as 01, 02, 03 and 16, read the BAI2 file format explained instead of this post.

If the text starts with OFXHEADER or a run of angle-bracket tags, the file is already OFX or QFX with the wrong extension. Opening a QFX file to see what is inside shows how to recognise one.

Finding the column layout of a TXT bank export

The column layout is where each field starts and ends on a line. In a delimited file the separator marks it. In a fixed-width file you count character positions. Either way, write the layout down once, because the bank uses the same layout every month and you will reuse it.

An example fixed-width line

Here is a made-up line from a fixed-width export, with a ruler above it to count positions:

         1         2         3         4         5         6
12345678901234567890123456789012345678901234567890123456789012
05/12/2026 TRANSFER FROM SAVINGS            1200.00 C  8834.50

Split by position, it reads:

The flag column is the detail people miss. Here the amount is unsigned, and the C or D after it says which way the money moved. Your bank's layout may differ, so check against a statement you already know.

Using Excel to split the columns

In Excel, choose Data, then From Text/CSV, and pick the file. For delimited text, choose the separator. For fixed width, use Data, then Text to Columns, choose Fixed width and click to set a break at each field boundary. Set the date column's type to text at this stage so Excel does not reinterpret it.

Rebuilding the TXT rows as a CSV file

The CSV you build should have one header row and one transaction per line, with three columns: Date, Description and Amount. Write the date as year-month-day, keep the description as text, and turn any debit or credit flag into a sign so money out is negative. Drop the balance column unless your converter asks for it.

Steps, starting from the split columns in Excel:

1. Add an Amount column. If the flag is D, make the amount negative; if C, keep it positive. A formula such as =IF(D2="D",-C2,C2) does this when the amount is in C and the flag in D.
2. Rewrite the dates as yyyy-mm-dd.
3. Trim extra spaces from the description with TRIM.
4. Delete header and footer lines the bank added, such as report titles, page numbers and totals.
5. Save as CSV UTF-8 and open the result in a text editor to confirm it.

Check the result against a known balance. Take the opening balance from the bank, add the sum of the Amount column, and compare it with the closing balance. If the two agree, no line was lost in the split. On a Mac the same steps work in Numbers, and converting a CSV to QBO on a Mac covers the Mac side of the next stage if QuickBooks is the target.

Turning the rebuilt CSV into an OFX file and testing it

With a clean CSV, an OFX converter maps the three columns to the OFX date, amount and name fields, generates a unique ID for each line and writes the account header. You supply the account type and the closing balance. Then open the OFX in a text editor and import it into an empty test account before the real one.

What to check in the finished file:

• The number of STMTTRN blocks equals the number of CSV rows.
• Every transaction ID differs from the others.
• Three dates and three amounts match the TXT original.
• Money out shows as negative.

If the OFX is going to Xero or another program, test there with a short file of a few lines first. If the goal is a printable record rather than an import, a bank file is the wrong output, and turning a QBO file into a PDF report suits that better. If you are moving between accounting systems at the same time, the bank history questions in switching from Xero to QuickBooks Online are worth reading before you convert months of text files.

Does QuickBooks Online need the TXT converted at all?

Possibly not. Intuit's page on uploading transactions manually to QuickBooks Online lists .txt among the file types for a transaction list upload, alongside .csv, .qbo and .ofx. If QuickBooks Online is the destination, try uploading the delimited TXT, or the CSV you rebuilt, before building an OFX.

The same page sets a limit of 350 KB per file and up to 1,000 lines. A fixed-width text file is unlikely to map cleanly in the upload screen, so for that kind the CSV is the better upload. For a BAI2 file, a straight upload will not work at all.

If your bank's only alternative to the text export is a PDF statement, SimpleBankConvert is being built for that case: it reads the PDF and writes a CSV or QBO file. See the homepage for what it covers.