As I transition to 64 bit solutions and a new iMac coming soon, one of my big problems has been getting Pan financial data into QB. I have finally cracked the code on QB .IIF format and can get Pan X to create a file that can be directly imported into QB Mac. If this is of any interest to anyone else, I would be happy to share my solution.
Scott
Always interested in how data can be imported into a financial program’s files.
QB’s .IIF format appears to be quite opaque and difficult to figure out, so I used a translator module in Excel to go from Pan to Excel to IIF to QB for the past 10 years or so. Desperation with all that being done on 32 bit software pushed me to figure out a better solution using Pan X directly.
By examining sample files I found on the internet that have literally 100 or more elements, I eventually was able to pare down the inputs to the most basic that fit my QB file. If you use Categories, Items, Invoice due dates and such, those elements have to be added to the basic formulas presented here. It’s basically an array with each line representing one transaction or several lines together representing the transaction plus its splits.
The overview: Select one or more financial transactions in my Pan DB. Build a text array with the header, transaction details, and footer. Export that and import into a dedicated, separate Pan DB with fields matching the header elements. Run one command to export the .IIF file in ASCII text. Voila! Ready for import.
Of course the transactions have to be in proper double entry format with some accounts getting debited and others credited using + and - amounts.
The output procedure builds a local variable lv1. Here is the header:
local lv1
lv1="!TRNS"+¬+"DATE"+¬+"ACCNT"+¬+"NAME"+¬+"AMOUNT"+¬+"MEMO"+¬+"ACCTTYPE"
+lf()+"!SPL" +¬+"DATE"+¬+"ACCNT"+¬+"NAME"+¬+"AMOUNT"+¬+"MEMO"+¬+"ACCTTYPE"
+lf()+"!ENDTRNS"
Continuing to build the body of the transaction:
lv1=lv1+lf()+"TRNS"+¬+Datepattern(«Entry Date», "mm/dd/yy")+¬+"Business Checking"+¬+"Richard" +¬+pattern(RHtoSC, "#.##") +¬+"Employee Exp. "+DatePattern(Today(), "Month")+¬+"BANK"
lv1=lv1+lf()+"SPL"+¬+ Datepattern(«Entry Date», "mm/dd/yy")+¬+"Payroll Expenses:Wages"+¬+¬+"-"+pattern(CalcGrossPayRH+CalcEmplContribRH, "#.##")+¬+¬+"EXP"
If («Extras Amt 1»+«Extras Amt 2»)/2≠0
lv1=lv1+lf()+"SPL"+¬+ Datepattern(«Entry Date», "mm/dd/yy")+¬+"Office Expenses:Office Supplies"+¬+¬+"-"+Pattern((«Extras Amt 1»+«Extras Amt 2»)/2, "#.##")+¬+¬+"EXP"
Endif
If «Extras Amt 5»/2≠0
lv1=lv1+lf()+"SPL"+¬+ Datepattern(«Entry Date», "mm/dd/yy")+¬+"Office Expenses:Office Cleaning"+¬+¬+str(«Extras Amt 5»/2)+¬+¬+"EXP"
Endif
If RHSimpleMatch≠0
lv1=lv1+lf()+"SPL"+¬+ Datepattern(«Entry Date», "mm/dd/yy")+¬+"Payroll Liabilities:SIMPLE Contributions Payable"+¬+¬+str(RHSimpleMatch)+¬+¬+"OCLIAB"
Endif
If «RH Retire»≠0
lv1=lv1+lf()+"SPL"+¬+ Datepattern(«Entry Date», "mm/dd/yy")+¬+"Payroll Liabilities:SIMPLE Contributions Payable"+¬+¬+str(«RH Retire»)+¬+¬+"OCLIAB"
Endif
If RHSimpleMatch≠0
lv1=lv1+lf()+"SPL"+¬+ Datepattern(«Entry Date», "mm/dd/yy")+¬+"Payroll Expenses:Benefits:Retirement Plan - SIMPLE" +¬+¬+"-"+str(RHSimpleMatch)+¬+¬+"EXP"
Endif
lv1=lv1+lf()+"ENDTRNS"
opendatabase "QB Export"
importtext lv1,"ExistingData","Replace"
Call ".Export Data"
Save
Here is “.Export Data” from the QB Export DB - whose only function is this translation:
export "~/Desktop/ Import QB.IIF", exportline(), lf(), "ASCII"