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.
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.
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.
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.
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.
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.
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.
If everything looks good, we accept the import by clicking “let’s go.”
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.
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.