by Nick Marodis
| This article may not be the last word on how to export your data to QuickBooks®, but it will provide a good starting point from which to work. |
According to some statistics there are over 2.8 million users of Quicken's QuickBooks®! With that many out there, I can't be the only one who needs to import to A5 from QB or export from A5 to QB. Though this article will not deal with importing into A5 from QuickBooks I will mention a product that should work, found at www.datablox.com. [Editor's note: I have no experience with Datablox and have no idea whether Nick Marodis has tested Datablox with Alpha Five. You can write to Nick directly at nick@databasebydesign.com or write to Datablox before purchasing their product.]
Well, to the project at hand. QuickBooks will allow the importing of a comma delimited text file which is an easy thing for A5 to do. However, QuickBooks does require a special header for each column of data. It's at this point that it gets hairy. Quick Books offers examples of this at their web site and in the help files. Using both will help you get a better picture as to how your text file should be formatted.
This page is very useful: http://www.quickbooks.com/support/faqs/docs/w_iiffiles.html
Here is a script I designed to export my customer information to the Quickbooks iif file (Intuit Interchange File) format:
'******************Customer table****************
dim qb as c
dim aline1 as c
dim aline2 as c
dim tbl as p
dim fp as p
dim tp as p
dim stor as c
dim stor2 as c
dim stor3 as c
dim qry as p
dim count as n
dim count1 as n
dim count2 as n
dim query.description as c
dim query.order as c
dim query.filter as c
dim query.options as c
tp=table.current()
'Gets the values needed from the current table and assigns then to these var.
'qb=ui_get_file("Open QuickBooks","qbw32(*.EXE)","","X")
qb=tp.lqbfile
stor=tp.storein
stor2=tp.storein2
stor3=tp.storein3
tbl=table.open(:A5.get_path()+"\rainbow.dbf")
query.description="QBC"
query.order=""
query.options="M"
query.filter="Epqb=.f."
qry=tbl.query_create()
count=qry.records_get()
if count=0 then
goto invoice
end if
fp= file.create(var->stor,file_rw_exclusive)
comma=chr(44) 'chr(44) is a ,
statusbar.clear()
' this is the header for the customer file
aline1="!CUST"+comma+"NAME"+comma+"BADDR1"+comma+"BADDR3"+comma+"BADDR4"\
+comma+"BADDR5"+comma+"PHONE1"+comma+"FAXNUM"+comma+"NOTE"+comma+"CTYPE"+comma+\
"TAXABLE"+comma+"TERMS"+comma+"TAXITEM"
fp.write_line(var->aline1)
tbl.fetch_first()
count2=0
'Check to see if the customer is taxable.
while .not. tbl.fetch_eof()
isit=tbl.Taxable
if isit=.t. then
okisit="Y"
else
okisit="N"
end if
aline2="CUST"+comma+alltrim(tbl.Company)+comma+alltrim(tbl.address_1)+comma+alltrim(tbl.city)+comma+ \
alltrim(tbl.state)+comma+alltrim(tbl.zip)+comma+alltrim(tbl.Work_phone)+comma+alltrim(tbl.Fax_phone)\
+comma+tbl.Cust_id+comma+alltrim(tbl.Groupid)+comma+okisit+comma+alltrim(tbl.Terms)+comma+tbl.Qbname
fp.write_line(var->aline2)
count2=count2+1
statusbar.percent(count2,count)
tbl.fetch_next()
end while
fp.flush()
fp.close()
qry.drop()
tbl.close()
'****************************Invoice table*******************
invoice:
dim alineT1 as c
dim alineT2 as c
dim alineS1 as c
dim alineS2 as c
dim alineE1 as c
dim alineE2 as c
dim tbrcitem as p
dim tbitem as p
dim tblw as p
dim fp as p
One of the things that my script needs to do is open three tables and fetch to see if an invoice is found in either of the two child tables that match the parent table, invoice_header.
tbrcitem=table.open(:a5.get_path()+"\invoice_rcitems.dbf")
tbitem=table.open(:a5.get_path()+"\invoice_items.dbf")
tblw=table.open(:A5.get_path()+"\invoice_header.dbf")
tblw.index_primary_put("Cust_id")
query.description="QBI"
query.order=""
query.options="M"
query.filter="Epqb=.f."
qry=tblw.query_create()
count1=qry.records_get()
if count1=0 then
goto payments
end if
fp= file.create(var->stor2,file_rw_exclusive)
comma=chr(44)
'this is the start of the quick books header needed for the invoice text file
alineT1="!TRNS"+comma+"TRNSID"+comma+"TRNSTYPE"+comma+"DATE"+comma+"ACCNT"\
+comma+"NAME"+comma+"AMOUNT"\
+comma+"DOCNUM"+comma+"CLEAR"+comma+"TOPRINT"+comma+"NAMEISTAXABLE"+comma+"TERMS"
alineS1="!SPL"+comma+"SPLID"+comma+"TRNSTYPE"+comma+"DATE"+comma+"ACCNT"+comma+"NAME"\
+comma+"AMOUNT"\
+comma+"DOCNUM"+comma+"MEMO"+comma+"CLEAR"+comma+"QNTY"+comma+"PRICE"+comma+"INVITEM"\
+comma+"TAXABLE"+comma+"EXTRA"
alineE1="!ENDTRNS"
alineE2="ENDTRNS"
' this is the end of the header
fp.write_line(var->alineT1)
fp.write_line(var->alineS1)
fp.write_line(var->alineE1)
tblw.fetch_first()
tblw.change_begin()
tblw.epqb=.t.
tblw.change_end(.t.)
while .not. tblw.fetch_eof()
whichinv=tblw.Invoice_number
isit=tblw.Ltaxable
if isit=.t. then
okisit="Y"
else
okisit="N"
end if
if exist(whichinv,"invoice_items.dbf","invoice_nu").or. exist(whichinv,"invoice_rcitems.dbf","invoice_nu") then
alineT2="TRNS"+comma+Right(tblw.invoice_number,6)+comma+"INVOICE"+comma+dtoc(tblw.date)+comma+\
"Accounts Receivable"+comma+alltrim(tblw.company)\
+comma+str(tblw.total,15,2)+comma+alltrim(tblw.invoice_number)+comma+"Y"+comma+"N"+comma+okisit+comma+"NET 30"
fp.write_line(var->alineT2)
else
goto again
end if
query.description="QBIA"
query.order=""
query.options="M"
query.filter="Invoice_number='"+whichinv+"'"
qry=tbitem.query_create()
tbitem.fetch_first()
while .not. tbitem.fetch_eof()
isit=tbitem.Taxable
if isit=.t. then
okisit="Y"
else
okisit="N"
end if
alineS2="SPL"+comma+Right(tbitem.invoice_number,6)+comma+"INVOICE"+comma+dtoc(tbitem.Enterdate)\
+comma+"Other Income"+comma+alltrim(tbitem.Company)+comma+\
"-"+str(tbitem.Extension,15,2)+comma+alltrim(tbitem.invoice_number)+comma+alltrim(tbitem.Description)\
+comma+"Y"+comma+"1"+comma+str(tbitem.Extension,15,2)+comma+alltrim(tbitem.Product_id)+comma+okisit
fp.write_line(var->alineS2)
tbitem.fetch_next()
end while
query.description="QBIB"
query.order=""
query.options="M"
query.filter="Invoice_number='"+whichinv+"'"
qry=tbrcitem.query_create()
tbrcitem.fetch_first()
while .not. tbrcitem.fetch_eof()
isit=tbrcitem.Taxable
if isit=.t. then
okisit="Y"
else
okisit="N"
end if
alineS2="SPL"+comma+Right(tbrcitem.invoice_number,6)+comma+"INVOICE"\
+comma+dtoc(tbrcitem.Postdate)+comma+"Other Income"+comma+alltrim(tbrcitem.Company)+comma+\
"-"+str(tbrcitem.Rcext,15,2)+comma+alltrim(tbrcitem.invoice_number)\
+comma+alltrim(tbrcitem.Rcdescrip)+comma+"Y"+comma+"1"+comma+str(tbrcitem.Rcext,15,2)\
+comma+alltrim(tbrcitem.R_c_code)+comma+okisit
'if the taxes are to be charged this second SPL line is a must.
fp.write_line(var->alineS2)
alineS2="SPL"+comma+Right(tbrcitem.invoice_number,6)+comma+"INVOICE"+comma+\
dtoc(tbrcitem.Postdate)+comma+"Sales Tax Payable"+comma+"Tax Board"+comma+\
"-"+str(tbrcitem.Trcext)+comma+alltrim(tbrcitem.invoice_number)+comma+"MyTax"\
+comma+"Y"+comma+"1"+comma+str(tbrcitem.Trcext)+comma+""+comma+"N"+comma+"AUTOSTAX"
fp.write_line(var->alineS2)
tbrcitem.fetch_next()
end while
fp.write_line(var->alineE2)
again:
tblw.fetch_next()
end while
fp.flush()
fp.close()
qry.drop()
tbitem.close()
tblw.close()
tbrcitem.close()
'debug(0)
'********************************Payment Table**********************
payments:
dim alineT1 as c
dim alineT2 as c
dim alineS1 as c
dim alineS2 as c
dim alineE1 as c
dim alineE2 as c
dim tbl as p
dim fp as p
tbl=table.open(:A5.get_path()+"\payment.dbf")
tbl.index_primary_put("Cust_id")
query.description="QBP"
query.order=""
query.options="M"
query.filter="Epqb=.f."
qry=tbl.query_create()
count2=qry.records_get()
if count2=0 then
goto lastthing
end if
fp= file.create(var->stor3,file_rw_exclusive)
comma=chr(44) 'chr(44) is a ,
'Header for Payments
alineT1="!TRNS"+comma+"TRNSID"+comma+"TRNSTYPE"+comma+"DATE"+comma+"ACCNT"\
+comma+"NAME"+comma+"AMOUNT"\
+comma+"DOCNUM"
alineS1="!SPL"+comma+"SPLID"+comma+"TRNSTYPE"+comma+"DATE"+comma+"ACCNT"\
+comma+"NAME"+comma+"AMOUNT"\
+comma+"DOCNUM"
alineE1="!ENDTRNS"
alineE2="ENDTRNS"
fp.write_line(var->alineT1)
fp.write_line(var->alineS1)
fp.write_line(var->alineE1)
tbl.fetch_first()
tbl.change_begin()
tbl.epqb=.t.
tbl.change_end(.t.)
while .not. tbl.fetch_eof()
alineT2="TRNS"+comma+Right(tbl.invoice_number,6)+comma+"PAYMENT"+comma+\
dtoc(tbl.payment_date)+comma+"Undeposited Funds"+comma+alltrim(tbl.company)\
+comma+str(tbl.applied_to_invoice,15,2)+comma+alltrim(tbl.check_number)
alineS2="SPL"+comma+Right(tbl.invoice_number,6)+comma+"PAYMENT"+comma+\
dtoc(tbl.payment_date)+comma+"Accounts Receivable"+comma+alltrim(tbl.company)+comma+\
"-"+str(tbl.applied_to_invoice,15,2)+comma+alltrim(tbl.check_number)
fp.write_line(var->alineT2)
fp.write_line(var->alineS2)
fp.write_line(var->alineE2)
tbl.fetch_next()
end while
fp.flush()
fp.close()
qry.drop()
lastthing:
sys_shell(qb)
end
This is an example of how a txt file for QB may look saved as a .iif file with Sales Tax:
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT NAMEISTAXABLE ADDR1 ADDR2 ADDR3 ADDR4 DUEDATE TERMS OTHER1 !SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM PAYMETH TAXABLE VALADJ SERVICEDATE OTHER2 EXTRA !ENDTRNS TRNS 6 INVOICE 7/16/98 Accounts Receivable Invoice with Sales Tax 873.44 3 N Y Y 7/16/98 SPL 7 INVOICE 7/16/98 Construction:Materials -62.5 Cabinet Pulls N -25 2.5 Cabinets:Cabinet Pulls Y N 0/0/0 SPL 8 INVOICE 7/16/98 Construction:Materials -750 Cabinets N -5 150 Cabinets Y N 0/0/0 SPL 9 INVOICE 7/16/98 Sales Tax Payable State Board of Equalization -60.94 3 N 7.50% San Carlos N N AUTOSTAX ENDTRNS
This Example actually shows no sales tax, however you will notice that the line for sales tax is still present, just with no values.
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 DUEDATE TERMS PAID SHIPDATE !SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM PAYMETH TAXABLE REIMBEXP EXTRA !ENDTRNS TRNS 45 INVOICE 7/24/98 Accounts Receivable "Melton, Johnny:Dental Office" 55 2 N Y 7/24/98 N 7/24/98 SPL 46 INVOICE 7/24/98 Construction:Labor -30 N 30 Framing N N SPL 47 INVOICE 7/24/98 Construction:Labor -25 N 25 Wood Door Y N SPL 48 INVOICE 7/24/98 Sales Tax Payable State Board of Equalization 0 2 N 7.75% Sales Tax N N AUTOSTAX ENDTRNS
Sorry, that I didn't take the time to explain each line of code. Writing is not my gift and neither is writing code, but Alpha made it so easy even a novice like me can write an application for others to use!
3/11/01
Don't forget, we need your feedback to make this site better!