Convert DBF 2 SQL

[TimStone]

We have had several threads about converting DBF files to SQL but they all seem to end unresolved. Years ago I remember a utility that did it with no problem, but can't seem to find one now that I'm sure is safe. ( Sometimes the downloads bring viruses ).

Some resources say to import the data in to Access and then import it into a SQL database. When trying that with the latest version of Access it tells me the .dbf file is not in the expected format. It works just fine.

I noticed a reference to the DBF2SQL.prg available in FWH, but some comments stated it would not work with FPT files.

Has anyone found a reasonable way to do this ? Ultimately all of my clients, with about 115 DBF files each, would need to be converted. Many of those files do have memo fields.

I likely would want to use MSSQL Express ( since I compile with Microsoft Visual Studio ) though I am also studying MySql.

[nageswaragunupudi]

FWH has this built-in functionality for many years (from June 2013)

Code: Select all  Expand view  RUN

oCn := FW_OpenAdoConnection( { "MSSQL", "SQLEXPRESS", [cDataBase], "SA", cPassWord }, .t. )
if oCn == nil
   ? "Connect Fail"
else
   FW_AdoImportFromDBF( oCn, "c:\fwh\samples\customer.dbf"
   oRs := FW_OpenRecordSet( oCn, "customers" )
   XBROWSER oRs FASTEDIT
   oRs:Close()
   oCn:Close()
endif
 

The same syntax applies Access, MSSql server, Oracle, MySql or any other server using ADO.

[nageswaragunupudi]

For MySQL/MariaDB using FWH buit-in functions:

Code: Select all  Expand view  RUN

oCn := maria_Connect( { server, database, user, password } )
oCn:ImportFromDBF( "c:\fwh\samples\customer.dbf" )
//
oRs := oCn:RowSet( "customer" )
XBROWSER oRs FASTEDIT
oRs:Close()
oCn:Close()
 

[Rick Lipkin]

Tim

If you are looking to migrate your application to MSSql .. I would like to offer my help .. As you know, I have been using ADO for many years and my favorite RDMS is Sql Server for Enterprise applications and MS Access for local portable applications.

There are several ways to code your application using the ADO rdd which uses much of your .dbf code or dive straight in and use the ADO class and methods ( my choice ).

Microsoft Ado info
[url]
https://docs.microsoft.com/en-us/sql/ad ... erver-2017
[/url]

FiveWin Ado coding examples
[url]
https://wiki.fivetechsoft.com/doku.php? ... ted_stuffs
[/url]

Rao ( see above post ) has done some great work in creating many of the ADO wrappers you can use to simplify your connections and recordsets .. look at your FiveWin folder \Samples\Source\Function\AdoFuncs.prg

Like you .. I have a commercial FleetManagemt application targeted at State and Federal Agencies .. which does have a major core garage shop module . I have a trial version that uses the portable ms Access database .. you are welcome to visit my website and download ..

[url]
http://www.sabms.com/fleetpro360/
[/url]

Let me know if I can help you ..

Rick Lipkin

[TimStone]

Rich,

I have looked at your information ( actually have it all in OneNote pages ). I figured I would first try to import the DBF files into Access, and since I have the latest version, I figured that would be simple. Sadly, it keeps giving me the error that the DBF is not in the proper format. Hmmmm .... not sure why that occurs.

[Rick Lipkin]

Tim

You can convert your dbf data the old fashioned way .. with code ..

It goes something like this .. look in your samples folder for adorick.prg

Create your Access table as 2003 .mdb .. NOT .accdb .. you will need the ACE client of you use .accdb .. .mdb uses the built in ole MS jet provider ..

Code: Select all  Expand view  RUN

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Rick.mdb"
cRDD      := xPROVIDER+" -- "+xSOURCE

 // global connection string
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE

If .not. File( cDefa+"\Rick.mdb" )

   Ferase( cDefa+"\Rick.mdb" )

   // create the adox object
   Try
      catNewDB := CreateObject("ADOX.Catalog")
   Catch
      MsgInfo( "Could not Create ADOX object")
      Return(.f.)
   End try

   // create the table Rick.mdb
   Try
     catNewDB:Create('Provider='+xProvider+';Data Source='+xSource+';Jet OLEDB:Engine Type=5' )
   Catch
     MsgInfo( "Could not create the table "+xSource )
     Return(.f.)
   End Try

   Try
     oCn  := CREATEOBJECT( "ADODB.Connection" )
   Catch
     MsgInfo( "Could not create the ADO object for connection")
   End Try

   TRY
     oCn:Open( xCONNECT )
   CATCH oErr
     MsgInfo( "Could not open a Connection to Database "+xSource )
     RETURN(.F.)
   END TRY


   cSQL := "CREATE TABLE CUSTOMER"
   cSQL += "( "
   cSQL += "[CUSTOMEREID] char(18) NOT NULL, "
   cSQL += "[LAST NAME] char(30) NULL, "
   cSQL += "[FIRST NAME] char(30) NULL, "
   cSQL += "[MID INIT] char(30) NULL, "
   cSQL += "[ADDRESS1] char(30) NULL, "
   cSQL += "[CITY] char(30) NULL, "
   cSQL += "[STATE] char(30) NULL, "
   cSQL += "CONSTRAINT PK_USERINFO PRIMARY KEY ( CUSTOMEREID )"
   cSQL += " )"

   Try
      oCn:Execute( cSQL )
   Catch
      MsgInfo( "Table CUSTOMER Failed" )
      Return(.f.)
   End try

   oCn:Close()
   oCn := nil

Endif

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

// start .dbf conversion

cSQL := "SELECT * FROM CUSTOMER"
TRY
   oRsCust:Open( cSQL, xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening CUSTOMER table here" )
   RETURN(.F.)
END TRY

Select 1
Use Customer via "DBFCDX" EXCL

Go top

DO while .not. eof()
     
     oRsCust:AddNew()
     oRsCust:Fields("CustomerEid"):Value  := "011111111111111111"  // generate any random number for your primary key
     oRsCust:Fields("Last Name"):Value     := a->LastName
     oRsCust:Fields("First Name"):Value     := a->FirstName
     oRsCust:Fields("Mid Init"):Value          := a->MidInit
     oRsCust:Fields("Address1"):Value        := a->address1
     oRsCust:Fields("City"):Value                := a->city
     oRsCust:Fields("State"):Value              := a->state
     oRsCust:Update()
   
     Select Customer
     Skip

Enddo

CLose Databases
oRsCust:Close()

Return(nil)
 

Kinda gives you a flavor on how to use the Ado methods .. again, Rao has simplified a lot of this .. but you still need to understand the methods for cursor movement .. addNew, find, filter, update,movenext, movetop, eof, bof .. etc .. notice there is no need for record locking .. the ole provider handles all of that.

Rick Lipkin

[TimStone]

Rick,

We come back to the same issue ... the Memo fields. Using Access, I can import from a DBF file UNLESS it has a memo field. Then it tells me it's not the expected structure.

Your example also does not create an SQL database with a memo field.

Tim

[nageswaragunupudi]

FW_AdoImportFromDBF() takes care of everything.

[Rick Lipkin]

Tim

Add this line in the Create table command to create a memo ( data type ole object ) that will accept binary files .. like pictures etc ..

Code: Select all  Expand view  RUN

cSql += "[MEMO] LONGBINARY NULL,"
 

Code: Select all  Expand view  RUN

cSQL := "CREATE TABLE CUSTOMER"
cSQL += "( "
cSQL += "[CUSTOMEREID] char(18) NOT NULL, "
cSQL += "[LAST NAME] char(30) NULL, "
cSQL += "[FIRST NAME] char(30) NULL, "
cSQL += "[MID INIT] char(30) NULL, "
cSQL += "[ADDRESS1] char(30) NULL, "
cSQL += "[CITY] char(30) NULL, "
cSQL += "[STATE] char(30) NULL, "
cSql += "[MEMO] LONGBINARY NULL,"
cSQL += "CONSTRAINT PK_USERINFO PRIMARY KEY ( CUSTOMEREID )"
cSQL += " )"

 

Rick Lipkin

[nageswaragunupudi]

FWH Ado functions take care of all the issues including memo fields.

Structure of \fwh\samples\wwonders.dbf:

Code: Select all  Expand view  RUN

"NAME",  "C", 40, 0
"IMAGE", "M", 10, 0
"NOTES", "M", 10, 0
 

The DBF contains two memo fields. Text is stored in the memo field "NOTES". The image itself, which is binary data, is stored in the memo field "IMAGE".

FWH takes care of the memo fields including whether the memo field is text or binary.

Sample:

Code: Select all  Expand view  RUN

#include "fivewin.ch"

REQUEST DBFCDX

function Main()

   local oCn, oRs

   RDDSETDEFAULT( "DBFCDX" )
   oCn   := FW_MSSQLDB()
   FW_AdoImportFromDBF( oCn, "c:\fwh\samples\wwonders.dbf" )
   oRs   := FW_OpenRecordSet( oCn, "wwonders" )
   XBROWSER oRs TITLE "MSSQL WWONDERS"
   oRs:Close()
   oCn:Close()

return nil
 

The program code and its functionality is the same whether the connection is to MSAccess, MS Sql Server, MySql, Oracle.



Exactly the same code works with MSACCESS also, by changing only the connection.

Code: Select all  Expand view  RUN

#include "fivewin.ch"

REQUEST DBFCDX

function Main()

   local oCn, oRs

   RDDSETDEFAULT( "DBFCDX" )
   oCn   := FW_OpenAdoConnection( "c:\fwh\samples\xbrtest.mdb" )
   FW_AdoImportFromDBF( oCn, "c:\fwh\samples\wwonders.dbf" )
   oRs   := FW_OpenRecordSet( oCn, "wwonders" )
   XBROWSER oRs TITLE "MSACCESS WWONDERS"
   oRs:Close()
   oCn:Close()

return nil
 

Same code can be tried with changing the connection to MySql server or Oracle server.