Last Updated on November 8, 2024

Do you want to learn what it takes to import your banking transactions to QuickBooks online? As QuickBooks gives a better picture of income/expense, profit/loss, and overall growth of a business, it is the most popular accounting software among small and mid-sized business owners. There are times when you might want to download and Import Banking Transactions in QuickBooks using Excel CSV Files, and this article will guide you through each step of completing the task. You will also learn about the common mistakes that you need to avoid before the import so you won’t get any errors. Follow the complete article for detailed information.

Reading this article, you will learn how to prepare banking transactions and import them to QuickBooks. However, if you don’t want to perform the steps on your own or are running short on time, call our QB professionals at 1.855.738.2784. Our expert team will guide you in importing the transactions into your QuickBooks application.

What types of files can you import into QuickBooks?

It is important to know what file formats QuickBooks supports before you import your bank account transactions into it. QuickBooks supports multiple file formats that one can import, whereas CSV (Comma Separated Value) is the most common file format in which users get their transactions from different banking websites. Down below is the list of file formats that one can use to import bank account transactions and list data in QuickBooks.

What CSV File Layout is Supported by QuickBooks

At present QuickBooks supports a CSV file that has 3 or 4 columns, importing any CSV file with more than 4 columns will give an error message “Error: We can’t upload this .CSV file because some of the columns aren’t mapped correctly.” The error message also appears if the file is not formatted appropriately or if it contains invalid characters. Following are the examples of 3 and 4 columns CSV file that can be imported in QuickBooks.

Example of a 3 Column Excel file

Example of a 4 Column Excel file

If you want to add more info like a payee or a transaction, then you can do it later from the For Review page under your online banking once the import is done. You can also set rules in QuickBooks Online that will automatically add information to the imported files. However, it is also possible that QuickBooks does not support the file that you have downloaded from your banking website, as every bank has a different CSV file format; in such conditions, you can either download a different format of supported files like QBO or OFX or manually edit the CSV file to make it acceptable by QuickBooks.

Directly Import Banking Transactions in QuickBooks using Excel CSV Files

We have discussed the import process and resolution steps for importing CSV files to QBO above. Now, let’s check how to directly import the banking transactions from a CSV file for QuickBooks Desktop users –

Note: Since the supported file when importing bank transactions in QuickBooks Desktop is a (.qbo) file, you must first convert the CSV file to QBO by using these steps –

1. Start with uploading your CSV file to DocuClipper, then map the columns to the appropriate QBO fields.
2. Review and edit transactions if needed, and download your data using QBO file format.

Once the .QBO file is ready; use the following instructions to download the web connect files in QB Desktop –

1. Open the QBDT File menu, choose Utilities, and select Import.
2. Click Web Connect Files, then locate the QBO file you saved and select Open.
3. When prompted, choose the Bank Account and select Create a new QuickBooks account.
4. Click Continue, hit OK, and go to the Bank Feeds Center to review your transactions.

Tips to Prepare the CSV File to Import into QuickBooks Online

Before you begin the steps of importing into QuickBooks online, your CSV should be preformatted. The CSV file format must be properly formatted so that QuickBooks can import it with little or no complications. There are, however, a few things that you have to pay attention to so that the data transfer process is flawless –

1. The QuickBooks Online application has three varied fields for banking transactions – Date, Description, and Amount. Ensure you have your transactions maintained in this order and remove any other fields from your CSV file to avoid any problems.
2. It is mandatory that the amount is properly filled and if there is a transaction with nil amount, then put ‘0’ in it. There must be some value in each of the Amount fields and you should not leave any one of them blank. This generally happens if you have a 4 column CSV file, so please check your file thoroughly.
3. There is no sign allowed in the Amount field such as money sign or any other sign like a comma. Eradicate these signs before you go further to export the file into QuickBooks.
4. If the Amount field is in a single column in your Excel CSV file, then the minus (-) sign should be used to display the transactions for money going out instead of brackets {}.
5. Exclude the details that are unrelated to transactions from the CSV file. Details like the account number and opening and Closing balances should be erased to prepare your data file for import.
6. Ensure you save the file after making these changes and then proceed further to initiate the data transfer.

Instructions to Edit CSV File for a Successful Import

