{{tag>category:"OpenInsight 32-Bit" author:"David E Schranz" author:"[url=http://www.sprezzatura.com]The Sprezzatura Group[/url]" author:"John Poremba" author:"Richard Bright"}} [[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]] ==== Importing directly from DBF Files (OpenInsight 32-Bit) ==== === At 08 MAR 2003 04:16:45PM David E Schranz wrote: === I need to import directly from a DBF Foxpro Database. Which is the best to achieve this? Any Suggestions? Tks David Schranz ---- === At 09 MAR 2003 05:37AM [url=http://www.sprezzatura.com]The Sprezzatura Group[/url] wrote: === David, The header of the file contains the field name & width definitions. From there you should be able to simply parse the file using OSBREAD, and adjust for date fields. Optionally, use AREV if it's a one-off exercise. Steve [url=http://www.sprezzatura.com]The Sprezzatura Group[/url] [i]World Leaders in all things RevSoft[/i] [img]http://www.sprezzatura.com/zz.gif[/img] ---- === At 11 MAR 2003 01:33PM John Poremba wrote: === I do just this as an automatic daily scheduled event. It does require establishing an ODBC connection. If you would like the code I will post it. It updates OI tables from a Foxpro DBC via the ODBC. Please advise. ---- === At 11 MAR 2003 03:28PM Richard Bright wrote: === John, Interested to see code. Ira might even make use of it in his documentation of OI 'how to do...' Richard Bright BrightIdeas New Zealand r.bright@ark.co.nz ---- === At 12 MAR 2003 11:09AM John Poremba wrote: === Ok, here's the code. Note that the FoxPro and OI tables have the same file names. This is not required, in fact this sales order data, could have been handled as one OI file. Note that sorels and soitem are the sales order line item data which could have been handled as multi value fields. If you need info on how I fire off auto processing via the MS schdualer thats another story. This is just the data import code. Note: again you need to establish a ODBC conection. A datasets are not needed. FUNCTION Ezdoedi_M2mFileProcess_SO (ezdoedi) * for use in an event script, QryMethod example from OI help * additionl help from Cameron Purdy(RevelationSoftware) * Modified and customized 6/99 by John Poremba * revision 4/01 by JP, sales orders only. * get data directly from m2m 5/15/01 Declare Subroutine Utility declare subroutine MSG declare Function Get.RecCount declare Function DSGetProperty declare Function DSInstance $insert XO_Equates $insert MSG_Equates $insert DS_Equates * MSG.NOTE: RESPONSE=';MESSAGE=' MESSAGE=IMPORT SO DATA FOR EDI PROCESSING|Click to CONTINUE, to Exit." MESSAGE=BNY" RESPONSE=MSG(@window, MESSAGE) IF RESPONSE=1 THEN GOTO PROCESS MSG(@WINDOW,"EXITING UPON YOUR REQUEST.");GOTO DONE * PROCESS: Results=";CNTR=0;tblna=';row=';Rec=' open "SOMAST" to fData7 then Cnt7=Get.RecCount(fData7,Flag,0) else gosub OpenError; return 0 open "SOITEM" to fData8 then Cnt8=Get.RecCount(fData8,Flag,0) else gosub OpenError; return 0 open "SORELS" to fData9 then Cnt9=Get.RecCount(fData9,Flag,0) else gosub OpenError; return 0 Utility("CURSOR", "H") * create connection hXO=XOInstance("M2MDATA","ODBC","","") hQry=0 if hXO then * create the query handle for the connection handle hQry=QryInstance(hXO) if hQry then * execute a script Utility("CURSOR", "H") Flag=QryMethod(hQry, QRY_EXECUTE$, "select * from SoMast where fstatus=Open' and not deleted()") Tblna=SOMAST"; cRows=cnt7; gosub ProcessInfo if Flag then * retrieve a row at a time loop Flag=QryMethod(hQry, QRY_GETROW$, Row) while Flag Row=iconv(Row,"MD3");Row=iconv(Row,"MD0");Row=iconv(Row,"MD5") Row=iconv(Row,"MD5");Row=iconv(Row,"MD3") Row=iconv(Row,"MD3") write Row to fData7,Row else gosub WriteError Msg(@window,MsgUp,CNTR,MSGINSTUPDATE$) CNTR=CNTR +1 repeat Results 1,1=" * cancel script Flag=QryMethod(hQry, QRY_CANCEL$) end else gosub error end Msg(@window,MsgUp);gosub Info; ClearSelect * execute a script Utility("CURSOR", "H") Flag=QryMethod(hQry, QRY_EXECUTE$, "select soitem.* from Soitem inner join somast on soitem.fsono == somast.fsono where somast.fstatus=Open' and not deleted() order by soitem.fsono, soitem.finumber") Tblna=SOITEM"; cRows=cnt8; gosub ProcessInfo if Flag then * retrieve a row at a time loop Flag=QryMethod(hQry, QRY_GETROW$, Row) while Flag Row=iconv(Row,"MD5");Row=iconv(Row,"MD5") SIKey=Row:Row write Row to fData8,SIKey else gosub WriteError Msg(@window,MsgUp,CNTR,MSGINSTUPDATE$) CNTR=CNTR +1 repeat Results 1,1=" * cancel script Flag=QryMethod(hQry, QRY_CANCEL$) end else gosub error end Msg(@window,MsgUp);gosub Info; ClearSelect * execute a script Utility("CURSOR", "H") Flag=QryMethod(hQry, QRY_EXECUTE$, "select sorels.* from SoRels inner join somast on sorels.fsono == somast.fsono where somast.fstatus=Open' and not deleted() order by sorels.fsono, sorels.finumber") Tblna=SORELS"; cRows=cnt9; gosub ProcessInfo if Flag then * retrieve a row at a time loop Flag=QryMethod(hQry, QRY_GETROW$, Row) while Flag Row=iconv(Row,"MD5"); Row=iconv(Row,"MD5") Row=iconv(Row,"MD5"); Row=iconv(Row,"MD5") Row=iconv(Row,"MD5"); Row=iconv(Row,"MD5") Row=iconv(Row,"MD5"); Row=iconv(Row,"MD5") SRKey=Row:Row write Row to fData9, SRKey else status=Set_FSError(); gosub WriteError Msg(@window,MsgUp,CNTR,MSGINSTUPDATE$) CNTR=CNTR +1 repeat Results 1,1=" * cancel script Flag=QryMethod(hQry, QRY_CANCEL$) end else gosub error end Msg(@window,MsgUp);gosub Info; ClearSelect * close the query handle Flag=QryMethod(hQry, QRY_DESTROY$) end else gosub error end * close the connection Flag=XOMethod(hXO, XO_DESTROY$) end else gosub error end * DONE: Utility("CURSOR", "A") MSG(@WINDOW,"SALES ORDER IMPORT COMPLETE!") return 0 * Error handling Error: if hQry then Flag=QryMethod(hQry, QRY_GETERROR$, "", "", "", "", Text) end else Flag=XOMethod(hXO, QRY_GETERROR$, "", "", "", "", Text) end convert @vm to @tm in Text Def=" Def =Text Def=ExecuteScript Error" Def =!" Msg(@window, Def) return OpenError: * failed to open LH_TABLE$ EQUATE LH_TABLE$ TO Tblna Def=" Def =Failed to open ": LH_TABLE$ Def=Open Error" Def =!" Msg(@window, Def) return WriteError: * failed to write "Row" to "Row" MSG("WRITE ERROR ":SRKEY:"|":status:"|":@file_error,'T.5','','') return InFo: * keep user informed. Message=' MESSAGE=CNTR:" Records from ":TBLNA:" Imported." MESSAGE=T2' MESSAGE=W' MESSAGE=!' Msg(@window,Message) cntr=0 return ProcessInFo: * keep user informed on progress. Message=' MESSAGE=Importing Records from ":TBLNA:"" MESSAGE=G' MESSAGE=W' MESSAGE=*' MESSAGE=cRows MsgUp=Msg(@window, MESSAGE) return ---- === At 17 MAR 2003 03:11PM David E Schranz wrote: === Guys! Thanks for your help, I think the best way would be to directly parse the DBF file as suggested by Sprezzatura. Thanks again. I was hoping for some sort of environmental bond similar to AREV. Rgds David Schranz [[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=BB08AE434F16701085256CE30074E40C|View this thread on the Works forum...]]