How to Fix a QuickBooks Bank CSV Import

What to run

Open QuickBooks CSV Validator and use the original bank export. The check stays in the browser. Choose the QuickBooks profile that matches the company file. QuickBooks Online, the United States date default, UK and Sole Trader, and a Desktop candidate are separate profiles because the published date guidance is not the same everywhere.

Leave the separator on auto unless the preview is one column. Choose the encoding only when the original file is not valid UTF-8. Windows-1252 covers many Western bank exports. Mark whether the first row is headings. Then choose 3 columns or 4 columns. A 3-column candidate is Date, Description, Amount. A 4-column candidate is Date, Description, Credit and Debit.

Map the date, the description and either one amount column or both a debit column and a credit column. For one amount column, say whether a negative number is money out. For two columns, say whether the source Credit column is money in, which is typical of a bank statement, or money out, which is Intuit's own 4-column example. The page shows both totals and does not choose the split for you.

Date,Description,Credit,Debit
20/11/2018,Example of a payment,100.00,
21/11/2018,Example of a deposit,,200.00

How to read the result

Money out and money in are the numbers to compare with the statement. In the 3-column file a negative amount is money out. In the 4-column file Intuit's example puts the payment in Credit and the deposit in Debit, and the other cell is blank rather than zero. If those totals are the opposite of the statement, switch the direction and look again. Do not download while they are reversed.

A zero amount, a row with both Credit and Debit filled, a negative number in a split column, a description that is only a number, or a date that contradicts the selected order blocks the candidate. The row stays in the list. The tool does not delete it to make the file look clean. Duplicate dates and descriptions are warnings, because a statement can contain two identical transactions.

Dates that can be read as either dd/mm/yyyy or mm/dd/yyyy stay warnings until one row proves the order, such as 20/11/2018. Intuit recommends dd/mm/yyyy. The upload screen still asks you to confirm the order, so the candidate and that choice must match. Special characters from the UK checklist block a description until you opt in to removing them, and the preview shows the changed text.

What to open next

If the preview is one column, the separator is wrong, or names are garbled, open CSV Fixer on the same original file. It detects comma, semicolon, tab and pipe separators and decodes the original bytes. It does not decide which column is a QuickBooks debit. Bring the readable export back to the validator.

Download only after the approval checkbox. The file is UTF-8 with a byte order mark and Windows line endings, which is the usual way to save a CSV for the QuickBooks upload screen. The headings are exactly Date, Description and Amount, or Date, Description, Credit and Debit. The word amount is not added to Credit or Debit.

In QuickBooks, upload the candidate to the account that matches the file. A credit-card export can use the opposite sign because a payment reduces the card balance. Choose the credit card account there, not a bank account. Then compare the imported count and the money-in and money-out totals with the statement before you categorise anything.

Stop when

Stop when the candidate totals match the statement, the date order is the one you will select in QuickBooks, and every blocking row has been corrected in the source. Keep the original export. The candidate is a reformatted copy, not a receipt that QuickBooks has accepted.

Do not keep changing the direction until a later screen stops complaining. That can swap a whole month of transactions. QuickBooks Self-Employed is not given a separate CSV layout in the current Intuit article. A pass for QuickBooks Online does not establish that Self-Employed or Desktop will accept the file. Desktop imports are often QBO, QFX or OFX instead of this CSV.

References

Use the linked specifications and product documentation to check behavior in your own environment.