CSV2QBO Converter Help File
© Copyright 2014 QIF Tools
The CSV2QBO converter is a single step, easy to use converter when you need to import data into QuickBooks® software from CSV files. Use CSV2QBO to import transaction data into QuickBooks when the data is either in Microsoft Excel® or was downloaded from your financial institution in CSV (spreadsheet) format.
To get started first set your QuickBooks account information with the menu. Then use the menu, to define the column setup in your CSV file, and then run the conversion and create your QBO file in a single step.
Copy the license file CSV2QBO.lic from the product confirmation e-mail to the same folder where you installed the converter - i.e. C:\Program Files\QIF Tools\CSV2QBO
If the license file is not available, you can also enter the license manually 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 Help, and Enter License, and paste (or type) the full license code into the dialog.
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.
If you have the Windows version installed simply run the program from the start menu or run CSV2QBO.exe. If you are running the portable version, double-click CSV2QBOportable.jar.
When the QFX file is created, first it needs to be of the correct type - Bank or Credit Card. In addition there are three pieces of information that may be inserted into the QBO file when it is created. The only one of these that is critical is the QuickBooks FID. 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. The easiest method to look up the QuickBooks FID for your bank is to run the converter, select , and . This will bring up a dialog to specify the QuickBooks Account Information. Select the as Bank, or Credit Card in the drop down list.
Select "" in the dialog and it will bring up a window with all the QuickBooks Financial Institutions 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 or brokerage is not listed, then QuickBooks will not import QBO files (Web Connect files) 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.
If for reason CSV2QBO does not find the QuickBooks FID list, you can search for fidir.txt yourself. You will need to turn on the viewing and searching of hidden and system files. Locate your QuickBooks version in one of the following folders, and then search in the sub folders.
Windows 7 or Vista: 'C:\Program Data\Intuit\'
Windows XP: 'C:\Documents and Settings\All Users\Application Data\Intuit\'
Mac OS X: 'Library/Application Support/'
The two other pieces of information for the QBO file are optional, but allow QuickBooks to automatically determine which account to import into. If you don't want to save your accounts numbers for security reasons, then you can skip entering this this information (see below). If you are always importing into the same account, then CSV2QBO will save the information from session to session, so you do not have to re-enter it. Note that CSV2QBO does not access the Internet at all, so any information entered will not be sent anywhere other than your computer.
The second piece of information is a bank account routing number, which is required by QBO for bank accounts (but not for credit cards). However, it is not actually used by QuickBooks, so if you don't specify one, CSV2QBO will insert a default value and QuickBooks will accept it.
The third piece of information is the account number. If for security reasons you don't want to specify it, CSV2QBO will insert a default value. You can then manually match accounts inside QuickBooks when reading in the QBO file.
To determine the account number to use in the QBO file, CSV2QBO will look in the following locations, in order.
!Type:accounttype;accountname^FID|accountnumber
Just remember that the FID is critical, and although CSV2QBO will run without one, it will give a warning, and QuickBooks will give an error when reading the QBO file. The routing number and account number just make things run more smoothly, especially if you are importing on a regular basis from the same accounts.
Also, note that the QuickBooks Account Info is only saved on your computer, and it not sent over other web or to any other computers. The information will be re-used on future conversions, unless of course you are converting a different account.
CSV2QBO 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.
Normally credit card statements will have charges as negative numbers and payments as positive numbers. That is what QuickBooks expects. Some credit card companies switch things so that charges are positive - showing an increase in your balance - and payments are negative. Use the menu to select if this is the type of data in your CSV file.
The converter can be run in two modes - Preview Mode and Express Mode. If you don't know that the columns are labeled correctly, then use Preview Mode. If you have run the converter previously and are sure that the columns are correct then it's faster to use Express Mode.
Select, and this will bring up a 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 first 20 lines of your CSV file. First select the correct account type at the top of the window. 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 CSV2QBO 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.
The only required fields are the , and depending on whether the transaction amounts are in one column or two, either or and .
To create a Memo that combines two different columns choose 'Memo' for the the first part of the memo, and 'Memo Add-on" 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 data is correct, select at the bottom of the preview window. The converter 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.
Preview Mode settings will be automatically remembered, and will apply to all subsequent conversions. To clear Preview Mode, select the menu and then .
Use this option when your CSV file has names for each column and the account type defined. Select , and this will bring up a file chooser dialog. Navigate to the folder containing the CSV file, select the file, and then select at the bottom of the dialog. The converter 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.
To use CSV2QBO from the command line or a script simply invoke it on Windows as:
CSV2QBO inputfile.csv
Or for the portable version
CSV2QBOportable.jar inputfile.csv
There is no need to specify an output file name, CSV2QBO will use the same name and an QBO extension. The log will be written to a file with the same name and a .log extension, or ERROR.log if the input file name is invalid.
Note that if the output file already exists, it will be overwritten. And if your input file name has any spaces in it, remember to use quotes - for example:
CSV2QBO "input file.csv"
The easiest way to import the QBO file is simply to double-click it from Windows Explorer. 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.
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.
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.
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 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.
Review the log in the CSV2QBO Status window. Often the cause is a missing header - see next paragraph.
If the CSV2QBO status windows has the error, "No account header found, unable to process", 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
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.
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 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.
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.
Because a QBO file can only contain transactions from a single financial institution, CSV2QBO will only process the first account found in a CSV file when creating a QBO file.
After the converter has run, you may wish to save the log information to a file. Select the File menu, and the Save Log menu entry. This will bring up a File Save dialog. Simply specify a file name and select Save.
If you want to create or edit the CSV file, the following is is a complete description of the conventions used by CSV2QBO.
By way of example, a simple bank account might look like this:
!Type:Bank;My bank account^23456|1111222233334444
DateNumberPayeeAmountMemo
5/1/2011Opening Balance0
5/2/2011Deposit100From David
5/10/2011ATMATM Withdrawal50
5/18/2011INTInterest Paid This Period0.01
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:
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 will look at the column headers to determine the account type, and read the rest of the information from the QuickBooks Account Info dialog.