QBO to CSV: Open a Web Connect File in a Spreadsheet

QBO to CSV means pulling the dates, payees and amounts out of a Web Connect file and laying them in spreadsheet columns. A QBO file is plain text with labelled tags, so you can read it in a text editor, then copy each transaction's date, name and amount into a table you can sort, check and save as CSV.

Why turn a QBO download back into CSV?

You turn a QBO file into a CSV when you need to look at the transactions, edit them or send them somewhere that does not accept QBO. A QBO file is built for QuickBooks to read, not for a person, so a spreadsheet is the easier place to check what your bank sent.

The usual reasons are:

Checking before you import. A bank's QBO download may hold a different date range than you expected, or lines you already entered by hand. Seeing them in rows lets you spot that first.
Moving to Xero. Xero expects statement CSVs in a set column order, so a spreadsheet built to that order is the easiest way in.
Fixing or trimming the data. Deleting duplicate rows or cutting a date range is quick in a spreadsheet.
Sharing with someone who has no accounting software. A bookkeeper, a lender or a relative can open a CSV in any spreadsheet program.
Keeping your own records. A table of transactions is easier to file and search than a tag file.

QBO is the file QuickBooks calls Web Connect. If you want the full picture of what such a file is, what a QBO file contains and how QuickBooks reads it comes first. The rest of this post assumes you have a .qbo file and want its rows.

Pulling dates and amounts out of the QBO tags

Each transaction in a QBO file sits between STMTTRN tags, and four tags inside it hold what you want: DTPOSTED for the date, TRNAMT for the amount, NAME for the payee and MEMO for extra text. Copy those four values for each transaction into columns and you have the CSV.

Open the file in a plain text editor such as Notepad or TextEdit in plain-text mode. Do not open it in a word processor, which can alter the characters. Below is the same pair of transactions as they appear in the QBO file and as CSV rows. The values are examples.

In the QBO file:

<STMTTRN><TRNTYPE>DEBIT<DTPOSTED>20260314<TRNAMT>-412.80<FITID>20260314001<NAME>FLOUR SUPPLY CO<MEMO>Card purchase</STMTTRN>
<STMTTRN><TRNTYPE>CREDIT<DTPOSTED>20260316<TRNAMT>2500.00<FITID>20260316001<NAME>CUSTOMER PAYMENT<MEMO>Deposit</STMTTRN>

As CSV rows:

Date,Payee,Memo,Amount
2026-03-14,FLOUR SUPPLY CO,Card purchase,-412.80
2026-03-16,CUSTOMER PAYMENT,Deposit,2500.00

Reading a tag

A tag is a word in angle brackets, such as . The value follows it directly with no closing tag in most bank files. The value runs until the next opening bracket. So in <TRNAMT>-412.80<FITID>, the amount is -412.80.

What each tag becomes

DTPOSTED becomes the Date column. It is written as year, month, day with no separators, so 20260314 is March 14, 2026. Some banks add a time and a time zone after the date, such as 20260314120000[0:GMT]. Keep only the first eight digits.
TRNAMT becomes the Amount column. The sign is already there: negative is money out.
NAME becomes the Payee or Description column.
MEMO becomes a Memo column. It is often empty or repeats the name.

Working through a whole file

For a file with a handful of lines, copy by hand. For dozens, a text editor's find and replace does most of the work. Replace <STMTTRN> with a line break, replace each tag with a comma, and delete the closing tags. Check a few rows against the original before you trust the result. If your file has hundreds of lines, a free QBO to CSV converter online can do this in one step, but check what it does with your data, because many such tools upload the file to a server.

Opening the QBO data in Excel as a table

Excel can read a QBO file only as plain text, not as a table of transactions. It will not split the tags into columns by itself. You can import it as text, then split the tags into columns using Excel's own tools, or paste the cleaned lines from your text editor.

This answers a common search, convert QBO file to Excel: there is no direct open-as-table. There is a short route that works:

Do the find-and-replace in the text editor so that each transaction is one line with commas between the values, as in the CSV example above.
Save the file with a .csv extension. Choose "All files" in the save box, or the editor may add .txt.
Open the .csv in Excel. It places each comma-separated value in its own column.
Add a header row if you need one.
Format the Date column as a date and the Amount column as a number.
Save as an Excel workbook if you want one, or leave it as CSV.

A second route is Excel's text import. On the Data tab, the option to bring in data from a text or CSV file opens a wizard. Pick the QBO file, and use the delimiter step to split on the angle brackets. It produces a messy grid, because each tag lands in its own cell, but you can delete the tag columns and keep the value columns.

Two warnings save time. First, open the file as text, not by double-clicking: your computer may offer QuickBooks, which is not what you want. Second, if a bank's QBO file shows the whole file on one long line, put a line break before every <STMTTRN> first, or Excel will read it as one cell.

SimpleBankConvert makes QBO and CSV files from PDF statements. It is in early access and does not read QBO files as its input, so see the homepage for what it does today.

Which QBO fields get lost in a CSV?

A CSV keeps only the columns you choose to copy, so everything else in the QBO file is dropped. That includes the bank and account details, the transaction ID, the transaction type and the balance. For most checking purposes you lose nothing you need, but a few of these matter if you plan to go back to QuickBooks.

What does not survive the trip:

The unique transaction ID (FITID). QuickBooks uses it to avoid importing a transaction twice. A CSV has no such field unless you keep it as a column. If you later convert your CSV back to QBO, the converter has to invent new IDs, and old and new lines can then appear as duplicates.
The bank ID and account block. The institution, routing number, account number and account type are not part of any transaction row. Write them down if you need them.
TRNTYPE. The label such as DEBIT, CHECK or FEE disappears. The sign of the amount still tells you the direction.
The statement balance. The ending balance and the date it applies to are not on any row. Copy them to a note, because they are what you reconcile against.
The date range. DTSTART and DTEND show what the file claims to cover. Note them before you start, so you can see whether any rows are missing.

If you are going to Xero, you also need to set up the columns Xero expects, which differ from this simple layout. The CSV layout Xero expects goes through it column by column. If you only need the data for your own check, the four columns above are plenty.

QBO to CSV dates that come out in the wrong order

Dates go wrong in a CSV because the QBO date is a bare number, such as 20260314, and spreadsheets try to interpret it. Excel may show it as a long number, as 20,260,314, or as a date in an order you did not expect. The cure is to turn the number into a real date once, deliberately.

The usual symptoms and their fixes:

The date shows as a long number. Excel read the value as a number. Split it with a formula. If the number is in cell A2, =DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2)) returns a real date. Format the result as a date.
The date shows as ##### . The column is too narrow. Widen it.
The date is in the wrong order. Excel reads the date with your computer's regional setting, so 03/04/2026 may mean March 4 or April 3. The year-first form 2026-03-04 avoids the question, so write your dates that way when you save the CSV.
The date has extra digits. Some banks append a time and a time zone. Keep only the first eight digits before you convert.
A date drops its leading zero. If you open a CSV and Excel shows 3/4/2026, the underlying value is fine. Only the display changed.

A last check catches most of these: sort the CSV by date and look at the first and last rows. They should match the period the file claims to cover. If you see a date a year out or a date in the wrong month, the conversion has misread something, and it is better to find it now than at reconciliation.

When you are happy with the CSV, you can carry on. Old Quicken files are a different job, covered in moving QIF files into QuickBooks. Quicken downloads in QFX form have their own post, getting a QFX file into QuickBooks.