DBF to SQL converter program

[pieter]

Hello,

I am making a program which reads all the dbf files in a folder, and then each dbf file will have corresponding sql table in a MYSQL database.

1: Choose a SQL database name
2: Choose a folder, to find all dbf's in it.
3: Make a MYSQL database with the name given by step 1.
4: Each dbf file becomes a table in the MYSQL database. each sql table has the name of the dbf file. (So a file customer.dbf becomes customer in MySQL)
5: import the data from each dbf file to the sql tables.

The code look like this:

Code: Select all  Expand view  RUN

FUNCTION CreateDatabaseIfNotYetExist( vardb )
   local oError
   
   ADOCONNECT oCn TO MYSQL SERVER localhost USER root PASSWORD <mysqlpassword>
 
   if oCn == nil
      MsgInfo( "Not connected" )
    else
      if oCn:State > 0
         MsgInfo( "open" )
         
      TRY
         oCn:Execute( "CREATE DATABASE " + vardb ) //This works, a database name which I chose, is created.
         MsgInfo( "created" )      
      CATCH oError
         MsgInfo( "The database already exists" )
      END          
         
      else
         MsgInfo( "not open" )
      endif
   endif        
 
   oCn:Close()
 
Return nil

Code: Select all  Expand view  RUN

FUNCTION ConnectWithDatabase( vardb )
   //ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE vardb USER root PASSWORD <mysqlpassword> // PASSWORD ...   (I tried to put the variable vardb in the Command, but as I already thought this did not work)
   oCn := FW_OpenAdoConnection( "MYSQL", "localhost",vardb, "root", "mysqlpassword" )   //I tried also this.
   
   if oCn == nil .or. oCn:State < 1
      MsgInfo( "Connect failed" )
      return nil
   endif        
 
   MsgInfo( "Connection Open" )
RETURN NIL
 

Code: Select all  Expand view  RUN

local oRs, oWnd, sqldatabasename := Space( 20 )
   DEFINE WINDOW oWnd TITLE "DBFTOSQLTOOL"
    
   @1.8, 3 SAY "sqldatabasename: " OF oWnd
   @2,15 GET sqldatabasename OF oWnd           
   
   ACTIVATE WINDOW oWnd     
           
   CreateDatabaseIfNotYetExist( sqldatabasename )
   ConnectWithDatabase( sqldatabasename )
   
   FW_AdoImportFromDBF( oCn, "C:\Pieter\Dev\import\customer.dbf")
   
   oRs = TRecSet():New():Open( "customer", oCn )
   
   if oRs:IsOpen()
 
      XBROWSER oRs // SETUP oBrw:lIncrFilter := .T.
               // SETUP oBrw:bEdit := { | oRec | MyEdit( oRec ) }
                 // SETUP oBrw:lIncrSearch := .T.
                   // SETUP oBrw:lWildSeek := .T.                  
 
      oRs:Close()
   else
      MsgAlert( "The recordset could not be opened" )
      MsgInfo( "Check that you have REQUEST DBFCDX" )      
   endif


 

Code: Select all  Expand view  RUN

How can I use the command ADOCONNECT or FW_OpenAdoConnection to connect with the database which I just created.
ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE vardb USER root PASSWORD <mysqlpassword> // PASSWORD ...   (I tried to put the variable vardb in the Command, but as I already thought this did not work)
oCn := FW_OpenAdoConnection( "MYSQL", "localhost",vardb, "root", "mysqlpassword" )  //I tried also this, but it did not work.

 

Anybody an idea.

Best regards,

Pieter

[dutch]

Dear Pieter,

You can find DBF2SQL in this forum, it automatic create MySql data from DBF File.

[pieter]

Hello Dutch:),

thanks for your reply.

I already could convert dbf to sql, but I want to automate it for a lot of dbf files and the option to choose you own sql database name.

If there exist a sql database name "DB1", I could write the following:

ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE DB1 USER root PASSWORD mysqlpassword //this works

but what if I have a variable vardb := "DB2" (Which I earlier have created with oCn:Execute( "CREATE DATABASE " + vardb ) )

and I want to connect:

ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE vardb USER root PASSWORD mysqlpassword //vardb should literally replaced by DB2 and then it should work also.

Somehow I can not get this work.

Maybe you know it, or where can I find a solution in a post.

[dutch]

could you try
&vardb
or
(vardb)

[pieter]

Yes:D, thank you very much.

&vardb works and (vardb) also.

Pieter

[pieter]

Hello,

Question:
How can I pick a directory or file from the Microsoft Windows Explorer easily? I think I have seen it in the fivewin sample directory in a .prg file once

