White
PapersWhite
Papers
Links: ITA FoxPro Developers Page | ITA Home Page
Overview
A QuickBooks import file is simply a tab-delimited text file with an .IIF extension. In the simplest terms, all you have to do is create the file and then import it into QuickBooks.
Step 1: Create the Import File
In your VFP application, write the necessary code to build a text file in format
that matches the specifications for the version of QuickBooks you are targeting.
Give it any name you like, but use an .IIF extension so QuickBooks will
recognize it. An example of such a file is provided later in this document.
Step 2: Import it into QuickBooks
In QuickBooks, go to the File menu and chose Import. In the Open File dialog,
select the file you created in Step 1 and click Open. QuickBooks will import the
records in this file.
That's really all there is to it. Well, OK, there are some details to know about, so keep reading.
A Little Background on QuickBooks
QuickBooks can import many different types of data, including chart of accounts, lists (customer, employee, terms, vendors, etc.), and transactions. This paper will limit itself to a discussion about importing dollar transactions, since that is probably one of the more common needs that developers encounter. The technique for importing other types of data is very similar, so if you need to import something besides transactions hopefully you can adapt the information in this paper to your particular needs.
Unlike many other computerized accounting packages, QuickBooks hides some of the accounting terminology and complexity from the user. Indeed, this is one of the reasons it is so popular. For example, in ordinary use, QuickBooks does not require you to work with debits and credits per se. Instead, it uses an activity-based paradigm for doing typical tasks such as "enter a bill", "pay bills", "create an invoice", and "receive payments".
Nonetheless, behind the scenes QuickBooks is still doing conventional double-entry bookkeeping. Therefore, when you create a file of dollar transactions to be imported into QuickBooks you must specify both sides of the transaction, debit and credit. In QuickBooks import files, credits are represented as negative numbers and debits as positive numbers.
QuickBooks uses the concept of "splits" to handle the offsetting entry or entries for a particular transaction. One side of the transaction will be a single entry whose amount is the total amount of the transaction; the other side of the transaction will be one or more offsetting entries (splits) that add up to the same amount as the first entry, but with the opposite sign. This concept is carried through to the structure of an import file, where a transaction must consist of a transaction record followed by one or more split records that add up to the same amount. An “end transaction” record is also required, making a minimum of three records you need to write to your export file for each transaction.
Structure of a QuickBooks Import File
The records in a QuickBooks import file are identified by a record code in the first field of each record. These codes are described in the QuickBooks help file, and may vary from version to version. However, in all versions that I have worked with, a transaction consists of a TRNS record, followed by one or more SPL (split) records, and terminated by an ENDTRNS record. Each such grouping of records is one transaction. An import file can contain any number of transactions simply by including multiple groupings of TRNS/SPL/ENDTRNS records.
The first three rows of the import file are header records containing the keywords that define the columns that are to be included in the detail records to follow. The record types in these headers rows are preceded by an exclamation point (!) to identify them as header records.
The specifications for the individual fields within each type of record have varied from version to version of QuickBooks, so you should refer to the help file for the particular version of QuickBooks that you are working with. Depending on your needs, you may find that you do not need to populate all of these fields for your transactions. Although QuickBooks can process import files where not all the possible fields are included, I have not found anything that says which fields are optional are which are required. For this reason, as well as to make your code as generic as possible, I think that it is a good idea to include all of the fields all of the time. Simply insert a tab character as the placeholder for the fields for which you are not supplying actual values. This will become clearer when we look at the example below.
Example
Here’s an example that follows the specifications for QuickBooks 2000. Note that in order to make these records fit in the width available in this document, each record is shown on several lines. In the actual file, each record is one line, terminated with a carriage return / line feed. For purposes of illustration, the tab characters are represented by the > symbol.
In this example, there are two transactions. Both are invoices. In each case, the TRNS record is posted to Accounts Receivable. Note that the amount field in the TRNS record is a positive number, representing a debit, which increases the balance in asset accounts such as Accounts Receivable. The SPL record is posted to the income account named “Sales”. It is a negative number, representing a credit, which increases the balance in income accounts such as Sales.
!TRNS > TRNSID > TRNSTYP > DATE > ACCNT > NAME > CLASS >
AMOUNT > DOCNUM > MEMO > CLEAR > TOPRINT > ADDR1 >
ADDR2 > ADDR3 > ADDR4 > ADDR5 > DUEDATE > TERMS >
PAID > SHIPDATE
!SPL > SPLID > TRNSTYP > DATE > ACCNT > NAME > CLASS > AMOUNT >
DOCNUM > MEMO > CLEAR > QNTY > PRICE > INVITEM > PAYMETH >
TAXABLE > REIMBEX > EXTRA
!ENDTRNS
TRNS > > INVOICE > 08/30/1999 > Accounts Receivable > JOHNDOE > >
17.80 > 350031 > > N > Y > > > > > > > Net 10 Days >
N > 08/30/1999
SPL > > INVOICE > 08/30/1999 > Sales > > > -17.80 >
> > N > > 17.80 > Product > > N > > NOTHING
ENDTRNS
TRNS > > INVOICE > 08/30/1999 > Accounts Receivable > JANEDOE > >
25.30 > 350032 > > N > Y > > > > > > > Net 10 Days >
N > 08/30/1999
SPL > > INVOICE > 08/30/1999 > Sales > > > -25.30 >
> > N > > 25.30 > Product > > N > > NOTHING
ENDTRNS
In this example, it is our intent to use QuickBooks only to prepare customer statements and manage accounts receivable. The actual invoices, showing the line-item detail of the product(s) that were sold, are prepared and printed by the VFP application. Therefore, only summary information is being passed to QuickBooks. The customer statements printed by QuickBooks will list all of the invoices for the statement period, one invoice per line, showing the invoice number for each one. Note that the invoice number from the VFP application is being passed to QuickBooks in the DocNum field of the TRNS record.
You may also have noticed that the Price field of the SPL record contains the same amount as the Amount field, but as a positive number. In this example, we are not using QuickBooks to track the individual item detail on each invoice. So as far as QuickBooks is concerned each invoice has only one generic item named “Product”, which is priced at the total amount of the invoice. You can see this amount as a positive number in the Price column of the SPL records, along with the description “Product” which appears in the InvItem field.
Code Sample
There are any number of ways to create an ASCII text file from a VFP application, but one way is to use FoxPro’s low-level file functions. Here is some sample code that could be used to create the file shown above. For the sake of brevity, not all local memvars are explicitly declared.
* Abstract..: Create a QuickBooks transaction import file for invoices.
* Author....: Rick Borup, Information Technology Associates, Champaign, IL
* Create the export file, bail out with an error msg if unable to create it.
LOCAL lcExpName, lnFileNo
lcExpName = "myQBFile.IIF"
lnFileNo = FCREATE( lcExpName, 0) && Create and open for Read/Write.
IF lnFileNo < 0 && -1 means error, couldn't create the file.
??CHR(7)
WAIT WINDOW NOWAIT ;
"Error: Can't open file " + lcExpName + CHR(13) + ;
"The QuickBooks 2000 batch file could not be created."
RETURN
ENDIF
LOCAL lcHdrRec, lcSplitRec, lcEndRec, lcTab
lcTab = chr(9)
* Construct the three header records.
lcHdrRec = "!TRNS" + lcTab + "TRNSID" + lcTab + "TRNSTYP" + lcTab + ;
"DATE" + lcTab + "ACCNT" + lcTab + "NAME" + lcTab + ;
"CLASS" + lcTab + "AMOUNT" + lcTab + "DOCNUM" + lcTab + ;
"MEMO" + lcTab + "CLEAR" + lcTab + "TOPRINT" + lcTab + ;
"ADDR1" + lcTab + "ADDR2" + lcTab + "ADDR3" + lcTab + ;
"ADDR4" + lcTab + "ADDR5" + lcTab + "DUEDATE" + lcTab + ;
"TERMS" + lcTab + "PAID" + lcTab + "SHIPDATE"
lcSplitRec = "!SPL" + lcTab + "SPLID" + lcTab + "TRNSTYP" + lcTab + ;
"DATE" + lcTab + "ACCNT" + lcTab + "NAME" + lcTab + ;
"CLASS" + lcTab + "AMOUNT" + lcTab + "DOCNUM" + lcTab + ;
"MEMO" + lcTab + "CLEAR" + lcTab + "QNTY" + lcTab + ;
"PRICE" + lcTab + "INVITEM" + lcTab + "PAYMETH" + lcTab + ;
"TAXABLE" + lcTab + "REIMBEX" + lcTab + "EXTRA"
lcEndRec = "!ENDTRNS"
* Write the three header records.
=FPUTS( lnFileNo, lcHdrRec) && the !TRNS header record
=FPUTS( lnFileNo, lcSplitRec) && the !SPL header record
=FPUTS( lnFileNo, lcEndRec) && the !ENDTRNS record
* Now, scan the INVOICES table and write a set of three records
* to the export file for each invoice for the specified date.
SELECT invoices
GO TOP
SCAN FOR invoices.invdate = ldDate
* Set the value of the variable fields
lcCustNo = UPPER( ALLTRIM( invoices.custno))
lcDocNo = ALLTRIM( invoices.invoiceno)
lcShipDate = DTOC( invoices.shipdate)
lcPlusAmt = ALLTRIM( STR( invoices.invamt, 13, 2))
lcMinusAmt = "-" + lcPlusAmt
* Set the value of the columns for the transaction record
lcTRNS = "TRNS"
lcTRNSID = ""
lcTRNSTYP = "INVOICE"
lcDATE = lcShipDate
lcACCNT = "Accounts Receivable"
lcNAME = lcCustNo
lcCLASS = ""
lcAMOUNT = lcPlusAmt
lcDOCNUM = lcDocNo
lcMEMO = ""
lcCLEAR = "N"
lcTOPRINT = "Y"
lcADDR1 = ""
lcADDR2 = ""
lcADDR3 = ""
lcADDR4 = ""
lcADDR5 = ""
lcDUEDATE = ""
lcTERMS = "Net 10 Days"
lcPAID = "N"
lcShipDate = lcShipDate
* Create the transaction header record string, and write it to the file.
lcHdrRec = lcTRNS + lcTab + lcTRNSID + lcTab + lcTRNSTYP + lcTab + ;
lcDATE + lcTab + lcACCNT + lcTab + lcNAME + lcTab + lcCLASS + lcTab + ;
lcAMOUNT + lcTab + lcDOCNUM + lcTab + lcMEMO + lcTab + lcCLEAR + lcTab + ;
lcTOPRINT + lcTab + lcADDR1 + lcTab + lcADDR2 + lcTab + lcADDR3 + lcTab + ;
lcADDR4 + lcTab + lcADDR5 + lcTab + lcDUEDATE + lcTab + lcTERMS + lcTab + ;
lcPAID + lcTab + lcShipDate
=FPUTS( lnFileNo, lcHdrRec)
* Set the value of the columns for the split record
lcSPL = "SPL"
lcSPLID = ""
lcTRNSTYP = "INVOICE"
lcDATE = lcShipDate
lcACCNT = "Sales"
lcNAME = ""
lcCLASS = ""
lcAMOUNT = lcMinusAmt
lcDOCNUM = ""
lcMEMO = ""
lcCLEAR = "N"
lcQNTY = ""
lcPRICE = lcPlusAmt
lcINVITEM = "Product"
lcPAYMETH = ""
lcTAXABLE = "N"
lcREIMBEX = "NOTHING" && Not sure if "NOTHING" is really required or not.
lcEXTRA = ""
* Create the split record string, and write it to the file.
lcSplitRec = lcSPL + lcTab + lcSPLID + lcTab + lcTRNSTYP + lcTab + ;
lcDATE + lcTab + lcACCNT + lcTab + lcNAME + lcTab + lcCLASS + lcTab + ;
lcAMOUNT + lcTab + lcDOCNUM + lcTab + lcMEMO + lcTab + lcCLEAR + lcTab + ;
lcQNTY + lcTab + lcPRICE + lcTab + lcINVITEM + lcTab + lcPAYMETH + lcTab + ;
lcTAXABLE + lcTab + lcREIMBEX + lcTab + lcEXTRA
=FPUTS( lnFileNo, lcSplitRec)
* Create the end transaction record string, and write it to the file.
lcEndRec = "ENDTRNS"
=FPUTS(lnFileNo, lcEndRec)
ENDSCAN
=FCLOSE(lnFileNo)
Summary
Creating an import file for QuickBooks is not difficult. Probably the main thing to watch out for is differences in file formats between versions of QuickBooks. The help file that comes with QuickBooks contains the specifications you will need, and is probably your best source of information. In the help file, search for references to “import”, “export”, and “IIF”. Another valuable source of information is the QuickBooks Web site. There are several articles in the QuickBooks Knowledgebase that pertain to importing data. Go to the QuickBooks support site at http://www.quickbooks.com/support/ and do a search on “IIF”.
Acknowledgements
QuickBooks and QuickBooks Pro, among others, are registered trademarks and/or registered service marks of Intuit Inc., or one of its subsidiaries, in the United States and other countries. Microsoft, FoxPro, and Visual FoxPro are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other brands or product names are trademarks or service marks of their respective owners.
Rick Borup
Champaign, IL
February 13, 2001
About the author:
Rick Borup is an independent developer specializing in the design, development, and support of mission-critical business software solutions for small to medium-size businesses. Rick earned B.S. and M.B.A. degrees from the University of Illinois at Urbana-Champaign, and is owner and president of Information Technology Associates in Champaign, Illinois. He has been developing solutions with FoxPro/Visual FoxPro (VFP) since 1991, and is a Microsoft Certified Solution Developer (MCSD) and a Microsoft Certified Professional (MCP) in VFP.
Rick can be reached by email at rborup@ita-software.com, on the Universal Thread at http://www.universalthread.com, or via the Microsoft Developer Applications Forum on CompuServe.