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 your banking transactions to QuickBooks Online in a file with Excel CSV format, 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.
What Types of Files can you Import in QuickBooks
It is important to know that what file formats are supported by QuickBooks before you import your bank account transactions into it. QuickBooks support multiple file formats that one can import where 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.
|Microsoft Excel||xls, xlsx||Microsoft Excel 97 to 2003, 2007 to 2010|
|Text files||csv, txt||Comma Separated value and text files|
|Intuit Interchange Files||IIF||Legacy Intuit Interchange File format files|
|Bank Statement files||qbo, ofx, qif, qfx||Common bank, and credit card statement file format|
|QuickBooks XML||xml||QuickBooks XML format file|
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 condition 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.
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.
- QuickBooks Online does not support special characters in the CSV file so avoid getting any of the following symbols and characters.
- % ! @ # % ^ & *
- 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.
- Make sure to enter amount without using the currency symbol e.g. don’t use $542 instead use 542.
- A comma should not separate figures in thousands e.g. 2,452 should be entered as 2452.
- If you need to enter a figure that has a value 0, then you must leave the field blank.
- Credit and Debit columns must appear without any other word like amount or value.
- If you are on a Mac operating system, then you will need to save the file in Windows CSV format.
- Date format must be in date/month/year
- Remove any alphabets from the dates like 02/02/2019 SAT.
- 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 the import, follow the steps given below:
- Under the Banking section from the left side pane in QuickBooks Online, select the Banking option.
- Click the Update drop down list and choose File Upload.
- If No Account Connected popup appears then click Upload Transactions Manually.
- Click Browse and select the CSV file.
- Click Next and select the account on which you want to upload the transactions from the QuickBooks Account drop down menu.
- Click Next and checkmark the transaction check boxes and then click Next again.
- Click Yes to start the import process.
- 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. In case if you get any errors and don’t know how to resolve them, then you can get help from QuickBooks support team by calling the direct helpline number (844)-888-4666.