Wednesday, January 5, 2011

How to get Python to talk to QuickBooks Pro 2008 using QBFC

You can import/export and change most QuickBooks data (including most transactions) using the qbXML and QBFC methods. Quickbooks has a built-in COM listener, and it's possible for other applications to login through this interface...even if QB is not running.

Prerequisites


  • Python COM: The pywin32 module enables python to talk COM. Look for the file matching your architecture AND python version. The downloaded .exe file should automatically install on your Windows system. You need pywin32 for both the QBFC method and the qbXML method.

  • QBFC10.dll: The QBFC10.dll file, and the installer for it, is not included with Quickbooks, so you need to get it from the Quickbooks SDK available at http://developer.intuit.com, which means you'll need to register at that site.
    This prerequisite only applies to QBFC. qbXML uses QBXMLRP2.dll, which is already included with Quickbooks.


Using qbXML



QBFC is just a thin object-oriented wrapper around qbXML, so the best way to understand QBFC is to look at some qbXML first.

#SAMPLE QBXML
<QBXML>                                   # XML container
   <QBXMLMsgsRq>                          # Container for multiple transaction types
      <InventoryAdjustmentQueryRs>        # List of transactions
         <InventoryAdjustmentRet>         # Container for each transaction
            <TxnID>12345</TxnID>          # Transaction detail
            <Memo>Something to say</Memo> # Transaction detail
         </InventoryAdjustmentRet>
      </InventoryAdjustmentQueryRs>
   </QBXMLMsgsRq> 
</QBXML>



As you see, getting transaction information in qbXML is like peeling an onion - layer after layer of containers. Here's how to do qbXML using Python 2.6:

#!usr/bin/python
import win32com.client
import xml.etree.ElementTree

# Connect to Quickbooks
sessionManager = win32com.client.Dispatch("QBXMLRP2.RequestProcessor")    
sessionManager.OpenConnection('', 'Test qbXML Request')
ticket = sessionManager.BeginSession("", 0)

# Send query and receive response
qbxml_query = """
<?qbxml version="6.0"?>
<QBXML>
   <QBXMLMsgsRq onError="stopOnError"> 
      <InventoryAdjustmentQueryRq metaData="MetaDataAndResponseData">
      </InventoryAdjustmentQueryRq>
   </QBXMLMsgsRq> 
</QBXML>
"""
response_string = sessionManager.ProcessRequest(ticket, qbxml_query)

# Disconnect from Quickbooks
sessionManager.EndSession(ticket)     # Close the company file
sessionManager.CloseConnection()      # Close the connection

# Parse the response into an Element Tree and peel away the layers of response
QBXML = xml.etree.ElementTree.fromstring(response_string)
QBXMLMsgsRs = QBXML.find('QBXMLMsgsRs')
InventoryAdjustmentQueryRs = QBXMLMsgsRs.getiterator("InventoryAdjustmentRet")
for InvAdjRet in InventoryAdjustmentQueryRs:
    txnid = InvAdjRet.find('TxnID').text
    memo = InvAdjRet.find('memo').text



See how each container needs to be opened to get to the bottommost data?

It's about the same in both qbXML and QBFC.


Using QBFC



Here's the same query in QBFC using Python 2.6:

#!usr/bin/python
import win32com.client
#No ElementTree needed, since no raw XML

# Open a QB Session
sessionManager = win32com.client.Dispatch("QBFC10.QBSessionManager")    
sessionManager.OpenConnection('', 'Test QBFC Request')
# No ticket needed in QBFC
sessionManager.BeginSession("", 0)

# Send query and receive response
requestMsgSet = sessionManager.CreateMsgSetRequest("US", 6, 0)
requestMsgSet.AppendInventoryAdjustmentQueryRq()
responseMsgSet = sessionManager.DoRequests(requestMsgSet)

# Disconnect from Quickbooks
sessionManager.EndSession()           # Close the company file (no ticket needed)
sessionManager.CloseConnection()      # Close the connection

# Peel away the layers of response
QBXML = responseMsgSet
QBXMLMsgsRq = QBXML.ResponseList
InventoryAdjustmentQueryRs = QBXMLMsgsRq.GetAt(0)
for x in range(0, len(InventoryAdjustmentQueryRs.Detail)):
    InventoryAdjustmentRet = QueryRs.Detail.GetAt(x)
    txnid = InventoryAdjustmentRet.TxnID.GetValue()
    memo = InventoryAdjustmentRet.Memo.GetValue()

The two small advantages of QBFC are:
  1. Each transaction is a single variable with all the data appended at attributes. That seems slightly easier to deal with than using ElementTree to convert each transaction into a dict. But not much.

  2. QBFC transactions seem to be about 20% faster than equivalent qbXML