csv2qbo Convert | Help
Getting Started
csv2qbo Convert is a single step financial data translator to convert bank, and credit card transactions into the .qbo file format suitable for QuickBooks® software. Use CSV2QBO Convert to import transaction data into QuickBooks when the data comes from a spreadsheet program such as Microsoft Excel® or Google Docs® or was downloaded from your financial institution in CSV (spreadsheet) format.
To get started first set your QuickBooks account information and date formats with the button. It is important to select an FID. If your bank is not listed, then it is not supported by QuickBooks, and you need to select the FID of some other bank as a workaround. See more about FID's below.
Then select the button to choose a file to convert. This will bring up a standard file chooser to select your CSV Statement. There are two action buttons, plus the cancel button, at the bottom of the file chooser. Use the button to preview how .csv files will convert, and to assign and verify which column is which before doing the import. Once you have previewed a file, then use the button to do one-step conversion of other files directly to .qbo file format, suitable for input into QuickBooks.
Whenever opening a file from a different bank or that has a different layout, always first use Preview to verify the column setup. Use the pull-down list at the bottom of each column to select the correct type of information in that column. Be sure to select one column, one column, and either one column or both and columns. If you have a balance column, the column selection should be blank (to ignore it). You can also choose which transactions to convert. See more about Preview Mode below.
Verify that credits to the account are positive and debits are negative. If they are switched, then select the checkbox for and the amounts will be correctly output to your .qbo file.
Select at the bottom of the Preview Screen to finish the conversion and create your .qbo file suitable for import into QuickBooks. Run subsequent conversions of CSV statements from the same bank with select a file and thento create your .qbo file in a single step.
Installation
- Microsoft Windows® full install
- Download csv2qbo.exe for Windows, save the file to your computer, and run the installation program by double clicking the file.
- If you do not have have Java installed it will be automatically downloaded during the installation.
- Mac OS X® full install
- Download csv2qbo.dmg for Mac OS X, save the file to your computer. Locate the file in the download area, open it by double clicking, then and run the installer.app by double clicking it.
- If you do not have have Java installed it will be automatically downloaded during the installation.
- Portable Installation
- Download csv2qbo.zip and save the file to your computer.
- If running on Mac OS X the unzip will be done automatically as part of the download, and csv2qboportable.jar should be in your user download folder.
- If running on other operation systems run zip or winzip on csv2qbo.zip and extract csv2qboportable.jar to a suitable folder such as C:\Program Files\MoneyThumb.
- Make sure you have Java installed on your computer. If you do not have Java already installed, download it for free at www.java.com.
- Download csv2qbo.zip and save the file to your computer.
Entering License Information
On Microsoft Windows, the easiest way to enter the license is to copy the license file csv2qbo.lic from the product confirmation e-mail to the same folder where you installed csv2qboConvert - i.e. C:\Program Files\MoneyThumb\csv2qbo Convert.
Otherwise enter the license by copying the license string (CTRL-C) from the confirmation e-mail and pasting it (CTRL-V) into the license dialog. To enter the license string manually from within the program select the License button, and paste (or type) the full license code into the dialog.
After you enter your license, your license email will be shown in the program title bar, and in .
Preparation
There are two things to do before running csv2qbo Convert:
- Create a CSV file.
This can be downloaded from a bank or brokerage web site, created by the QIF2CSV counterpart to this program, or be manually created in Excel and saved as a CSV (Comma Delimited) file in Excel. See Appendix A for guidelines on creating a file from scratch.
- Get the account number of the QuickBooks account into which you want to import transactions. If you are creating a new account, then any number will suffice. If you wish to import transactions into an existing account, then QuickBooks will match up the account numbers, and you will want to import into the correct account.
Running csv2qbo Convert
On Windows or Mac OS X, double click the csv2qbo icon on your desktop.
You may also run csv2qbo Convert from the Windows Start Menu, or run csv2qbo.exe on Windows or csv2qbo Convert.app on Mac OS X.
If you are running the portable version, run csv2qboportable.jar by double clicking it, or starting it as a Java program.
Settings Dialog
Use the button to bring up the Settings dialog:
Setting QuickBooks Account Info
First use the pull-down menu to select the correct type for the .qbo file - Bank or Credit Card. In addition there are four pieces of account information that may be inserted into the .qbo file when it is created. The only one of these that is critical is the. This is a four or five digit number that QuickBooks uses to identify the financial institution. QuickBooks will validate the FID over the Internet when reading in the .qbo file, and it must be correct and valid, or QuickBooks will abort the file import.
Select "" in the dialog and it will bring up a window with all the QuickBooks Financial Institutions that are listed for that type of account. Type some or all of the name of your financial institution into the search box at the top to search within the list. Scroll down the list to find your bank and select it. The bank URL is also included to help resolve ambiguity if your bank has a name that is similar to other banks.
If your bank is not listed, then QuickBooks will not import .qbo files (Web Connect files) that are identified as being from that financial institution. The only workaround is to use an FID from some other bank. You can still name the account to include the name of your financial institution. So long as you as not also doing QuickBooks Direct Connect downloads into the same account, it will work just fine. There are some Financial Institutions that provide Direct Connect, but do not support Web Connect, and if you use one of these, you will have to create two accounts and move transactions from one to the other.
Just remember that the FID is critical, and although csv2qboConvert will run without one, it will give a warning, and QuickBooks will give an error when reading the .qbo file.
The .qbo formated files are required to have account information. All files require an account number and bank accounts also require a bank routing number. If you don't want to save your accounts numbers for security reasons, then you can skip entering this this information. If you do provide your account number to be inserted into the .qbo file, then QuickBooks uses that number to automatically determine which account to import into. If you are always importing into the same account, then csv2qbo Convert will save the information from session to session, so you do not have to re-enter it. For PayPal users, your account number is the email address you use to sign into PayPal. Note that csv2qbo Convert does not access the Internet at all, so any information entered is only saved on your computer, and is not sent over other web or to any other computers.
To determine the account number to use in the .qbo file, csv2qbo Convert will look in the following locations, in order.
- The account header in the CSV file, typically the first line in the file. This method is best if you are converting data for different accounts in QuickBooks on a regular basis, and don't want to always re-enter the numbers. To specify this information in the CSV file, use an account header of the form:
!Type:accounttype;accountname^FID|accountnumber
- Values from the dialog window (see above). This method is best if you are doing a one time conversion, or are always converting the same account. Once again, if you are concerned about entering your account number, then don't, and either manually edit the .qbo file after it is created, or match up accounts when doing the QuickBooks import.
- The CSV file name, if it is a number without any letters.
- As a last resort, csv2qboConvert will use an arbitrary default number for the routing number and account number. You will then have to manually match accounts when reading the .qbo file into QuickBooks.
The bank account routing number is required by .qbo for bank accounts (but not for credit card accounts). However, it is not actually used by QuickBooks, so if you don't specify one, csv2qbo Convert will insert a default value and QuickBooks will accept it.
Lastly, the currency needs to be specified. US Dollars are the initial setting, use the drop down to select a different currency.
Date Formats
csv2qbo Convert can read dates either in US format (month-day-year) or European format (day-month-year). Use the Settings menu to select the date format that is used in your CSV file. If your dates have the month name or abbreviation rather than a number, then this setting is not applicable. Note that there is no need to specify a date format for .qbo files.
Positive and Negative Charges
Normally bank statements will have charges as negative numbers and payments as positive numbers. That is what Quicken expects. Many credit card companies switch things so that charges are positive - showing an increase in your balance - and payments are negative. Use the dialog to select if this is the type of data in your CSV file.
Assigned Column Names
Column names can be preassigned to your conversions. Whenever you use Preview Mode, the column names are saved and automatically assigned to subsequent conversions. See the description of Preview Mode, in the following section. The current column names are displayed in the text box in this section.
You can also read predefined column setting from a CSV format file. csv2qbo Convert is shipped with a number of presets that facilitate conversion from popular CSV download sites, such as PayPal. To read presets select the button, and use the open file dialog to open a csv file with the column names. A preset CSV file is just a single line of text with the column names separated by commas. You can also save the current displayed column names to your own preset file with
Splitting transactions for QuickBooks
When reading transactions from payment systems such as PayPal, the transaction is often composed of a gross amount, a transaction fee, resulting in a net amount. Theoption specifies whether split transactions such as these create one transaction in the .qbo file, or whether each split generates a new transaction in the .qbo file. When transactions are split, the QuickBooks import will create one QuickBooks transaction that is the received payment amount, and another transaction that is the transaction fee. This enables you to match up payments with invoices, and to track transactions fees in a separate account. If the transaction is not split, such a transaction will be imported as a single transaction with the net amount. See more about specifying split amounts in the description of Preview Mode, in the following section.
Converting the CSV File
csv2qbo Convert can be run in two modes - Preview Mode and Express Mode. If you are just getting started, then use Preview Mode. If you have run CSV2QBO Convert previously or are sure that the columns names are all correct then it's faster to use Express Mode. To run either mode, start with the button.
Preview Mode
Select the button and this will bring up the file chooser dialog. Navigate to the folder containing the .csv file, select the file, and then select at the bottom of the dialog.
This will bring up a preview window that displays the contents of your CSV file. The account type at the top of the window will be fixed to the QuickBooks account type you chose in the menu. At the bottom of each column is a selector that contains the name of the data in that column. It may have already been set correctly by csv2qboConvert based on column headings in the CSV file. If it's not correct, use the pull-down to select the correct type of data. Each type can only be used in one column, so types that have already been used will be grayed out. If you have many columns, you can increase the width of the columns of interest by going to the header row and dragging the column separator to increase the width of the column, and of course drag a corner of the window to enlarge it as well.
Some credit card files also have the the signs of amounts reversed so that credits have a minus sign. In this case the checkbox for should be selected. This will ensure that debits and credits are correctly labeled in the .qbo file, and imported correctly into QuickBooks. The checkbox for will hide all the columns that do not currently have a name assigned. This is very useful for files that come from PayPal or Stripe that have too many columns for them all to be readable.
The only required fields are the , and depending on whether the transaction amounts are in one column or two, either or and . If your data has an column that has positive amounts for both credits and debits (i.e. mint.com) then you also need to select a column that has the type - credit/debit or CR/DB.
When the option is set under , you can specify which columns contain amounts that are to be split. Additional column names will be displayed for , , and . For these columns, if there is an amount present, it will generate its own transaction, with a payee of the column name. You can also define your own split names by using the column name. This will prompt you for the name to used for amounts in this column. Additionally, you can force the amounts in a column to be either positive or negative amounts by specifying a plus or minus sign before the split name.
To create a Memo that combines two different columns choose for the the first part of the memo, and for the second part. The text from the two columns will be combined into a single Memo, with a space between the two text strings.
When the column names are correct, select at the bottom of the preview window. The conversion will proceed, giving some statistics on how many lines were processed and create a .qbo file with the same name. If a .qbo file with that name already exists you will be prompted to overwrite it.
Preview Mode column settings will be automatically remembered, and will apply to subsequent Express Mode conversions. To clear Preview Mode, run a different file in Preview Mode.
Express Mode
Express mode can be used for all conversions, although it is highly recommended that whenever converting a csv file from a new source, you should first use Preview Mode to make sure the column setup is correct. Select the button and this will bring up the file chooser dialog. Navigate to the folder containing your input file, and select at the bottom of the dialog
. csv2qboConvert will run, giving some statistics on how many lines were processed and create a .qbo file with the same name. If a .qbo file with that name already exists you will be prompted to overwrite it.
Processing payment systems downloads (PayPal, Square, Stripe...) with Presets
Payment systems such as PayPal, Square, Stripe create .csv files which contain transactions which contain additional information beyond the net amount of the sale. Most commonly the downloaded .csv file will contain columns with the gross amount of the sale, the transaction fee, and the net amount of the sale. It may also contain a column with the refund amount, if any. When importing this information into QuickBooks, it is often desirable to have this information as multiple transactions. For example the gross amount of the sale would become one transaction, and the transaction fee would be a second transaction. This allows the two amounts to go to different QuickBooks accounts, and also allows the the gross amount to be matched up with a QuickBooks customer invoice.
Presets in csv2qbo Convert make this conversion very simple.
1. Select and read in the Preset file for the payment system processor with . csv2qbo Convert is shipped with a number of standard preset files for common payment systems and credit cards.
2. You will also likely want to select the checkbox for so that individual transactions are created for each amount.
3. Select and run the conversion with . You will automatically have the correct columns mapped, and create a .qbo file. If you wish to verify that the column names are correct, you can instead the conversion from the Open File dialog before creating the .qbo file.
You can also create your own Preset files if you wish to have different names for the transaction fee, refund, or have a payment system that has additional fields. You can save the column settings that are displayed in the dialog with . You can also create preset files with a spreadsheet program such as Microsoft Excel that creates .csv files. The file format is just a single line with the column names, separated by commas.
Importing .qbo files into QuickBooks
The easiest way to import the .qbo file into QuickBooks is simply to double-click it from Windows Explorer or Max OS X Finder. You can also read the .qbo file while running QuickBooks. Simply select , then from the pull-down menu select , then, and then . This will bring up the standard dialog, select the file, and then select. The transactions should be read into QuickBooks.
If you are using QuickBooks Online, then open the account into which you want to import the .qbo file. There is an menu in the upper right. Select the down-arrow next to , and then select . Select , navigate to the .qbo file on your computer, select it, and select , and The transactions should be read into QuickBooks Online.
One of the advantages of using .qbo file format is that QuickBooks 'remembers' which transactions it previously read, and will not read the same transaction again. If your CSV file has a transaction ID from your financial institution, then make sure that the column header is correct, and csv2qbo will use that transaction ID. csv2qbo will generate transaction Id's if they are not available, but they will not match up with direct downloads from your financial institution.
Trouble Shooting
Cannot find your Financial Institution in the list of FID's.
If your Financial Institution is not listed for the type of account your are imported (bank or credit card) then you will have to 'fool' QuickBooks by using the FID of some other Financial Institution. QuickBooks will NOT import transactions with FID's from banks that are not QuickBooks affiliates. You can use pretty much any other FID you want, one would suggest a similar name to your Financial Institution and/or a large bank that is unlikely to drop QuickBooks support. See the following section for more details as well.
QuickBooks Error: "QuickBooks is currently unable to verify the financial institution for this download. Please try again later."
When you import the .qbo file into QuickBooks, if you receive an error "QuickBooks is currently unable to verify the financial institution for this download. Please try again later." then the FID is incorrect. QuickBooks is very picky about this. The financial institution must be currently in good standing with QuickBooks for Web Connect download, and the account must be of the correct type. QuickBooks distinguishes between financial institution support of bank versus credit card transactions.
csv2qbo Convert will only display those FID's that are listed as being able to import Web Connect files, so if you don't see your bank or brokerage, QuickBooks will not accept Web Connect files from that financial institution. Also, even though a bank may be listed, QuickBooks may still give this error. That problem seems mostly to occur with small banks, perhaps they did not stay current with QuickBooks.
If you are running into a brick wall with QuickBooks FID's, the workaround is to use an FID for some other financial institution other than the one you actually use. However, if you already are downloading from your financial institution, then you would have to have two accounts and copy transactions from one to the other. Therefore, this is practical only if you don't already download directly from that financial institutional, or you import transactions very infrequently.
If QuickBooks is refusing to link a .qbo import to an existing account, then make sure that the account type was set correctly - see the section above on Setting QuickBooks Account Info. QuickBooks will only allow you to import credit card .qbo files to credit cards accounts, and bank account .qbo files to bank accounts. If the account number was not specified accurately in the QuickBooks Account Info, then QuickBooks will generally allow you to do a manual match, but it will also change the account number in QuickBooks.
No transactions when import the .qbo file into QuickBooks
Review the log in the csv2qbo Convert log window. Often the cause is a missing header - see next paragraph.
csv2qbo Convert Error: No account header found, Using default account header...
If the csv2qbo Convert log windows has the error, "No account header found, Using default account header", then it likely means that you are trying to read a CSV file whose first line does not label what each column contains. Some credit card companies create CSV files like this. Either Use Preview Mode to specify what is in each column, or use Notepad or Excel or any text editor to insert a line at the beginning of the CSV file that labels each column. For example, for AMEX the line would be
Date,Number,Amount,Payee,Memo
csv2qbo Convert Error: Unknown section header type
If the CSV2QFX Convert log windows has the error: "Unknown section header type", then it means that there is no information on what type of account is being processed, or the type is not recognized. Either set the account type in , run in Preview Mode to define the account type , or edit the CSV file to add a line starting with !Type. See Appendix A for a full list of types supported by !Type.
csv2qbo Convert Error: Incomplete header...
There are missing columns which are needed to process the CSV file. The date might be missing, there might be a credits column without a debits column, or similar types of missing data. Review the column names in Preview Mode, or edit the CSV file to label the columns.
Missing Information
If not all transaction information is being input into QuickBooks, then verify that the CSV file has headers that correspond to the names in Appendix A. Although csv2qbo Convert recognizes a wide variety of alternative names, the program or web site that created the CSV file might be using some names for column headers that are not being recognized. Review the column settings in Preview Mode, or edit the CSV file to correct the column names.
Switched Information in QuickBooks
If you are importing the .qbo file into QuickBooks and information is switched (i.e. the Payee is what you expected for some other field) then the headers in the CSV file may be misunderstood. Either use Preview Mode to correct the column description, or look at the beginning of the conversion log to see what columns are being used for what information, and edit the headers in the CSV file accordingly.
If the amounts are switched (i.e. credit card charges are showing up as positive rather than negative) then use the menu to select and rerun the conversion.
Multiple Accounts in a singe file
Because a .qbo file can only contain transactions from a single financial institution, csv2qbo Convert will only process the first account found in a CSV file when creating a .qbo file.
Saving the csv2qbo Convert Log
After csv2qbo Convert has run, you may wish to save the log information to a file. Select the Save Log button. This will bring up a File Save dialog. Simply specify a file name and select Save.
To clear the log information select the button.
Appendix A
Spreadsheet conventions
If you want to create or edit the CSV file, the following is is a complete description of the conventions used by csv2qbo Convert.
By way of example, a simple bank account might look like this:
!Type:Bank;My bank account^23456|1111222233334444 |
||||
Date |
Number |
Payee |
Amount |
Memo |
5/1/2011 |
Opening Balance |
0 |
||
5/2/2011 |
Deposit |
100 |
From David |
|
5/10/2011 |
ATM |
ATM Withdrawal |
50 |
|
5/18/2011 |
INT |
Interest Paid This Period |
0.01 |
|
First Line
The first line for an account must have the type of the account, and may optionally have the account name, FID, and account number. The punctuation is a semi colon after the account type, an up-arrow after the account name, and a vertical bar after the FID.
!Type:accounttype;accountname^FID|accountnumber
The is one of the following:
-
- Bank - checking and savings accounts
- CCard - credit card accounts
The is the name of the account being created, the is the QuickBooks FID, a 4 or 5 digit number that is checked by QuickBooks, and the is the account number that QuickBooks can use to match accounts.
If the first line does not have this type of information, then csv2qbo Convert will look at the column headers to determine the account type, and read the rest of the information from the QuickBooks Account Info dialog.
Columns
All data must be in columns, and the columns should have the column name at the top of the column. Normally the column headers are in the second line of the spreadsheet, but csv2qbo Convert will search until a header row is found. The order of the columns is not important, and only the columns in bold are required.
Many alternate column names are used by various financial institutions, and most of them are recognized by csv2qbo Convert. If your CSV file does not have column headers, or uses different names, then running under Preview Mode is the easiest way to assign the column names for your CSV file.
The following is the list of 'standard' column names and their meanings for different account types.
Bank and Credit Card Accounts
-
- Date - transaction date
- Number - check number (for bank accounts only)
- Payee - payee or merchant
- Payee & Memo - payee or merchant, also copied to the start of the memo field
- Amount - Amount of the transaction in $ or other currency. Alternatively a credit and a debit column can be used
- Credit - credit amounts
- Debit - debit amounts
- Type - transaction type, such as credit or debit
- Transaction ID - the unique transaction ID
- Memo - memo or description of the transaction
- Memo Add-on - addition description to be added to the memo
- Gross Amount - when splitting transactions, gross amount of transaction
- Transaction Fee - when splitting transactions, the transaction fee charged
- Refund Amount - when splitting transactions, amount of refund
When the split transaction option is set, split transactions will be created by using a column header of
Split<Category>
The Category will become the payee name for output transactions after the first one created for this split transaction
Saving a CSV file from within Microsoft Excel
- Do a,
- At the bottom of the File Save Dialog select the file to be .
- Select .
- When you get a popup warning that some features in the file may not be compatible with CSV (Comma Delimited) format, select to keep this format and the CSV file will be saved.
Saving a CSV file from within Google Docs
- Do a, and then .
- This will start a file download. Select to download.
- This will bring up a file chooser dialog to save your file. Just select an appropriate folder and select and the CSV file will be downloaded.