Code: Select all  Expand view  RUN

  local oRs, oWnd, sqldatabasename := Space( 20 ), dbfPath := Space( 20 )
  DEFINE WINDOW oWnd TITLE "DBFTOSQLTOOL"
    
   @1.8, 3 SAY "sqldatabasename: " OF oWnd
   @2,15 GET sqldatabasename OF oWnd       
   @3.8, 3 SAY "Choose directorypath: " OF oWnd   //here I want acces to the file explorer, (so I can choose for example C:\Pieter\DATA\ or C:\Pieter\DATA\customer.dbf, maybe there is a .prg file which can do this easily.  
   @4, 15 GET dbfPath OF oWnd  //

   ACTIVATE WINDOW oWnd 
 

[James Bott]

Peiter,

You can use cGETDIR() to get a directory name.

And try cGETFILE() to get the filename. I'm not sure if it includes the path.

James

[pieter]

James,

Thank you!, I got now a lot of things working:D. I still got error-messages, however it seems that the data is converted well from dbf to sql

Part of the code:

Code: Select all  Expand view  RUN

dbfPath := aGETFILES("DataBase | *.dbf")

FOR x:=1 to len(dbfPath)   
       FW_AdoImportFromDBF( oCn, dbfPath[x])
Next
 

Pieter

[James Bott]

I am not familiar with aGetFiles(). How do you specify a path?

James

[dutch]

Pieter,

You should select folder and get dbfPath.

Code: Select all  Expand view  RUN

cFolder := cGetDir('Select Folder')
dbfPath = directory(cFolder +"\*.dbf")
FOR x:=1 to len(dbfPath)    
       FW_AdoImportFromDBF( oCn, dbfPath[x][1])
Next

James,

Thank you!, I got now a lot of things working:D. I still got error-messages, however it seems that the data is converted well from dbf to sql

Part of the code:

Code: Select all  Expand view  RUN

dbfPath := aGETFILES("DataBase | *.dbf")

FOR x:=1 to len(dbfPath)   
       FW_AdoImportFromDBF( oCn, dbfPath[x])
Next
 

Pieter

[pieter]

I am not familiar with aGetFiles(). How do you specify a path?

James,

When I searched for cGetDir() and cGetFile() (http://wiki.fivetechsoft.com/doku.php?i ... n_cgetfile) in a searchengine, I found also aGetFiles() http://wiki.fivetechsoft.com/doku.php?i ... _agetfiles. With aGetFiles() one can select multiple files in a chosen directory.

Pieter

James

[pieter]

Pieter,

You should select folder and get dbfPath.

Code: Select all  Expand view  RUN

cFolder := cGetDir('Select Folder')
dbfPath = directory(cFolder +"\*.dbf")
FOR x:=1 to len(dbfPath)    
       FW_AdoImportFromDBF( oCn, dbfPath[x][1])
Next

James,

Thank you!, I got now a lot of things working:D. I still got error-messages, however it seems that the data is converted well from dbf to sql

Part of the code:

Code: Select all  Expand view  RUN

dbfPath := aGETFILES("DataBase | *.dbf")

FOR x:=1 to len(dbfPath)   
       FW_AdoImportFromDBF( oCn, dbfPath[x])
Next
 

Pieter

Dutch,

Thanks for this solution, this solution you gave me is actually what I described in the beginning of the topic. (The solution that I made with aGetFiles() also works, here I have to select the files in a chosen directory)

Code: Select all  Expand view  RUN

cFolder := cGetDir('Select Folder')
dbfPath := directory(cFolder +"\*.dbf")
FOR x:=1 to len(dbfPath)    
       FW_AdoImportFromDBF( oCn, cFolder + "\" + dbfPath[x][1]) //I have added cFolder + "\" to get your code working.
Next

Pieter

[pieter]

I am searching now for a function which puts all subdirectory names of a chosen directory in a array.

Example:
directory: C:\Pieter\DBF2SQLtool\data

01
02
file1.dbf
file2.dbf

In directory 01 and 02 are also dbf files. which I can convert to sql with the code I already have.

Maybe somebody knows if this function to get all subdirectory names in a directory exist, or another solution?

Kind regards,

Pieter

[Antonio Linares]

Pieter,

http://www.fivetechsoft.com/harbour-docs/harbour-reference-guide.htm#ADir

[pieter]

Antonio,

thank you.

Code: Select all  Expand view  RUN

cFolder := cGetDir('Select Folder')
dbfPath := directory(cFolder +"\*.dbf")
//subdirectories := getSubdirectoryNames(C:\Pieter\DBFTOSQLTOOL\DATA) //I have invented this function myself.
MsgInfo(curDir())
MsgInfo(ADir("??")) //I tried this, to count all the subdirectories, 01 and 02, but it gives 0 and I thought it should be 2.

FOR x:=1 to len(dbfPath)    
       FW_AdoImportFromDBF( oCn, cFolder + "\" + dbfPath[x][1])
Next

I think I have to do something with cFileMask http://wiki.fivetechsoft.com/doku.php?i ... _cfilemask, but I don't know how to get directory names.

Pieter