QuickBooks Desktop allows you to import bank transactions using Excel CSV to QuickBooks Online. If you are not sure about doing so, this article can be helpful for you as we will be assisting you to import bank transactions using Excel CSV files.
CSV Template Format
QuickBooks Online allows you to import CSV files in which a 3-column or 4-column format is being used.
A 3-Column template contains a column for 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 contains 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|
Only these columns can be imported by QuickBooks Online.
Once you are done with importing, you will able to provide any additional details like category and payee for a transaction, on the For Review tab of Banking page. QuickBooks Online will automatically fill in the details if the banking rules are set up appropriately or it QuickBooks Online recognizes the descriptions of your transactions.
The CSV files are formatted differently by each bank and due to this, importing a CSV file may not be possible for all the financial institutions.
If your financial institute doesn’t support CSV files but provides Quicken, QuickBooks Online, or Microsoft Money, you can upload your data and then upload more than 90 days of bank transactions using the Web Connect.
Formatting CSV files for import
You can successfully import your bank transactions if you have formatted your CSV file correctly. If you upload file which is not properly formatted, you will receive an error for sure.
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 all the zeros (0) from the file and don’t enter any information in those cells.
- Make changes in any of the incorrect transactions that shows a number in the Description column.
- Remove all the word amount if it shows up in the name of Credit or Debit column.
- If you are using MAC OS then you must not save the file as a Windows CSV file.
- Make changes and standardize the Date format to a single format (for example, dd/mm/yyyy).
- Some of the banks offers 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.
- Navigate to the Data and then click on 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 can show up transactions in reverse. For example, deposits can show up as negative because the balance gets reduced when you pay your credit card.
You need to make sure that credit card transactions post properly to your company file by selecting a corresponding credit card account in QuickBooks Online.
Import transactions from a CSV file
To import transactions from a CSV file:
- Open QuickBooks and from the Banking menu, click on Banking.
- Click on File upload. If you haven’t connected an account yet, you can click on Upload transactions manually. You can start uploading your data from the Import Data page (click on Settings ⚙ and from the Tools section, click on Import Data > Bank Data).
- Click on Browse and then select the file you downloaded from your bank. If the CSV file is still not downloaded yet, you can perform the onscreen prompts from the Upload file window and download all your transactions and then choose the file you have downloaded.
- Click on Next.
- Choose appropriate bank or credit card account from the QuickBooks Account drop-down list and then click on Next.
- Check the statement fields as per the CSV file format and then click on Next.
- Put a checkmark on the transactions to import and then click on Next.
- Click on Yes on the import now prompt.
- Click on Let’s go! when you get Import completed notification.
Once the import finishes, you will be taken to the Banking window where you will see all your imported transactions on the For Review tab. You can also review, add, or match each transaction.
Focus on your business, not your books.
You can have a dedicated team of QuickBooks-certified bookkeepers that will help you in setting up and perform all your bookkeeping for you. You can know more about it at QuickBooks Support department.
Technical Support For QuickBooks Desktop
Provided steps in this article should be helpful for you in importing bank transactions using the Excel CSV file. However, if you are not able to do so, it might be possible that your transactions are damaged, or you are not following appropriate guidelines to import your files. In such cases, you can connect with the support department of getsupportnumber.com by dialing our toll-free QuickBooks Support Phone Number +1800-567-0425 to get instant QuickBooks Support.