Knowledgebase
[QODBC-Desktop] I keep getting an error '[QODBC] Assignment Error' when I try to import entire tables into Excel?
Posted by Rajendra Dewani (QODBC Support) on 13 December 2013 07:23 AM

Check if the table you are importing has any long fields and exclude them from the import. Some tables in QuickBooks have fields as long as 4095 characters and cannot fit in a single spreadsheet cell.


An excellent example of this is the InvoiceLine table. If you run sp_columns InvoiceLine using QODBC Test Tool, you will see that the Memo and InvoiceLineGroupDesc columns can be up to 4095 characters long, so they can't be imported into Excel.




This means that a:  "select * from InvoiceLine."

It will not import. You must instead select all the columns of interest that you wish to use by doing something like the following:-

SELECT InvoiceLine.TxnID, InvoiceLine.TimeCreated, InvoiceLine.TimeModified, InvoiceLine.EditSequence, InvoiceLine.TxnNumber, InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName, InvoiceLine.ClassRefListID, InvoiceLine.ClassRefFullName, InvoiceLine.ARAccountRefListID, InvoiceLine.ARAccountRefFullName, InvoiceLine.TemplateRefListID, InvoiceLine.TemplateRefFullName, InvoiceLine.TxnDate, InvoiceLine.TxnDateMacro, InvoiceLine.RefNumber, InvoiceLine.BillAddressAddr1, InvoiceLine.BillAddressAddr2, InvoiceLine.BillAddressAddr3, InvoiceLine.BillAddressAddr4, InvoiceLine.BillAddressCity, InvoiceLine.BillAddressState, InvoiceLine.BillAddressPostalCode, InvoiceLine.BillAddressCountry, InvoiceLine.ShipAddressAddr1, InvoiceLine.ShipAddressAddr2, InvoiceLine.ShipAddressAddr3, InvoiceLine.ShipAddressAddr4, InvoiceLine.ShipAddressCity, InvoiceLine.ShipAddressState, InvoiceLine.ShipAddressPostalCode, InvoiceLine.ShipAddressCountry, InvoiceLine.IsPending, InvoiceLine.IsFinanceCharge, InvoiceLine.PONumber, InvoiceLine.TermsRefListID, InvoiceLine.TermsRefFullName, InvoiceLine.DueDate, InvoiceLine.SalesRepRefListID, InvoiceLine.SalesRepRefFullName, InvoiceLine.FOB, InvoiceLine.ShipDate, InvoiceLine.ShipMethodRefListID, InvoiceLine.ShipMethodRefFullName, InvoiceLine.Subtotal, InvoiceLine.ItemSalesTaxRefListID, InvoiceLine.ItemSalesTaxRefFullName, InvoiceLine.SalesTaxPercentage, InvoiceLine.SalesTaxTotal, InvoiceLine.AppliedAmount, InvoiceLine.BalanceRemaining, InvoiceLine.IsPaid, InvoiceLine.CustomerMsgRefListID, InvoiceLine.CustomerMsgRefFullName, InvoiceLine.IsToBePrinted, InvoiceLine.CustomerSalesTaxCodeRefListID, InvoiceLine.CustomerSalesTaxCodeRefFullName, InvoiceLine.SuggestedDiscountAmount, InvoiceLine.SuggestedDiscountDate, InvoiceLine.InvoiceLineType, InvoiceLine.InvoiceLineSeqNo, InvoiceLine.InvoiceLineGroupTxnLineID, InvoiceLine.InvoiceLineGroupItemGroupRefListID, InvoiceLine.InvoiceLineGroupItemGroupRefFullName, InvoiceLine.InvoiceLineGroupQuantity, InvoiceLine.InvoiceLineGroupIsPrintItemsInGroup, InvoiceLine.InvoiceLineGroupTotalAmount, InvoiceLine.InvoiceLineGroupSeqNo, InvoiceLine.InvoiceLineTxnLineID, InvoiceLine.InvoiceLineItemRefListID, InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineRatePercent, InvoiceLine.InvoiceLinePriceLevelRefListID, InvoiceLine.InvoiceLinePriceLevelRefFullName, InvoiceLine.InvoiceLineClassRefListID, InvoiceLine.InvoiceLineClassRefFullName, InvoiceLine.InvoiceLineAmount, InvoiceLine.InvoiceLineServiceDate, InvoiceLine.InvoiceLineSalesTaxCodeRefListID, InvoiceLine.InvoiceLineSalesTaxCodeRefFullName, InvoiceLine.InvoiceLineOverrideItemAccountRefListID, InvoiceLine.InvoiceLineOverrideItemAccountRefFullName, InvoiceLine.FQSaveToCache, InvoiceLine.FQPrimaryKey, InvoiceLine.CustomFieldInvoiceLineOther1, InvoiceLine.CustomFieldInvoiceLineOther2, InvoiceLine.CustomFieldInvoiceLineColor, InvoiceLine.CustomFieldInvoiceLineMaterial, InvoiceLine.CustomFieldInvoiceLineGroupOther1, InvoiceLine.CustomFieldInvoiceLineGroupOther2, InvoiceLine.CustomFieldInvoiceLineGroupColor, InvoiceLine.CustomFieldInvoiceLineGroupMaterial, InvoiceLine.CustomFieldInvoiceLineGroupLineOther1, InvoiceLine.CustomFieldInvoiceLineGroupLineOther2, InvoiceLine.CustomFieldInvoiceLineGroupLineColor, InvoiceLine.CustomFieldInvoiceLineGroupLineMaterial, InvoiceLine.CustomFieldOther, InvoiceLine.CustomFieldBDay, InvoiceLine.CustomFieldContract, InvoiceLine.CustomFieldSpousesName
FROM InvoiceLine InvoiceLine
WHERE (InvoiceLine.TxnDate>{d '2024-01-01'})

Note: CustomField names will vary depending on the names you give them in QuickBooks.


You can also control the length of the column returned by QODBC using the LEFT function, even in stored procedure reports.

For example:

An examination of the report InventoryValuationSummary shows that the RowData and RowDataType columns can return values as long as 4096 characters:


Using the LEFT function, I can extract the first 30 characters of the RowData and RowDataType columns only:

sp_report InventoryValuationSummary show ItemDesc, QuantityOnHand, RowType,
{fn LEFT("RowData", 30)} AS "Row Data", {fn LEFT("RowDataType", 30)} AS "Row Data Type"
parameters DateFrom = {d '2024-11-19'}, DateTo = {d '2024-11-19'},
ItemFilterType = 'InventoryAndAssembly'

 

Keywords: assignment error

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).