cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Intuit

Format CSV files in Excel to get bank transactions into QuickBooks

Learn how to check the format of the CSV file you got from your bank.

Instead of connecting your bank and credit card accounts to QuickBooks, you can upload them manually. You usually download this file from your bank. Before you can upload it, you need to make sure the data is in the correct format. Here's how to check the format of bank transactions in a CSV file you plan to upload into QuickBooks.

Learn about CSV formats

Each bank formats CSV files differently. This means you may not be able to import CSVs from certain banks.

You can upload CSVs with either the following 3-column or 4-column format. These are the only columns QuickBooks can handle and this order.

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

Tip: You can add more transaction info, like the category or payee, after you upload them into QuickBooks Online.

Here are a few other things to check. 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:
    1. Open the CSV in Microsoft Excel.
    2. Highlight the column that contains the date.
    3. Go to Data then select Text to Columns.
    4. Follow the onscreen steps and select the following:
      • Original data type: Delimited
      • Delimiters: Space
      • Column data format: General

You can also follow the steps from Microsoft.

CSV files for credit card accounts may display transactions in reverse. For example, deposits may appear as negative amounts.

To make sure these transactions import correctly, select the corresponding 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.

Was this helpful?

You must sign in to vote, reply, or post