How to import CSV or Excel files as QBO into Quickbooks

How to import CSV or Excel files as QBO into Quickbooks

This tutorial shows how to convert a CSV or Excel file with transactions, typically downloaded from your bank or you can create it by yourself, to QBO (Web Connect) format and import into Quickbooks desktop. We will use CSV2QBO utility.

Click PC download, if you download for PC or for Mac, if you download for Mac. The utility is digitally signed and you can verify, that it is verified publisher ProperSoft Inc.

How to import CSV or Excel files as QBO into Quickbooks Step 1: verified publisher

We will use a sample.csv file. You can download a sample.csv file from https://www.propersoft.net page, as well.

How to import CSV or Excel files as QBO into Quickbooks Step 2: open sample csv

Open the CSV file, it has four transactions: three expenses and one deposit.

How to import CSV or Excel files as QBO into Quickbooks Step 3: sample csv review

Look at the file, by clicking on the 'Source Tab'. There is a 'Table View' tab, that shows the CSV file is a Table.

How to import CSV or Excel files as QBO into Quickbooks Step 4: source tab table view

And there is a 'Raw View' tab, that shows how actually the file looks like, if you open it, as a text file.

How to import CSV or Excel files as QBO into Quickbooks Step 5: source tab raw view

It has the first line, as column names. It is very helpful for the converter, if your CSV file has the first line, as a column names in common English words, like Date, Amount, Debit, Credit, Payee, Memo, Check #, Type. The converter is able to understand this column names and map, without your manually mapping the file. It configures out, that this is the 'Date' column, this is 'Amount' column, this is 'Payee' column, this is 'Memo' column, this is 'Check #' column and so on.

How to import CSV or Excel files as QBO into Quickbooks Step 6: column names

What do you see at the Transaction tab? You see how the converter understood your CSV file. If your CSV file has multiple Date columns, it may choose the first column or the column it decides most appropriate. But it could be not the column you want to use, so you can review Mapping if needed or you can reassign columns. Or let's say some columns have some Payee and Memo columns and they could be switched, like the converter decides, especially if the columns are not properly marked and there is no first line, as column names. It sees the Text column and uses, as a Memo column, it is another column. Text column uses as Payee, but for your specific file it could be different way, like this column has to be Payee, this column has to be Memo or your CSV file doesn't have Payee and Memo, it has just a long description, so you can use this description for both Memo and Payee. Another important moment to look at, that expenses must be negative and deposits must be positive. It applies the same role for Checking Account, Saving Account, Credit Card. But for some Credit Card files, the CSV file applies in an opposite way, Amount applies in the opposite way. What does it mean? It usually looks like this: some Credit Card files have expenses as positive numbers because from Credit Card Company perspective view is increasing the balance of your Credit Card and payments to the account are negative numbers since your increasing balance on your Credit Card. But for QBO files the rule doesn't apply. So, if the Credit Card or Checking Account expenses must be negative and deposits or payments to the account must be positive. If you see Data, like this, you should click a button 'Change +/-' once per file and verify, that your expense amounts are negative and deposits are positive.

How to import CSV or Excel files as QBO into Quickbooks Step 7: change button

Another important moment. Let's say some dates, especially for customers or users outside US, you may see dates in the different formats. So you may see dates on your CSV file, in this case, the Date applies as date/month/year. For US users it could be month/date/year. The converter tries to understand what the date format on your CSV file. And for some obvious cases, like this one, you can see, that this is modern twelve, this is the month, this is four digits year. So it configures out itself, that this is the date format is day/month/year. On the Transaction tab, it shows, that the Date is always in the format: year/month/date.

Regard so how data on your CSV file, this is how you see the Date on the Transaction tab. So you can verify, that this is correctly parsed. If you see something not expected, let's say month and date are switched, you can tell the converter which format to expect. You can see, in this case, default is month/day/year, but the converter was able to figure out for these dates, that dates are day/month/year. In cases, when it's not obvious, for example, Jan, 8, you can adjust this and the converter will reload the file.

How to import CSV or Excel files as QBO into Quickbooks Step 8: date format

The left sidebar is for CSV file to Mapping, to tell the converter how the Data look like. For example, we want to change Payee and Memo. We click 'Review Mapping'. And then what do we see? That Memo is assigned to 'Memo' and Payee is assigned to 'Payee'. We can change it if needed. For example, your file doesn't have column names, then you would see Fill 1, Fill 2, Fill 3, Fill 4. This is like your preview. So you can see which one is Fill 1, Fill 2, Fill 3. And here you select, which Field you want to be Check #, which Field you want to be the Amount.

