I need to import directly from a DBF Foxpro Database.

Which is the best to achieve this?

Any Suggestions?

Tks

David Schranz

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

The Sprezzatura Group

World Leaders in all things RevSoft

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.

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

[email protected]

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

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

View this thread on the Works forum...