Last Updated on October 17, 2020 by Editor Team
As we all know QuickBooks allows the users to import bank transactions in QuickBooks Online, and all users are able to manually import bank transactions in QuickBooks Online. But here the question occurs, ‘Is there any other way to import transaction in QuickBooks?’. Well, the answer is ‘Yes’. And today here we are with the way to import bank transactions using excel CSV files into QuickBooks Online.
Save Time & Effort
Get help from expert
Importing of bank transactions into QuickBooks Online by using excel CSV files is a time-saving, and effective way when you have a lot of data to import into Intuit QuickBooks. At that time, there’s no need to waste time entering everything manually. Because sometimes doing everything manually take more time than you have, also increases the chances of making mistakes, which could easily throw off your entire dataset. Let’s go to the depth for more ideas.
Make Your CSV File Ready
It is important to ready the CSV file, before importing bank transactions using Excel CSV files into QuickBooks Online. So first of all, we set up the CSV file.
CSV Template Format
Here we are importing the bank transactions using the CSV files, so it is necessary to know the CSV template 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|
Once you are done with importing, you will be able to make any additional details, such as transactions, on the Forward tab of the banking page. QuickBooks Online will automatically fill in the details if banking rules are properly established or it recognizes the details of your transaction as QuickBooks Online.
CSV files are formatted differently by each bank and due to this, it may not be possible for all financial institutions to import a CSV file. If your financial institution does not support CSV files but offers Quicken, QuickBooks Online, or Microsoft Money, you can upload your data and then upload bank transactions over 90 days using Web Connect Huh.
Formate CSV Files for Import
If you have properly formatted your CSV file then you can successfully import your bank transactions. If you upload a file that is not properly formatted, you will receive an error to be sure. Here are some formatting restrictions for CSV files (important for you):
- Special characters are not allowed in these features:
- Characters such as #,%, and anywhere in the file.
- Using parentheses () for negative signs should be (234) -234.
- Currency symbol: $ 234 must be 234.
- Thousands (2, 111 must be 2111) to separate the commas.
- Remove all the zeros (0) from the file and do not enter any information in those cells.
- You can make changes in any of the incorrect transactions that show a number in the Description column.
- You need to remove all the word amount if it shows up in the name of the Credit or Debit column.
- If you are using MAC Operating System then you must not save the file as a Windows CSV file.
- You can also make changes and standardize the Date format to a single format (for example, dd/mm/yyyy).
- Some of the banks offer the day of the week and include it in the Date section (for example, 20/11/2018 TUE), which is not an acceptable format. You can correct it by splitting the date and the day of the week into two different columns:
- Select the column where the date is mentioned.
- After that, navigate to the Data and then click on the option of Text to columns.
- Then, you need to follow the Convert Text to Columns Wizard, and at the last, you can choose the following options:-
- Delimited Original data type
- Delimiters: Space
- Column data format: General
- Select Finish.
CSV Files for Credit Card Accounts
CSV files for credit card accounts can show transactions in reverse. For example, a deposit may appear as negative because the balance decreases when you pay your credit card.
You need to ensure that the credit card transaction is properly posted in your company file by selecting the corresponding credit card account in QuickBooks Online.
Procedure to Import Bank Transactions Using Excel CSV Files into QuickBooks Online
Follow the procedure to import bank transactions using excel CSV files into QuickBooks Online:
- First, you need to go to the left Banking menu option and then click on the option of Banking.
- Click on the option of File upload. If you have never connected to an account, you can select the Upload transactions manually option. You can also begin your upload from the Import Data page, and follow these given below steps:-
- Go to the Settings option
- Under Tools click on the option of Import Data.
- And then click on the Bank Data option.
- After that click on the option of Browse and choose the file, that you want to download from your bank. If you haven’t downloaded your CSV file yet, then you need to follow the onscreen instructions in the Upload file window to download your transactions and then click on the option of the file that you want to download.
- Click on the Open option, and then choose the Next option.
- Go to the QuickBooks Account drop-down list, click on the bank or credit card account option, and then choose the Next option.
- After that, choose a date, date format, Description, and Account. Note: You can choose 1 column or 2 columns.
- Click on Next.
- Select the checkboxes of the transactions to import and then click on the option of Next.
- Click on the option of Yes.
- Click on the given option of “Let’s go!” when the Import completed notification appears.
After the import is finished, you are directed to the banking menu or the transaction menu, where your imported transactions are displayed on the For Review tab. You can review, add, or mail every transaction.
We hope, now you are able to import bank transactions using excel CSV files into QuickBooks Online. Follow the above steps carefully without skipping any step. You can get our ProAdvisor support at any time and anywhere if in case you are unable to perform this activity.