How To Import Transactions From Excel Into QuickBooks Online

Written By

Have you ever needed to import data from Excel into Quickbooks? 

In this scenario, we’re going to assume that maybe you intermingled some personal and business expenses on a personal credit card. You then went on to download the transactions from your credit card company as an Excel sheet and deleted all the transactions that weren’t business related. This will leave you with an Excel file of business transactions that will need to be imported into QuickBooks. You can accomplish this in a handful of ways but we’re going to show you just one here.

The first thing we need to do is clean up our excel file a little bit. Some Credit Card Companies add headings that can throw off the mechanism that QuickBooks uses to parse the banking file.

Transaction Excel Sheet

You might notice that the file we’re working with is an XLS file. This will not be usable by QuickBooks – we need it to be in CSV format. Converting the file is pretty straight forward. All you need to do is click, File and Save As and change the file type to CSV.

Use CSV file so Quickbooks can use it as an import.

Now that we have our file set up, we need to choose which account we want to add the file to. For this example, we’re going to choose the Petty Cash account.

Select petty cash

Once we click on Petty Cash, we’ll be taken to the accounts screen and we’ll be able to upload our file. Click downward pointing arrow next to the Updated button in the upper right-hand part of the screen. That will drop down a menu and we’ll be able to click on “File Upload” from there.

On the next screen, we need to tell QuickBooks where we’re getting our data from. It will want to know if we’re giving it a file local on our computer or uploading directly from our banking institution. In this example, we exported our CSV previously and that file lives locally on our computer.

Browse for file or connect your bank.
Select our register.csv file

Now that we’ve located our file, we need to work with QuickBooks to tell it how to use this file and where it needs to go. On the next page, you’ll be presented with a prompt that says “Select a QuickBooks account for the bank file you want to upload.” In our example, we’re going to assign register.csv to our Petty Cash register. You don’t always have to assign an existing register, you can always “add new” and make a new account for your situation.

Map to petty cash account

The next step is a bit more tricky but once you do it a couple of times, it will become second nature. Here, we are asked to tell QuickBooks how to interpret the data from each column and how to map that data into a transaction. For example, our QuickBooks transaction has a Date column and our excel spreadsheet has a date column for each transaction. QuickBooks also has a field for Description. The most similar column to description is Column 4, Memo. Finally and possibly the most important part is the data for the amount of the transaction. QuickBooks is smart enough to know that some times banks separate payments and deposits into different columns and sometimes banks lump all the figures into one column and use negative and positive values to distinguish the two types of transactions.

Identify column names

The next step is to assist QuickBooks in parsing through the transactions. We’ll click next and in the subsequent screen, QuickBooks will present all our transaction data to us as it has been interpreted. You’ll see the matching dates, descriptions, and amounts and if everything looks good, you can click next and begin the import.

Confirm import of transactions

If everything looks good, we accept the import by clicking “let’s go.”

Accept transactions

We still have some work to do after we finish the import as these items do not get put away automatically. We’ll be able to confirm this by looking at our Petty Cash tile in our register list. The dollar value for Petty Cash is still zero and we have 44 items in our to-do list.

View items in the to-do list

So there you have it! Now you know how to batch import your transactions into QuickBooks Online from a banking file. The remaining steps on how to process these transactions now that they’re in QuickBooks can be found in my How to Code Banking in QuickBooks Online article.

If you’d like to see a video walk-through of this information, please see below.

Need some one on one Quickbooks assistance?
Share Now

6 Responses

  1. Great beat ! I wish to apprentice while you amend your site, how could i subscribe for a blog site? The account helped me a acceptable deal. I had been tiny bit acquainted of this your broadcast offered bright clear idea.

  2. Data import and export is very important part of business analysis. If anyone is QuickBooks user and he need to import and export data, then your article will help him.

  3. I’d like to assign categories (expense accounts) and classes before I import so that I don’t have to do it inside of QBO. Is there a way to do that?

    1. Yeah, use a 3rd party app such as Receipt Bank (now Dext) or Hubdoc. There are a lot of options, those are just two to give you a place to start.

    2. Hi Jo Ellen, You could use an data import utility like Zed Axis that can assign Expense Accounts and a whole range of other fields like Class, Billable and Customer. They have a 30 day free trial so you can test it out importing into QuickBooks Online. Hope that gives you another option.

Leave a Reply

Your email address will not be published.

Read similar articles

How to Apply A Discount to an Invoice Using Canadian QuickBooks Online

In this tutorial you'll learn how to apply a discount to an invoice in Canadian QuickBooks.