Format CSV files in Excel to get bank transactions into QuickBooks

Instead of connecting your bank and credit card accounts to QuickBooks Online, you can upload them manually. Before you can upload the file from our bank, you need to make sure the data is in the correct format.

If you need help with transactions, you can partner with QuickBooks Live Experts and feel more confident. Find out more about QuickBooks Live Bookkeeping.

For a better experience, open this article in QuickBooks Online. Launch side-by-side view

Learn about CSV formats

You can upload CSVs with either the 3-column or 4-column format to QuickBooks.

Each bank formats CSV files differently. This means you may not be able to import CSVs from certain banks since files don't have the format QuickBooksneeds.

• If your bank doesn't support CSV files but offers Quicken (QFX), QuickBooks Online (QBO), or Microsoft Money (OFX), you can try to import your data and then upload more than 90 days of bank transactions through Web Connect.

3-column format

This format has a Date, Description, and Amount column.

4-column format

This format has a Date, Description, Credit, and Debit column.

Format your CSV file

If you see an error message when you upload, do the following:

• Zeros: Remove any zeros (0) from the file. Leave those cells blank.
• Description: Correct any transactions that display numbers in the Description column.
• Credit or Debit: 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."
• Mac: Mac users must save the file as a Windows CSV file.
• Dates: Make sure the dates are in one format. We recommend you use: dd/mm/yyyy.
• Days of the week: 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. Follow these steps or the steps from Microsoft.
• Open the CSV in Microsoft Excel.
  1. Highlight the column that contains the date.
  2. Go to Data then select Text to Columns.
  3. Follow the onscreen steps and select the following:
     • Original data type: Delimited
     • Delimiters: Space
     • Column data format: General

Tips for CSV files for credit card accounts

• Transaction order: CSV files for credit card accounts may display transactions in reverse‌ order. For example, deposits may appear as negative amounts because paying your credit card reduces your balance.
  • To make sure these transactions are added correctly, choose the credit card account (not a bank account) when you upload the file into QuickBooks Online.

Upload transactions into QuickBooks Online

When your file is formatted correctly, follow these steps to upload them into QuickBooks Online.