|
|
|
Technical Support
|
 |
The QQT file format is used for saving data that was extracted using
OfficeQ6 so it can be processed later and/or at a different location. It
is a fast and simple format that can be read by virtually any programming
language - yet retains ALL record data available from the OfficeQ6 API.
Because of the long time it takes for OfficeQ6 to extract data from QuickBooks
- it is also very useful for testing. A set of data can be extracted one
time into a QQT file, and read from the file several times without needing
to run QuickBooks or OfficeQ6 again.
The format is also ideal for applications where the data is to be read at a
customer site, transferred, and processed elsewhere - such as over the internet. The QQT
file can be created by the customer using a tool with a simple user
interface - then uploaded using ftp or http to
a web site, then processed. This allows sensitive data (such as payroll)
to be remotely transferred.
Because a .QQT file contains all the data from a QuickBooks file at a
particular point in time - multiple .QQT files can be compared to identify
all changes to a QuickBooks file over a period of time.
Almost any programming environment can be used to read a QQT file.
It needs to be able to read binary ASCII text, read 32 bit binary numbers,
and reposition the file pointer to any byte offset.
There are 4 sections to the QQT file format - FILETYPE, STATS, RECSTART, AND RECDATA. Each will be described below.
Purpose is to identify that this is a QQT file and specify the total count of blocks that make up the file header (everything before RECSTART). Breaks down as follows:
offs len prompt
0x0000 8 none, file version (always= QQTD0100)
0x0008 1 none, single ascii digit - count of 4K blocks taken up by header
(value: always= 4)
0x0009 5 blanks
0x000B 2 crlf
The purpose of this section is to provide information about the file
that might be useful if you have a collection of files for testing - such
as how many of each record type or transaction type it contains. For example,
if you are looking for a file with a lot of CustInvc and CustPymt transactions,
and you have a set of QQT files handy - you can look here in each file to
quickly find files that match.
This section is arranged like a text file. It has several variable length
lines that are each terminated with 0x0D 0x0A (crlf). Brackets surround
captions so code can be used to find particular lines if desired.
Following the [end_trantype_counts] marker - the file is filled with all
zeros until offset 16384 (0x4000) - which begins the next section. Note
that until the [end_trantype_counts] marker - this file is all text and
can be viewed with most text editors. After the marker - the file becomes
all binary.
The following is the list of entries (with sample data for each) from a
real world (although very large) file. These were copied directly from a
QQT file - so formatting, spacings, etc. are correct. Note that record count
lines only exist if there are records of that type - whereas tran type count
lines exist even if the count is 0.
QQTD01004 [appversion:] 20.0.0.0 [qbproduct:] QuickBooks: Premier Accountant Edition 2010 [machine:] POTLATCH [file:] L:\qbdata\2010\1each_prm2010.qbw [file_unc:] \\POTLATCH\DriveL\qbdata\2010\1each_prm2010.qbw [extract_start:] 09/28/2009 06:27:30 [extract_end:] 09/28/2009 12:52:14 [first_tran:] 05/31/1989 [last_tran:] 04/10/2009 [start_record_counts:] Account 198 TranDoc 77261 TranLine 331704 InvtItem 32 PayrollItem 12 Employee 7 CustJob 1268 CustMessage 1 PayMeth 8 Terms 7 Vendor 151 VendType 5 OtherName 1 ToDoNote 11 Company 1 Settings 4 TranLink 91463 SalesRep 2 SalesTax 2 BillRate 2 Currency 164 UnitOfMeas 7 [end_record_counts:] [start_trantype_counts:] Customer CustRfnd 0 CustChrg 0 CustCred 26 Estimate 0 CustInvc 49806 CustPymt 22992 SalOrder 0 CashSale 0 TaxPymt 19 Vendor VendBill 1689 VendPymt 357 VendCard 0 CardChrg 0 CardRfnd 0 VendCred 15 Inventory Assembly 0 InvAdjst 46 ItemRcpt 0 PurchOrd 0 Other Journal 372 Check 414 Deposit 1449 LiaAdjst 7 Paycheck 0 LiabPymt 0 Transfer 69 YtdAdjst 0 [end_trantype_counts:]
This section provides counts of each record type (in binary form) as well as the offset in the file where records of that type start. It is an array of structs - each 32 bytes long with the following fields (pascal notation):
TRecProps = packed record // each entry 32 bytes long nCount :integer; nFiller :integer; // 4 0's (could be for 64 bit offset in future) nOffset :integer; nFiller2 :array [1..20] of char; // could be flags in future end;
Only fields nCount and nOffset are used. nCount is the count of that
record type in the file. nOffset if the offset in the file where the records
of that record type begin.
The record type numeric identifier (shown below) is as the index to this
array. In other words, if you want to know how many InvtItems are in the
file, start with offset 0x4000 - them multiply QB_RS_InvtItem * 32 (7 x
32 = 224 = 0xE0). The record count would be the 32 bit signed integer quantity
at file offset 0x40E0 (16608). The 32 bits following that is 0 filler -
then the 32 bits following that is the zero based offset in the file where
the InvtItem records begin.
hex dec hex mdb record name
offset
QB_RS_Account = 0x00 {0}; 4000 // aAccount
QB_RS_TranDoc = 0x01 {1}; 4020 // aTranDoc
QB_RS_TranLine = 0x02 {2}; 4040 // aTranLine
QB_RS_Budget = 0x05 {5}; 40A0 // aBudget
QB_RS_Class = 0x06 {6}; 40C0 // aClass
QB_RS_InvtItem = 0x07 {7}; 40E0 // vInvtItem
QB_RS_PayrollItem = 0x08 {8}; 4100 // pPayrollItem
QB_RS_Employee = 0x09 {9}; 4120 // pEmployee
QB_RS_TimeLine = 0x0D {13}; 41A0 // pTimeLine
QB_RS_CustJob = 0x0E {14}; 41C0 // cCustJob
QB_RS_CustType = 0x0F {15}; 41E0 // cCustType
QB_RS_JobType = 0x10 {16}; 4200 // cJobType
QB_RS_CustMessage = 0x11 {17}; 4220 // cCustMessage
QB_RS_ShipVia = 0x12 {18}; 4240 // xShipVia
QB_RS_PayMeth = 0x13 {19}; 4260 // xPayMeth
QB_RS_Terms = 0x14 {20}; 4280 // cTerms
QB_RS_Vendor = 0x15 {21}; 42A0 // vVendor
QB_RS_VendType = 0x16 {22}; 42C0 // vVendType
QB_RS_OtherName = 0x17 {23}; 42E0 // xOtherName
QB_RS_ToDoNote = 0x19 {25}; 4320 // xToDoNote
QB_RS_Company = 0x1C {28}; 4380 // xCompany
QB_RS_Settings = 0x1D {29}; 43A0 // xSettings
QB_RS_TranLink = 0x20 {32}; 4400 // aTranLink
QB_RS_SalesRep = 0x23 {35}; 4460 // pSalesRep
QB_RS_PriceLevel = 0x24 {36}; 4480 // vPriceLevel
QB_RS_SalesTax = 0x25 {37}; 44A0 // cSalesTax
QB_RS_BillRate = 0x28 {40}; 4500 // vBillRate
QB_RS_Currency = 0x29 {41}; 4520 // aCurrency
QB_RS_UnitOfMeas = 0x2A {42}; 4540 // vUnitOfMeas
... then all 00 bytes till 0x5000
This is a series of variable length records. All records of a particular record type are together - in the same order as the record type constants above. (All Account records, them TranDoc records, etc.) The entry for a record type in the RECSTART section is the file offset of the first record of that type. In this section, there are no separators between records of different types.
0x5000: start of record data
for each record
32 bit - 0 based index of record (starts at 0 - increments 1 for each)
32 bit byte count
bytes that make up the record
To read a series of records of a designated type, get the record count and offset from RECSTART. Starting at that offset, read the 4 byte index, then the 4 byte character count. Then read the record itself (a string) - using that 4 byte count. Make sure to honor the record count - if you overflow, you'll be reading records of the next type. (This could also be detected by the record index resetting to zero).
The string that makes up a record extracted above is identical to what is returned by ...RecordN and ...RecordK functions in the API.� See record formats.