How to import CSV or Excel files as QBO into Quickbooks Step 9: csv mapper payee

Let's say you have two columns: one Debit, one Credit or one Withdrawal, one Deposit. Then you set Amount to 'Do not use', and set Debit Column to 'Debit Amount' and Credit Column to 'Credit Amount'. Sometimes you have two columns, one Amount column, but they are all positive. But there is another special column, that has values, like Debit, Credit. For this case, you still use for Amount and then you would use Record Type, to tell this is the column, that has Debit and Credit values and configure out which transaction is the expense and which transaction is income.

How to import CSV or Excel files as QBO into Quickbooks Step 10: csv mapper record type

The QBO file doesn't have the QBO format, doesn't carry expense, income accounts. So, when you import Data into Quickbooks, that's when you assign expense and income accounts to the transaction. This two columns: Account Type and Account Number are not for expense, income accounts, they are for multiple accounts. So if your CSV file has a Checking Account and Credit Card Account, this column tells which account is which and then you can use this setup.

How to import CSV or Excel files as QBO into Quickbooks Step 11: csv mapper record account type

The QBO files do not have Split capabilities for transactions. But you can use this Split setup, for example, for PayPal files to set: Split 1 is gonna be like fee, Split 2 is gonna be like net or Split 1 - net, Split 2 - fee. What it will do for the QBO file? It will create two separate transactions: one is for the fee, one is for the net. So when it is imported into Quickbooks, you can assign different expense accounts for the fee and for the net.

How to import CSV or Excel files as QBO into Quickbooks Step 12: csv mapper record split

This two columns: Output Account Type and Output Account ID, you can set them as part of Mapping. So later, when you use Mapping, you don't have to set Account Type as well as Account Number. It will be set as part of Mapping.

How to import CSV or Excel files as QBO into Quickbooks Step 13: csv mapper record output account type

The right part here is for output QBO file. The first thing to look at is Account Type. If we put the cursor on the Account Type, here you can see some description. So the Account Type is important to choose.

How to import CSV or Excel files as QBO into Quickbooks Step 14: account type description

If you are importing Credit Card files, you must select Credit Card. Quickbooks will read your Account Type from the QBO file and will allow you to import into Account, only into this same Account Type, that it sees on the QBO file. For example, the QBO file has a Checking Account, then it will show you Checking Accounts to choose. If the QBO file has a Credit Card Account, then Quickbooks will show you only Credit Card Accounts to choose in Quickbooks to import. If you have just one file or one account, the Account Number could be any number and you can set it as actual Account Number, you can use maybe four last digits of your actual account, you can put the whole account there if you like. It is important to use different Account Numbers for different accounts. If you convert for multiple accounts, if you have four or five accounts, please, make sure, to use different Account Numbers, when you convert. That's why to avoid confusion, use actual Account Number or use four last digits of actual Account Number. So, when you use it, you don't have to remember which Account Number you use for which account.

How to import CSV or Excel files as QBO into Quickbooks Step 15: account type

Currency for Quickbooks has to be the United States if you have Quickbooks United States Edition. For Canadian Quickbooks, it has to be CAD or the USD, if you have multi-currency setup. For Quickbooks US Edition it has to be US Dollars. Even if you are importing for the non-USD account, in Quickbooks US you must use US Dollars Currency.

How to import CSV or Excel files as QBO into Quickbooks Step 16: currency

You can set End balance if you like. It really is not important, but you can set it, if it is important for you, when you reconcile the account. Quickbooks will show you that End Balance when you import a QBO file. But it will calculate balance from actual transactions. Bank ID has to be all digits number. It can be any number and for different accounts, it could be the same number, but has to be number. Some software demands that Branch ID has to be set. For Quickbooks, it doesn't matter. Encoding, if you are importing some non-lighting characters you can use Unicode, Hebrew, Japanese, depending on what your accounting software requires.

How to import CSV or Excel files as QBO into Quickbooks Step 17: encoding

INTU.BID is something that is present on the QBO file. Quickbooks reads that number and checks that number is allowed to be, or the bank representing that number is allowed to be imported. The '3000' is a default number for ''Wells Fargo''. You can leave this number. ''Wells Fargo'' is usually the most stable from Quickbooks perspective, it usually always imports. But you can use different numbers, but you must use them carefully. For example, you can click 'Look up' and if you have Quickbooks installed on your computer, you will see the full list. 'Supports' Banking and Credit Card is importing. Some banks have just Banking, some banks have just Credit Card. For example, if we uncheck 'Credit Card', you can see some only Banking, so you cannot import Credit Cards.

