In this article, we are going to show how to import bank transactions using Excel CSV files to QuickBooks Online. Some Banks only allow downloading of Excel CSV [comma separated values] files, therefore it is important to learn to import CSV files into QuickBooks. So, you can import your bank transactions on time. Read this entire article to have insight into importing bank transactions into QuickBooks Online. Here we are with all the details to guide you for related query, still having an issue contact us on: +1-844-405-0904
Different type of CSV Template Form
There are two different types of CSV Template Form, the first is 3 column format and the second is 4 column format. You can use either one of them to import transactions into QuickBooks Online.
Below are the examples of both kinds of CSV template forms, so you can have an idea which one to use for importing transactions.
3 Column Template
The format of the 3-column template is in such a way that in the first column you can enter the date, in the second column you can add a description, and in the third column, you can enter the amount of transaction.
| A | B | C | |
| 1 | Date | Description | Amount |
| 2 | 01-01-2019 | Example of a payment | -$400 |
| 3 | 01-01-2019 | Example of a deposit | $300 |
4 Column Template
In the 4-column template format, the first column is for the date, second is for the description, third is for the credit amount, and fourth is for the debit account.
| A | B | C | D | |
| 1 | Date | Description | Credit | Deposit |
| 2 | 01-01-2019 | Example of a payment | $100 | |
| 3 | 01-01-2019 | Example of a deposit | $200 |
In QuickBooks Online, these are the only columns that can be imported.
Although you can enter some additional information but only after the import is complete. You can easily add information like the payee of the transaction and category, for this, you need to go the Banking menu or Transaction menu and then click on the For Review tab. If you have already set up banking rules in QBO then this information will be automatically entered in the QuickBooks Online. If QuickBooks Online identifies the description of transactions then the information will be entered automatically.
It is not possible to import transactions using Excel CSV files into QuickBooks from every bank or financial institution because different banks have a different method for formatting their CSV files.
You don’t have to worry if your bank does not support the CSV files, you can also import bank transactions using Quicken file (QFX), QuickBooks Online (QBO) file, or Microsoft Money (OFX). If your bank transactions are older than 90 days then you can use Web connect to import them into QuickBooks Online.
Here are some tips before you start importing CSV file into QuickBooks
- QuickBooks can import 3 fields and those are Date, description, and amount.
- Every mandatory field should be filled otherwise an empty field can lead to errors. If there is any amount field that has no value then you need to enter 0 there. (Usually, it happens with the 4-Column template form.)
- In the amount field, you need to remove all the symbols as well as special characters i.e. currency sign, comma, percent sign, etc.
- If your amount field end within the one column then you should use the (-) minus sign to represent money instead of the brackets.
- Also, in the CSV file, you have to remove the non-transactional information like opening & closing balance, account numbers, etc.
Formatting CSV Files for Import
It is very important to format the CSV file before you start importing it into QuickBooks Online. If not formatted properly then you will surely encounter errors while importing the bank transactions.
- You need to remove all the zeroes (0) from the file and leave those cells empty.
- In the description field, if there are any numbers then you need to remove.
- In the Credit and Debit column, if you find out any word amount then you need to remove it. (In short, correct all the credit and debit amount.)
- If you are a Mac user then you need to save the file as a Windows CSV fils.
- You need to change the date format to a single format for example (dd/mm/yyyy). It means you need to select choose a one standard date format for the entire file.
- As we mentioned earlier different banks have a different method of formatting, some banks also add the day of the week in the Date column like 15/04/2020 MON, which is not a correct format. To correct this, you need to create a separate column for the day of the week. To do this follow the steps mentioned below,
- First, you need to highlight the column which includes the date.
- After that, you need to go to the Data, and here select Text to column option.
- Then, follow the instructions mentioned on the Convert Text to Column Wizard, you can choose any one of the following options.
- Original Data Type: Delimited
- Column Data Format: General
- Delimiters: Space
- Select Finish.
CSV Files for Credit Card Accounts
If you are CSV files for importing the credit card transactions then there is a possibility that transactions may display in reverse order. Let’s take an example to make it simpler, It could happen that your deposits may appear as a negative amount because when you pay your credit card bills it reduces the balance.
To successfully import the correct credit card transactions into QuickBooks Online it is important to select an appropriate Credit card account along with it.
Import Transactions using Excel CSV files to QuickBooks
Follow the steps mentioned below very carefully to import transactions from a CSV file.
- From the left side navigational panel select the Banking menu and then again click on the Banking.
- Then, select the File Upload option. If you have not connected your account before then you can select Update Transactions Manually option. If you want to upload your data Import Data Page, you can also do it (For this, go the Settings and then under the Tools select Import Data option, and then select Bank Data).
- After that, click on the Browse button, and then select the file that you downloaded from the bank. But if you have not downloaded the CSV file yet then you can follow the on-screen instructions in the Upload file window to download the transaction file. Once the download is complete select the CSV file.
- After that, click on Open and then select Next.
- Then, click on the QuickBooks Account drop-down menu and from the list choose the appropriate bank or credit card account and then select Next.
- Next, you need to choose the Date, Date Format, Description, and Account and select Next. NOTE: You can either choose column 1 or column 2.
- Checkmark the box stating Transactions to Import and then select Next.
- When the confirmation screen pop-up, select Yes.
- Once the import is completed, a notification will appear, click on Let’s Go.
As soon as the import is finished you will be redirected to the Banking menu or Transaction menu, here you can view the imported transactions in the For Review tab. In this tab, you can review, match, or add new transactions.
The Bottom Line
We hope that you find our article on how to import bank transactions using Excel CSV files to QuickBooks Online resourceful and valuable. Here, we have discussed the entire procedure for importing transactions from CSV files including preparing and formatting the CSV file.
If you have any queries related to the import bank transaction into QuickBooks or any other issues related to QuickBooks then you can have assistance from our Intuit Certified QuickBooks ProAdvisor. For more information contact us via our 24/7 toll-free customer support number +1-844-405-0904.