Assuming that you have successfully downloaded the bank account transaction file from your banking website and it is ready for the editing, follow the do’s and don’ts mentioned below while editing your CSV file.

1. QuickBooks Online does not support special characters in the CSV file, so avoid getting any of the following symbols and characters.
2. % ! @ # % ^ & *
3. Putting a negative sign – is the proper way of showing a negative value e.g. -542, do not use brackets (542) to show the negative amount.
4. Make sure to enter the amount without using the currency symbol, e.g. don’t use $542; instead, use 542.
5. A comma should not separate figures in thousands; e.g., 2,452 should be entered as 2452.
6. If you need to enter a figure that has a value of 0, then you must leave the field blank.
7. Credit and Debit columns must appear without any other word like amount or value.
8. If you are using a Mac operating system, you will need to save the file in Windows CSV format.
9. The date format must be in date/month/year
10. Remove any alphabets from the dates like 02/02/2019 SAT.
11. Hit Save from the File

Steps to Import Banking Transactions from a Comma Separated Value (CSV) File

Once the CSV file is formatted correctly and ready for import, follow the steps given below:

1. Under the Banking section from the left side pane in QuickBooks Online, select the Banking option.
2. Click the Update drop-down list and choose File Upload.
3. If No Account Connected popup appears, then click Upload Transactions Manually.
4. Click Browse and select the CSV file.
5. Click Next and select the account on which you want to upload the transactions from the QuickBooks Account drop down menu.
6. Click Next, checkmark the transaction check boxes, and then click Next again.
7. Click Yes to start the import process.
8. After the import is completed, click Let’s Go.

After the transactions are successfully imported in QuickBooks, you can review them in For Review window and can make necessary changes to them.

Common Errors While Importing Bank Transactions into QuickBooks Online

You can come across various issues while importing banking transactions in QBO. These issues will prevent you from importing the transactions, or the import will result in erroneous data. The common issues and the respective solutions to resolve them are given below in detail –

Case 1: Incorrect Mapping while Importing CSV to QBO

Incorrect mapping of your CSV file will restrict QuickBooks from reading your data properly; thus, ensure you have selected the correct columns in the mapping stage. While importing CSV to QBO, erase the unnecessary columns beside Date, Description, and Amount to avoid confusion. Remember, the names of columns in the CSV file should be the same as those in QuickBooks. It is suggested that the column names be renamed if they differ even slightly.

Case 2: Formatting Issues while QuickBooks Online Import CSV Invoices

Different bank and financial institutions may create their CSV files in varied ways for QuickBooks Online Import invoices CSV. These formatting variations may create issues when you try to import CSV to QBO. Here are some general methods to avoid formatting differences in such scenarios :

1. Do not complicate the ‘Amount’ column, and keep it as simple as possible. For instance, no symbols or commas should be used. You can edit or modify the column by right-clicking and pressing the ‘Format Cells’ option. You must change this option to ‘General’ if it is set to ‘Currency.’
2. If you have used special characters in the ‘Description’ column, then it can prevent you from importing the CSV file to QBO. You ought to search for those special characters and delete them because they will only complicate things further.
   Tip: Use the ‘Ctrl + H’ keys to easily spot these special characters in the description of your CSV file.
3. Finally, ensure the same date format is followed from the mapping stage. You must check and ensure the date format is set correctly. Additionally, highlight the date column and move to the ‘Number’ dropdown at the top of your screen. Then, select the ‘Date’ option and then click the ‘Short Date’ option.

Conclusion

We have learned how to import banking transactions in QuickBooks using the CSV files in this detailed blog. However, sometimes, the steps given might be unsuccessful in importing the CSV bank transactions. In such case, you can contact our QuickBooks Support Team at 1.855.738.2784 to find out what the problem is and how to resolve it with expert guidance. 

FAQs

Edward Martin

Edward Martin is a Technical Content Writer for our leading Accounting firm. He has over 10 years of experience in QuickBooks and Xero. He has also worked with Sage, FreshBooks, and many other software platforms. Edward’s passion for clarity, accuracy, and innovation is evident in his writing. He is well versed in how to simplify complex technical concepts and turn them into easy-to-understand content for our readers.