QuickBooks HelpQuickBooksHelpIntuit

Format CSV files in Excel to get bank transactions into QuickBooks

by Intuit•36• Updated 2 weeks ago

Note: This article is for customers using QuickBooks Online (Advanced, Essentials, Plus, and Simple Start) and Sole Trader.

Instead of connecting your bank and credit card accounts to QuickBooks, you can upload them manually. Usually, you download this file from your bank, but before you can upload it into QuickBooks you need to make sure the data is in the correct format.

Before you start

Each bank's .CSV file format is different. This means you may not be able to import CSVs from certain banks since the files don't have the format QuickBooks needs. If your bank doesn't export to a .CSV file, you can also upload your statements to QuickBooks Online via Quicken (.QFX), QuickBooks Online (.QBO), or Microsoft Money (.OFX) files as long as they follow the following format.

Import CSVs from banks and make sure these files follow a specific format.

Learn about CSV formats

You can upload CSVs with either the following 3-column or 4-column format. These are the only columns QuickBooks can handle and this order.

The three mandatory fields needed to import successfully into QuickBooks are Date, Description and Amount columns.

The 3-column format

This format has a Date, Description, and Amount column.

ABC
1DateDescriptionAmount
21/1/2018Example of a payment-100.00
31/1/2018Example of a deposit200.00

The 4-column format

This format has a Date, Description, Credit (money out), and Debit (money in) column.

ABCD
1DateDescriptionCreditDebit
21/1/2018Example of a payment100.00
31/1/2018Example of a deposit200.00

If you see an error message when you upload, do the following:

  • Remove any zeroes (0) from the file. Leave those cells blank.
  • Correct any transactions that display numbers in the Description column.
  • Remove the word "amount" if it appears in the Credit or Debit column header. They should only read "Credit" and "Debit" without the word "amount."
  • Make sure the dates are in one format. We recommend you use: dd/mm/yyyy.
  • Mac users must save the file as a Windows CSV file.
  • Some banks add the day of the week in the Date column (for example, 20/11/2018 TUE). You need to remove the day of the week. To do this, split the date and the day of the week into separate columns:
    1. Open the CSV in Microsoft Excel.
    2. Highlight the column that contains the date.
    3. Go to Data then select Text to Columns.
    4. Follow the onscreen steps and select the following:
      • Original data type: Delimited
      • Delimiters: Space
      • Column data format: General

You can also follow the steps from Microsoft.

  • CSV files for credit card accounts may display transactions in reverse. For example, deposits may appear as negative amounts because paying your credit card reduces your balance.
  • To make sure these transactions are imported correctly, select the corresponding credit card account (not a bank account) when you upload the file into QuickBooks.
  • To post credit card transactions to your company file, select a credit card account (not a bank account) in QuickBooks.

Upload transactions into QuickBooks

When your file is formatted correctly, follow these steps to upload them into QuickBooks, and categorise them to keep your books tidy.

If you've uploaded the data to the wrong bank account, you'll need to exclude the transactions before re-uploading the CSV file to the right account.

We're here to help
If you're confused about how to review, match or categorise transactions, or need help with manually uploading them, let us know. Sign in to QuickBooks and start a discussion with a qualified QuickBooks expert in the community.

QuickBooks Online EssentialsQuickBooks Online PlusQuickBooks Online Simple Start

Sign in now for personalized help

See articles customized for your product and join our large community of QuickBooks users.