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.
The 3-column template has columns for the Date, a Description of the transaction, and the Amount of the transaction.
|2||1/1/2018||Example of a payment||-100.00|
|3||1/1/2018||Example of a deposit||200.00|
The 4-column template has a Date column, a Description column, and both a Credit and a Debit column.
|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.
After the import you can enter any additional information, such as the category and payee for a transaction, 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 recognizes 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 message.
Here are some formatting restrictions for CSV files:
- Special characters are not allowed:
- Characters such as #, %, and & anywhere in the file.
- Using brackets ( ) for negative amounts: (234) should be -234.
- Currency symbols: $234 should be 234.
- Commas to separate thousands (2, 111 should be 2111).
- Remove zeros (0) from the file and leave those cells blank.
- Correct any transactions that display a number 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 standardize 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), which is not an acceptable format. To correct this, split the date and the day of the week into two separate columns:
- Highlight the column that contains the date.
- Go to Data then select Text to Columns.
- Follow the Convert Text to Columns Wizard, choosing the following:
- Original data type: Delimited
- Delimiters: Space
- Column data format: General
- 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:
- Go to the Banking menu and select Banking.
- 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 Settings ⚙, under Tools select Import Data, and select Bank Data).
- Select Browse and choose the file you downloaded from your bank. If you haven't downloaded your CSV file yet, follow the onscreen instructions in the Upload file window to download your transactions and then select the file you downloaded.
- Select Next.
- From the QuickBooks Account drop-down list, select the appropriate bank or credit card account then select Next.
- Map the statement fields according to your CSV file format and select Next.
- Select the checkboxes of the transactions to import and select Next.
- When prompted to import now, select Yes.
- Select Let’s go! when the Import completed notification appears.
Once the import is finished, you are directed to the Banking window where your imported transactions are displayed on the For Review tab. You can review, add, or match each transaction.
Focus on your business, not your books.
Get a dedicated team of QuickBooks-certified bookkeepers to set up and do all your bookkeeping for you. Learn more about QuickBooks Live Bookkeeping.