Bank statement to QuickBooks
A three-column CSV that QuickBooks Online's bank import reads without misreading anything.
Columns: Date, Description, Amount
No statement handy? or open the finished Excel
What goes wrong
A fourth column becomes a debit
QuickBooks Online accepts a bank CSV as three columns — date, description, amount — or as four, where the last two are credit and debit. It decides which by counting. Hand it a file whose fourth column is the running balance and it does not complain: it reads every balance as a debit and writes those figures into the books. Nothing on screen says anything is wrong. This is why the QuickBooks export here is a separate, narrower file rather than the same CSV under another name.
Getting the file into QuickBooks
- 1Convert the statement above and press CSV for QuickBooks.
- 2In QuickBooks Online, open the bank account and choose to upload transactions from a file.
- 3When it asks how the columns map, they should line up as date, description and amount with nothing left over.
- 4Set the date format to yyyy-mm-dd — the file uses ISO dates precisely because that reading cannot be ambiguous.
- 5Review the transactions QuickBooks lists before you accept them, as you would with any import.
QuickBooks questions
Why is the balance column missing?+
Because including it is the failure this file exists to avoid. With four columns QuickBooks reads the fourth as a debit. The balance is still in the Excel file and the plain CSV, where a person reads it rather than an importer.
Why is the opening balance row not in the file?+
An opening balance is a starting figure, not a transaction. Imported, it becomes a zero-value entry in the register that somebody then has to find and delete.
Will importing the same statement twice create duplicates?+
QuickBooks matches against what is already in the account and flags likely duplicates, but it is not infallible across overlapping date ranges. Import one statement period at a time and check the dates at the seam.
Does this work with QuickBooks Desktop?+
Desktop prefers QBO and OFX files, which we do not produce — those carry a bank identifier from a list Intuit controls. For Desktop, a tool that holds that list, such as MoneyThumb or ProperSoft, is the honest recommendation.