DBF to SQL conversion

[TimStone]

Many years ago there were utilities that would take DBF files and convert them to SQL.

I would like to explore this option to convert my existing 120 DBF files ( one application ) into tables in a single SQL database ( Microsoft SQL ).

I posted this once before but we seemed to go off track.

If anyone knows of a good conversion tool that will do this, please let me know.

Tim

[ADutheil]

Tim have a try at this one:

http://www.tkinformidia.net/uploads/1/2/0/0/12005968/dbf2sql-3.0.zip

[Rick Lipkin]

Tim

I went through a major conversion of one of my apps from .dbf to Sql Server .. and I decided to incorporate the update into my distributed executable.

First thing I had to do was to determine the trigger that would occur or not occur each time the application was started .. meaning, find some Sql table that is central to your application .. like your Users table .. if the Sql users table has no records .. then that would trigger the Sql update routine .. ( this assumes that your sql database and tables have already been created by a DBA with the proper security and authorized user accounts )

Here is a sample of the conversion code .. Hope this gives you some ideas what you can do on your own.

Rick Lipkin

Code: Select all  Expand view

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

cSQL := "SELECT * FROM AGENCY where agency = '"+xAGENCY+"'"

TRY
   oRS:Open(cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )

CATCH oErr
   MsgInfo( "Error in Opening AGENCY table" )
   IF cAUTH = 'Y'
      oDLG:End()
   ENDIF
   RETURN(.F.)
END TRY

IF oRS:eof
   SAYING := "There are no AGENCY Records for "+xAGENCY+CHR(10)
   SAYING += "Would you like to IMPORT a Dataset ?"+CHR(10)
   IF MsgYesNo(saying )

      nNUM := 0

      IF cAUTH = 'Y'
         cSAY := "Creating Dataset AGENCY "+str(Nnum)
         oSay:ReFresh()
         SysReFresh()
      ENDIF

      IF .not. FILE( cDEFA+"\UTILITY.DBF" )
         SAYING := "The Import file "+ cDEFA+"\UTILITY.DBF"+CHR(10)
         SAYING += "Could not be found .. Aborting"+CHR(10)
         MsgAlert(SAYING)
         oRs:Close()
         IF cAUTH = 'Y'
            oDLG:End()
         ENDIF
         RETURN(.F.)
      ENDIF

      oRs:CLose()

      oRs := TOleAuto():New( "ADODB.Recordset" )
      oRs:CursorType     := 1        // opendkeyset
      oRs:CursorLocation := 3        // local cache
      oRs:LockType       := 3        // lockoportunistic

      cSQL := "SELECT * FROM AGENCY"
      TRY
         oRS:Open(cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
      CATCH oErr
         MsgInfo( "Error in Opening AGENCY table" )
         IF cAUTH = 'Y'
            oDLG:End()
         ENDIF
         RETURN(.F.)
      END TRY

      SELECT 1
      USE UTILITY via "DBFCDX" EXCL
      GO TOP

      DO WHILE .not. EOF()

         IF DELETED()
            SELECT UTILITY
            SKIP
            LOOP
         ENDIF

         cEID := _GenEID( oRS, 1 )

         oRs:AddNew()

         oRs:Fields( "ageneid"):Value   := cEID
         oRs:Fields( "agency"):Value    := upper(utility->agency)
         oRs:Fields( "owner" ):Value    := UPPER(utility->owner)
         oRs:Fields( "address" ):Value  := UPPER(utility->address)
         oRs:Fields( "city" ):Value     := UPPER(utility->city)
         oRs:Fields( "state" ):Value    := UPPER(utility->state)
         oRs:Fields( "zip" ):Value      := utility->zip
         oRs:Fields( "last_program"):Value := "100"
         oRs:Fields( "last_vend"):Value := utility->last_vend
         oRs:Fields( "last_po" ):Value  := utility->last_po
         oRs:Fields( "contact"):Value   := "    "
         oRs:Fields( "phone" ):Value    := utility->phone
         oRs:Fields( "finance" ):Value  := upper(utility->finance)
         oRs:Fields( "fin_addr" ):Value := upper(utility->fin_addr )
         oRs:Fields( "fin_city" ):Value := upper(utility->fin_city )
         oRs:Fields( "fin_state" ):Value  := upper(utility->fin_state)
         oRs:Fields( "fin_zip" ):Value  := utility->fin_zip
         oRs:Fields( "shoprate" ):Value := utility->shoprate
         oRs:Fields( "logtype" ):Value  := utility->logtype
         oRs:Fields( "lextract" ):Value := if(empty(utility->lextract), ctod(""), utility->lextract)
         oRs:Fields( "comm1" ):Value    := utility->comm1
         oRs:Fields( "comm2" ):Value    := utility->comm2
         oRs:Fields( "comm3" ):Value    := utility->comm3

         oRs:Update()

         nNUM++
         IF cAUTH = 'Y'
            cSAY := "Creating Dataset AGENCY "+str(Nnum)
            oSay:ReFresh()
            SysReFresh()
         ENDIF

         SELECT UTILITY
         EXIT    // only allow one agency record to be created

      ENDDO

      CLOSE UTILITY

   ENDIF
ENDIF

 

[TimStone]

Thank you both. I'm exploring options here.

Tim