Should I use the three-column or the four-column format?

Either works, by Intuit’s help. Three columns — one Amount, negative for money out — is what the recipe here writes, and it is the simpler one to check by eye.

What date format does QuickBooks want?

One format, the same on every line, which you name at the mapping step. The recipe writes day first, as Intuit’s UK help recommends; switch the pattern to %m/%d/%Y for a US company file.

Why is my opening balance line missing from the result?

It moves no money, so it is not a transaction, and writing it would mean a row holding only a zero — which Intuit’s help asks you to avoid. The recipe leaves such lines out.

How many transactions can one file hold?

Intuit’s help gives 1,000 lines per file. Cut a longer result into 1,000-line pieces with the first-and-last-rows tool here and upload them in turn.

My amounts have commas in them — is that a problem?

The recipe takes the thousands separators out inside its arithmetic, so the Amount column holds plain numbers like -1234.50; the bank’s own columns are left untouched in your original file.

Do my bank transactions leave my computer during this?

No. The statement is reshaped by a database engine inside this page; the only copy that goes to QuickBooks is the file you upload there yourself.

All guides

Upload a bank CSV to QuickBooks Online: three columns and one date format

When a bank feed will not connect, QuickBooks Online takes transactions as a CSV — but only in its own shape. By Intuit’s help that is three columns, Date, Description and Amount, or four with Credit and Debit apart; every date in the same format; no cell holding only a zero; and no more than 1,000 lines per upload. A bank’s download rarely arrives like that, and the recipe here rewrites one into it.

The shape QuickBooks takes

Intuit’s help describes two layouts: three columns — Date, Description, Amount, with money out as a negative amount — or four, with Credit and Debit in separate columns. At the mapping step QuickBooks asks which one your file uses and which date format it is written in, so every date has to be written the same way.

The same help asks for cells that would only hold a zero to be left blank, for the word “amount” to be kept out of the money columns, and for no more than 1,000 transactions in one file. A running balance column is not part of either layout.

From the bank’s download to three columns

The button opens the SQL console with a query written for the sample statement. It rewrites each date in one day-first form, 31/01/2024, whether the bank used slashes or hyphens; turns the separate debit and credit columns into one Amount, credits positive and debits negative, with thousands separators taken out; trims the padding off each description; and leaves out a line that moves no money, like the opening balance, so no row carries a zero.

Day first is the form Intuit’s UK help recommends. For a company file set up in the United States, change %d/%m/%Y to %m/%d/%Y in the query’s strftime line and the dates are written month first instead. Run it on the sample to see the shape, then drop your own download and change the table name to the one the console gives your file — and the column names too, if your bank calls them something else.

More than 1,000 transactions

Download the result, and if the count is over 1,000 lines, the first-and-last-rows tool cuts it into pieces: keep the first 1,000, then skip 1,000 and keep the next, each piece with its heading row. Upload them one after another; QuickBooks shows each batch for review before anything posts.

For a statement with the bank’s account details on lines above the heading, delete those lines in the editor first, with “First row is headings” unticked. The bank-statement guide on this site covers the other shapes a bank download takes.

The sample file

Date,Description,Debit,Credit,Balance
05/02/2024,CARD PAYMENT COFFEE HOUSE,4.50,,4685.01
03/02/2024,CARD PAYMENT PARKING,12.00,,4689.51
03/02/2024,STRIPE PAYOUT,,85.27,4701.51
02/02/2024,DIRECT DEBIT INSURANCE,42.00,,4616.24
01/02/2024,TRANSFER FROM SAVINGS,,250.00,4658.24
31/01/2024,STRIPE PAYOUT,,116.22,4408.24
30/01/2024,CARD PAYMENT STATIONERY LTD,18.99,,4292.02

Obviously made-up data, small enough to read. Download it and drop it on the tool to see the fix before trying your own file.

Questions

Should I use the three-column or the four-column format?: Either works, by Intuit’s help. Three columns — one Amount, negative for money out — is what the recipe here writes, and it is the simpler one to check by eye.
What date format does QuickBooks want?: One format, the same on every line, which you name at the mapping step. The recipe writes day first, as Intuit’s UK help recommends; switch the pattern to %m/%d/%Y for a US company file.
Why is my opening balance line missing from the result?: It moves no money, so it is not a transaction, and writing it would mean a row holding only a zero — which Intuit’s help asks you to avoid. The recipe leaves such lines out.
How many transactions can one file hold?: Intuit’s help gives 1,000 lines per file. Cut a longer result into 1,000-line pieces with the first-and-last-rows tool here and upload them in turn.
My amounts have commas in them — is that a problem?: The recipe takes the thousands separators out inside its arithmetic, so the Amount column holds plain numbers like -1234.50; the bank’s own columns are left untouched in your original file.
Do my bank transactions leave my computer during this?: No. The statement is reshaped by a database engine inside this page; the only copy that goes to QuickBooks is the file you upload there yourself.