You can import bank transactions to QuickBooks Online using Excel CSV (comma-separated values) files.

CSV template format

QuickBooks Online can import CSV files that use either a 3-column or 4-column format.

3 Columns

The 3-column template has columns for the Date, a Description of the transaction, and the Amount of the transaction.

A B C
1 Date Description Amount
2 1/1/2018 Example of a payment -100.00
3 1/1/2018 Example of a deposit 200.00

 

Columns

The 4-column template has a Date column, a Description column, and both a Credit and a Debit column.

A B C D
1 Date Description Credit Debit
2 1/1/2018 Example of a payment 100.00
3 1/1/2018 Example of a deposit 200.00

 

These are the only columns QuickBooks Online can import.

You can enter any additional information, such as the category and payee for a transaction, after the import, on the For Review tab of the Banking page. QuickBooks Online will enter information automatically if you have set up any banking rules, or if QuickBooks Online recognises the descriptions of your transactions.

Each bank formats its CSV files differently, so importing a CSV file may not be possible for every financial institution.

If your bank doesn't support CSV files but offers Quicken (QFX), QuickBooks Online (QBO), or Microsoft Money (OFX), you can try to import your data and then upload more than 90 days of bank transactions through Web Connect.

Formatting CSV files for import

To  successfully import your bank transactions, your CSV file must be formatted correctly. If you try to upload an incorrectly-formatted file, you will receive an error, such as Error: We can’t upload this .csv file because some of the columns aren’t mapped correctly.

If you experience any issues importing your CSV file, make sure your file doesn't break any of the following formatting restrictions:

    • Special characters are not allowed. Because of this restriction:
      • Do not use ( ) for negative amounts (use -234, not (234) for negative figures)
      • Do not include currency symbols ($234 should be 234)
      • Do not use commas to separate thousands (2, 111 should be 2111)
      • Do not use characters such as #, %, and & anywhere in the file.
    • Zeroes (0) are not allowed anywhere in the file ($0 should be left blank)
    • Correct any transactions that display an amount in the Description column
    • Remove the word amount if it appears in the name of the Credit or Debit column (Credit amount and Debit amount are incorrect)
    • MAC users must save the file as a Windows CSV file
    • Correct and standardise the Date format to a single format (for example, dd/mm/yyyy)
    • Some banks provide the day of the week and include it in the Date column (for example, 20/11/2018 TUE in column A) which is not an acceptable format. To correct this, split the date and the day of the week in two separate columns.
      1. Highlight the column that contains the date.
      2. Go to Data then select Text to Columns.
      3. Follow the Convert Text to Columns Wizard, choosing the following:
        • Original data type: Delimited
        • Delimiters: Space
        • Column data format: General
      4. Select Finish.

CSV files for credit card accounts

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 credit card transactions post correctly to your company file, be sure to select a corresponding credit card account (not a bank account) in QuickBooks Online.

Import transactions from a CSV file

To import transactions from a CSV file:

  1. From the left menu, select Banking, and choose Banking.

  2. From the Update drop-down menu, select File upload.

    If you’ve never connected an account, you can select Upload transactions manually.

    You can also begin your upload from the Import Data page (select the Gear icon, under Tools, select Import Data, and select Bank Data).
  3. Select Browse and choose the file you downloaded from your bank.

  4. Select Next.

    If you haven't downloaded your CSV file yet, follow the on-screen instructions in the Upload file window to download your transactions and then select the file you downloaded.
  5. From the QuickBooks Account drop-down list, select the appropriate bank or credit card account and select Next.
  6. Map the statement fields according to your CSV file format and select Next.
  7. Select the checkboxes of the transactions to import and select Next.
  8. When prompted whether you want to import now, select Yes.
  9. Select Let’s go when the Import Completed notification appears.

Once the import is finished, you are directed to the Downloaded Transactions window where your imported transactions are displayed on the For Review tab for you to review, add, or match.