![QuickBooks Help](https://uxfabric.intuitcdn.net/web-app/sh-exp-sbg-app-experience/sbg_ohh_icon.19f2ae89.png)
Format CSV files in Excel to get bank transactions into QuickBooks
by Intuit•7• Updated 3 weeks ago
After you’ve downloaded the .CSV file from your bank, you need to do a little bit of formatting before uploading it to QuickBooks Online.
Before you start
Every bank sets up their .CSV files differently. This means you might not be able to import .CSV files from some banks because the files don't have the format QuickBooks needs. If your bank doesn't export files to .CSV, you can also upload your statements to QuickBooks Online through Quicken (.QFX), QuickBooks Online (.QBO), or Microsoft Money (.OFX) or text (.TXT) files. But these files must follow a specific format.
Learn about CSV formats
You can upload CSVs with either the following 3-column or 4-column format. These are the only columns QuickBooks can handle.
The 3-column format
This format has a Date, Description, and Amount column.
A | B | C | |
1 | Date | Description | Amount |
2 | 1/1/2018 | Example of a payment | -100.00 |
3 | 1/1/2018 | Example of a deposit | 200.00 |
The 4-column format
This format has a Date, Description, Credit, and Debit column.
A | B | C | D | |
1 | Date | Description | Credit | Debit |
2 | 1/1/2018 | Example of a payment | 100.00 | |
3 | 1/1/2018 | Example of a deposit | 200.00 |
If you see an error message when you upload, do the following:
- Remove any zeroes (0) from the file. Leave those cells blank.
- Correct any transactions that display numbers in the Description column.
- Remove the word "amount" if it appears in the Credit or Debit column header. They should only read "Credit" and "Debit" without the word "amount."
- Make sure the dates are in one format. We recommend you use: dd/mm/yyyy.
- Mac users must save the file as a Windows CSV file.
- Some banks add the day of the week in the Date column (for example, 20/11/2018 TUE). You need to remove the day of the week. To do this, split the date and the day of the week into separate columns:
- Open the CSV in Microsoft Excel.
- Highlight the column that contains the date.
- Go to Data then select Text to Columns.
- Follow the onscreen steps and select the following:
- Original data type: Delimited
- Delimiters: Space
- Column data format: General
- CSV files for credit card accounts may display transactions in reverse. For example, deposits may appear as negative amounts because paying your credit card reduces your balance.
- To make sure these transactions are imported correctly, select the corresponding credit card account (not a bank account) when you upload the file into QuickBooks Online.
- To post credit card transactions to your company file, select a credit card account (not a bank account) in QuickBooks Online.
Next steps
Now that your file is formatted correctly, you can manually upload your transactions into Quickbooks Online, and categorise them to keep your books tidy.
We're here to help
If you need additional help or have questions, or you require assistance at any point in this process, our Customer Success team is happy to help.
Sign in to QuickBooks and start a discussion in our QuickBooks Community or join one of our Facebook Groups to connect with other like-minded QuickBooks customers.
You must sign in to vote.
Sign in now for personalized help
See articles customized for your product and join our large community of QuickBooks users.
More like this
- Manually upload your transactions, receipts, and bills into QuickBooks Onlineby QuickBooks
- What to do when your bank no longer supports your bank feed - QuickBooks Self-Employedby QuickBooks
- Common errors for importing bank transactions using CSV in QuickBooks Onlineby QuickBooks
- What to do when your bank no longer supports your bank feed - QuickBooks Onlineby QuickBooks