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 the CSV2QBO utility. IMPORTANT: CSV2QBO is now replaced with the Transactions app, which converts from more formats and converts to more formats.

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

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

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

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

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

It has the first line, as column names. It is very helpful for the converter, if your CSV file has the first line, as 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 the 'Amount' column, this is the 'Payee' column, this is the 'Memo' column, this is the 'Check #' column, and so on.  

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 a 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 the 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 the button 'Change +/-' once per file and verify, that your expense amounts are negative and deposits are positive. 

Another important moment. Let's say some dates, especially for customers or users outside the US, you may see dates in 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 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, the 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.

The left sidebar is for the 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. 

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.  

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. These 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. 

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. 

These 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. 

The right part here is for the 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.  

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 from 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 an 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 the actual Account Number or use four last digits of the actual Account Number. So, when you use it, you don't have to remember which Account Number you use for which account. 

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 a 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.  

You can set the 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 the 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 a 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.  

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 Quickbook's 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.  

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.   

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.  

'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 to 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.  

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

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'. 

Choose and open a sample.qbo.  

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 the account. You unlink it first to import a QBO file, and then you unlink it again and link it back to your Bank. 

Web Connect file was successfully imported. Where is it 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 the 'Transaction List' button to review transactions. Why do we have to review transactions?  

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 does not match the Vendor records you have in Quickbooks. So, Payee, you see as 'Downloaded as'.  

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

'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 a default expense or income account. Because this account will be prefilled here when you choose Vendor record. 

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

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.