How to import CSV or Excel files as QBO into Quickbooks Step 18: intu.bid credit card

And if we uncheck 'Banking', you cannot import Checking Account. If you mark both of them, you can choose any bank. It is really not important which Bank you choose. But you can choose the name of the Bank, which is close to your Bank, any name you like. The Bank label you see only during import. It is not relevant to the actual account you choose in Quickbooks. So you can import ''The Citizens State Bank of Curay'' only putting on the QBO file, but your actual account in Quickbooks could be from the different Bank. You are choosing an account to which you want to import your Data.

How to import CSV or Excel files as QBO into Quickbooks Step 19: intu.bid banking

The button 'US' - for US Customers (if you have US Edition of Quickbooks), 'Canada' - for Canadian Customers (if you have Canadian Edition of Quickbooks). It sets before value here, typical values to have your QBO file imported.

How to import CSV or Excel files as QBO into Quickbooks Step 20: intu.bid us, cad

'Duplicate Payee as Memo when missing' - if your CSV file has just one column, you can use this checkbox and duplicate both of them. It is important, that Payee is limited to thirty-two characters by QBO specification. And Memo is limited two hundred fifty characters by QBO specification. So you will see a shorter version in a Payee column and when you import into Quickbooks, you will see the shorter version for a Payee column.

How to import CSV or Excel files as QBO into Quickbooks Step 21: Duplicate Payee as Memo

So let's try to convert. Check Date, check Amount is negative and Deposits is positive and click 'Convert' button.

How to import CSV or Excel files as QBO into Quickbooks Step 22: click convert

In Quickbooks, the QBO file is a Web Connect file. In this example, we imported a true file. Click 'File' - 'Utilities' - 'Import' - 'Web Connect Files'.

How to import CSV or Excel files as QBO into Quickbooks Step 23: file, utilities

Choose and open a sample.qbo.

How to import CSV or Excel files as QBO into Quickbooks Step 24: open sample

So you can see, that the Bank label is '3000' - "Wells Fargo Bank", Account Type - Checking and the Account number is those numbers. Now it says to 'Use an existing account' or 'Create a new Quickbooks account'. We have no accounts yet, so we will choose to 'Create a new account'. The important moment here, that you may have the account, but you already use it to download transactions directly from your Bank. But you need to import some transactions for the previous month and you are using CSV2QBO to convert those transactions to QBO format. But then you try to import and it doesn't list here. You have to edit account. You unlink it first to import a QBO file, and then you unlink it again and link back to your Bank.

How to import CSV or Excel files as QBO into Quickbooks Step 25: create new account

Web Connect file was successfully imported. Where is it imported?

How to import CSV or Excel files as QBO into Quickbooks Step 26: data imported

It is imported into Bank Feeds, not into the actual account, but first into Bank Feeds. This is the second place where you can see the Bank Label: only when you import you see the Bank label and when you review transactions. That Bank label will not be seen anywhere once you review transactions. That's why it is not important, which Bank you choose for the QBO file. As long, as this Bank is allowed to be imported. Click on 'Transaction List' button to review transactions. Why do we have to review transactions?

How to import CSV or Excel files as QBO into Quickbooks Step 27: click on transaction list

Because it is the Quickbooks process to import QBO files. Usually, the process also called 'Matching'. When you match the Payee names on the QBO file, that usually do not match your Payee names or do not match Vendor records you have in Quickbooks. So, Payee, you see as 'Downloaded as'.

How to import CSV or Excel files as QBO into Quickbooks Step 28: downloaded as column

And the 'Payee' is not empty (the Vendor record is not selected yet).

How to import CSV or Excel files as QBO into Quickbooks Step 29: payee column

'Select Name Type' is where you select your Vendor records. When you create the Vendor record there is also default expense account to set, so in your Vendor records do set your default expense or income account. Because this account will be prefilled here when you choose Vendor record.

How to import CSV or Excel files as QBO into Quickbooks Step 30: select name type

If all transactions look good, you can select them and then approve. When you approve transactions, they will be added to the register.

How to import CSV or Excel files as QBO into Quickbooks Step 31: add, approve

There is also a checkbox 'Show Bank Memo'. It will show, that 'Memo' from the QBO file may have important information for you when you review transactions. So, do click to this checkbox, if you want to see the 'Memo' from the QBO file.

How to import CSV or Excel files as QBO into Quickbooks Step 32: Show Bank Memo

Related articles