DBF to SQL converter program

[Antonio Linares]

Pieter,

Try this example:

Code: Select all  Expand view

#include "FiveWin.ch"

function Main()

   local aFiles := Directory( "*.*", "D" )
   local aSubDirs := {}
   
   AEval( aFiles, { | aFile | If( aFile[ 5 ] == "D", AAdd( aSubDirs, aFile ),) } ) 

   XBrowser( aSubDirs )

return nil

[pieter]

Antonio,

Thank you for this code:), it was what I needed.

Below is part of my code. I will probably rewrite this code, so that the code becomes more clear and reusable. (In that case I will make some functions)

Code: Select all  Expand view

   cFolder := cGetDir('Select Folder')
   dbfPath := directory(cFolder +"\*.dbf")
   
   FOR x:=1 to len(dbfPath)
       FW_AdoImportFromDBF( oCn, cFolder + "\" + dbfPath[x][1])
   Next
   
   aFiles := Directory( cFolder + "\*.*", "D" )
   aSubDirs := {}   
   AEval( aFiles, { | aFile | If( aFile[ 5 ] == "D", AAdd( aSubDirs, aFile ),) } )
     
   FOR y:=3 to len(aSubDirs)
           
             subFolder := cFolder + "\" + aSubDirs[y][1]
             dbfPath := directory(subFolder +"\*.dbf")
                         
             FOR z:=1 to len(dbfPath)    
               FW_AdoImportFromDBF( oCn, subFolder + "\" + dbfPath[z][1], subFolder + dbfPath[z][1] )
             Next
   Next

The code works, but there is one thing that I want to improve: With FW_AdoImportFromDBF, I want to make 01+dbffilename in a sql table, but without .dbf in the sql table
Now I get for example 01customer.dbf in the sql table (but I want 01customer). I think I can find a solution to cut the .dbf of the string (subFolder + dbfPath[z][1]) myself, but if anybody knows a quik solution (maybe a function) to do this easily, that would be great.

Pieter

[James Bott]

Peiter,

cFileNoExt( cFile )

See the FWH\Manual folder for help files. One of them has all the functions and it also has functions by category. See Functions by Category, File Management.

James

[pieter]

Hello James,

cFileNoExt( cFile ) is a very useful function:), it have saved me much time.

See the complete code below of the program.

Code: Select all  Expand view

#include "FiveWin.ch"
#include "adodef.ch"
 
REQUEST DBFCDX
 
static oCn
 
//----------------------------------------------------------------------------//
 
function MyTest()
   local oRs, oWnd, sqldatabasename := Space( 20 ), dbfPath := Space( 100 ), x, CFolder, aFiles, aSubDirs, subFolder, y, z
   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 )


   cFolder := cGetDir('Select Folder')
   dbfPath := directory(cFolder +"\*.dbf")
   FOR x:=1 to len(dbfPath) //convert dbf files in main directory.
       FW_AdoImportFromDBF( oCn, cFolder + "\" + dbfPath[x][1], "00" +  cFileNoExt(dbfPath[x][1]) )
   Next
 
   aFiles := Directory( cFolder + "\*.*", "D" )
   aSubDirs := {}   
   AEval( aFiles, { | aFile | If( aFile[ 5 ] == "D", AAdd( aSubDirs, aFile ),) } ) //get the subdirectory names.
     
   FOR y:=3 to len(aSubDirs) //convert dbf files in subdirectories
         subFolder := cFolder + "\" + aSubDirs[y][1]
         MsgInfo(cFolder)
         MsgInfo(subFolder) 
         dbfPath := directory(subFolder +"\*.dbf")
             FOR z:=1 to len(dbfPath)    
               FW_AdoImportFromDBF( oCn, subFolder + "\" + dbfPath[z][1], aSubDirs[y][1] + cFileNoExt( dbfPath[z][1] ) )
             Next
   Next

 
return nil
 
//----------------------------------------------------------------------------//

FUNCTION CloseDatabase()
   oCn:Close()
RETURN NIL  

FUNCTION ConnectWithDatabase( vardb )
   ADOCONNECT oCn TO MYSQL SERVER localhost DATABASE &vardb USER root PASSWORD password // PASSWORD ...
     
   if oCn == nil .or. oCn:State < 1
      MsgInfo( "Connect failed" )
      return nil
   endif        
 
   MsgInfo( "Connection Open" )
RETURN NIL
   
FUNCTION CreateDatabaseIfNotYetExist( vardb )
   local oError
   
   ADOCONNECT oCn TO MYSQL SERVER localhost USER root PASSWORD password
 
   if oCn == nil
      MsgInfo( "Not connected" )
    else
      if oCn:State > 0
         MsgInfo( "open" )
         
      TRY
         oCn:Execute( "CREATE DATABASE " + vardb )
         MsgInfo( "created" )      
      CATCH oError
         MsgInfo( "The database already exists" )
      END          
         
      else
         MsgInfo( "not open" )
      endif
   endif        
 
   oCn:Close()
 
Return nil
   

[James Bott]

Pieter,

For subfolers, you can try this function that Rao published a few years ago.

James

Code: Select all  Expand view

function SubFolders( cfolder )

   local aSubFolders := {}
   local n
   local aDir        := Directory( cFolder + '\*.*', 'D' )

   for n := 1 to Len( aDir )
      if aDir[ n ][ 5 ] == 'D' .and. Left( aDir[ n ][ 1 ], 1 ) != '.'
         AAdd( aSubFolders, aDir[ n ][ 1 ] )
      endif
   next

return aSubFolders
 

Source: http://forums.fivetechsupport.com/viewtopic.php?f=3&t=16138&p=83402&hilit=directory+list#p83402

[pieter]

James, Thank you, (I can use that code from Roa probably when I want to make my code better readable)

I have still another problem:

image hosting site no sign up

When FW_AdoImportFromDBF( oCn, cFolder + "\" + dbfPath[x][1], "00" + cFileNoExt(dbfPath[x][1]) ) is used, a sql table will be made, with also a column ID autoincrement. Some of my dbf files has also an Column ID. I think because of that the above error occured.

I thought of two options:
1) change the autoincrement Column ID of the SQL Table
2) Change the Column ID of the dbf files which will be converted.

I think Option 1 is the best. My question is, is there a way to change Column ID into for example SQL_ID? (with FW_AdoImportFromDBF)

Pieter

[James Bott]

Pieter,

That is a complicated one. Reinaldo has a few things to say about SQL auto-increment fields here:

viewtopic.php?f=3&t=30923&p=178201&hilit=sql#p178201

Personally, I think It is not good practice to use the same fieldname for ID in different files, especially if you are not using database objects. Unless you always refer to fields with their alias, you end up with "ID" referring to different files depending on the current workarea. It would be better to use CUSTID, PARTID, INVOICEID, etc.

However changing a fieldname in a legacy program is going to very difficult and tedious to debug.

If you are using database objects then it is not much of an issue because you are using oCust:id, oPart:id, and oInvoice:id, so the code is very clear and you don't have to worry about aliases.

You also are going to need the "standard" HBRECNO auto-increment field so the tables work with ADORDD. Maybe you can specify a different auto-increment fieldname other than ID.

I would be interested to hear what one of our SQL experts (for instance, Rao and Rick) has to say about this whole topic.

Regards,
James

[pieter]

James,

Thank you for the information. Does somebody know how to specify a different auto-increment fieldname other than ID for a sql table? Preferable with FW_AdoImportFromDBF. (for example SQL_ID or HBRECNO)

I also tried FW_AdoImportFromDBF( oCn, cFolder + "\" + dbfPath[x][1], "00" + cFileNoExt(dbfPath[x][1], "SQL_") ), but it does not work, none of the column names begin with "SQL_". (even if this works, it would not be an ideal solution, because actually I want to change only the auto-increment ID fieldname)

Pieter

[AHF]

James, Pieter,

You also are going to need the "standard" HBRECNO auto-increment field so the tables work with ADORDD. Maybe you can specify a different auto-increment fieldname other than ID.

You can work with ADORDD with any field name you want to be used as recno field just indicate it in:

Code: Select all  Expand view

SET ADO DEFAULT RECNO FIELD TO "ID"

If you want you might have different field names for each table to be used as recno just indicate it in:

Code: Select all  Expand view

SET ADO FIELDRECNO TABLES LIST TO {{"tablename1","HBRECNO"},{"tablename2","MYID"}}

If yo want to upload to any SQL you can do:

Code: Select all  Expand view

SELE 0
USE tablename ALIAS table VIA "DBFCDX"
SET INDEX TO  //if its auto open on
COPY TO sqltablename  // if rddstdefault not ADORDD place VIA "ADORDD"
USE sqltablename
BROWSE()
 

ADORDD has a bug and this will only works with SET ADO FORCE LOCK ON.
Ill post a new version with this and other minor corrections tomorrow.

This is usable for small tables ( < 10.000 records) as it takes sometime.
If you want you can do it faster copying to a csv file and then use statement (MySql) LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'.

Check Mysql doc or other engine doc to seek how you can do it this s the best and faster way to import big amounts of data.

Code: Select all  Expand view

SELE 0
USE tablename ALIAS table VIA "DBFCDX"
COPY TO sqltablename WHILE RECNO() < 1 //creates a empty structure in SQL to be used after by LOAD DATA
SET INDEX TO  //if its auto open on
COPY TO sqltablename  DELIMITED .... // if rddstdefault not ADORDD place VIA "ADORDD" gere are all records
hb_GetAdoConnection():Execute( "LOAD DATA LOCAL INFILE 'sqltablename' INTO TABLE "sqltablename" ......... )
USE sqltablename
BROWSE()

This should very fast even for millions of records. I didnt tried it yet.

[James Bott]

Antonio F,

You can work with ADORDD with any field name you want to be used as recno field just indicate it in:

SET ADO DEFAULT RECNO FIELD TO "ID"

Question. If the SQL database that you are using always adds an auto incrementing field called "ID," and you have a field named "ID" already, what might be the solution?

It's possible that the ID field in the DBF is not auto incrementing and even if it was, won't it get renumbered when it gets imported (as was mentioned by Reinaldo in a different thread).

I wonder if it is possible with MySQL to force it to not use ID as the auto incrementing fieldname? Otherwise it seems that Pieter is going to have to change the ID fieldname in his DBF before importing it; which means he will also have to change all his code to use the new fieldname.

Peiter, maybe you have already found a solution?

Well, OK, that was more than one question. I'm just wondering how to solve this situation.

Regards,
James

[AHF]

James, Pieter,

I think the easiest way might be:

1. Change the ID SQL field to WHATEVER_ID

Code: Select all  Expand view

"ALTER TABLE sqltable_name CHANGE old_column_name new_column_name"
 

2. Add new ID field EX.

Code: Select all  Expand view

"ALTER TABLE sqltable_name ADD COLUMN ID <DATATYPE>"
 

Now we have a AUTOINC field WHATEVER_ID and a ID with datatype you indicated.

If the ID field in the dbf file is autoinc I think FW_AdoImportFromDBF will try to import it as AUTOINC and you will get an error.

With ADORDD you can do (although it will be slow):

Code: Select all  Expand view

//ado sets must be initialized
......

hb_GetAdoConnection():Execute("ALTER TABLE sqltable_name CHANGE ID WHATEVER_ID")

//the data type for this field should match a compatible data type for the value stored in dbf
//if it is autoinc should be INT with the same length of the dbf.
hb_GetAdoConnection():Execute("ALTER TABLE sqltable_name ADD COLUMN ID VARCHAR(10)")

//set again field recno to WHATEVER_ID or it will not work.
SET ADO DEFAULT RECNO FIELD TO "WHATEVER_ID"

SELE 0
USE table_name ALIAS "DBF" VIA "DBFCDX"

SELE 0
USE sqltable_name ALIAS "SQL"  //assuming adordd its default rdd

SELE DBF

DO WHILE !EOF()
      sql->(dbappend())
      for n := 1 to dbf->( fcount() )
           sql->( fieldput( sql->( fieldpos( dbf->( fieldname( n ) ), dbf->( fieldget( n ) )    ) )

     next

    DBSKIP()
ENDDO

SELE SQL

BROWSE()

 

I didn't try it but it should work.

Mysql will not consider ID as autoinc field if you defined for it a different datatype.
I think FW_AdoImportFromDBF its doing that auto because the ID field in the DBF its autoinc.

Remember this will take sometime to execute with thousands of records.

Did you check LOAD DATA LOCAL INFILE use?
You can even change field types , exclude fields while importing it and its very fast.
In this case you would use COPY TO .... DELIMITED and then use that file to build the LOAD DATA LOCAL INIFILE statement.

Hope this will help.

[pieter]

Antonio F and James, both really thank you:D.

What James is writing in his post above was exacly my question. The solution that I want to implement, is to change the following code in adofuncs.prg in function FW_AdoCreateTableSQL( cTable, aCols, oCn, lAddAutoInc ).

Code: Select all  Expand view

if lAddAutoInc
      if aCols[ 1, 2 ] == '+'
         lAddAutoInc := .t.
      else
         AIns( aCols, 1, { "ID", '+', 10, 0 }, .t. )
      endif
   endif

That means that I will change the source code of adocuncs.prg. (I have have not done this yet).

Antonio, I read now you latest post, and I think you have almost the same solution (changing the auto-incrementfield of sql)

Regards,

Pieter

[James Bott]

Peiter,

The issue I am still concerned about is that when your ID field in the DBF is imported to an auto increment field in the SQL table, then it is going to be renumbered starting at 1 and not skipping any numbers up to the maximum number of records. If your DBF is numbered the same way then you are OK, but if it is not then you are going to have trouble.

The issues that Reinaldo discussed concern me and lead me to believe that auto increment fields should only be used for table "housekeeping" and not for primary keys. They seem best used for recno() type functions.

James

[nageswaragunupudi]

The code works, but there is one thing that I want to improve: With FW_AdoImportFromDBF, I want to make 01+dbffilename in a sql table, but without .dbf in the sql table

You have to include ".dbf" and with full/relative path when calling the function FW_AdoImportFromDBF. The function automatically removes ".dbf" while creating the table.

You can also specify a totally different table name in the 3rd parameter.

Examples:
FW_AdoImportFromDBF( oCn, "c:\fwh\samples\customer.dbf" ) --> Creates table with name "customer"

Sometimes we may have some DBFs whose names conflict with reserved words of SQL database. Though it is possible to have tables with such names by properly escaping the names each time we use, it is always safe and *wise* not to use reserved words as table names or field names.

In such cases we can give a totally different table name to be created.

FW_AdoImportFromDBF( oCn, "c:\myfolder\state.dbf", "states" )

[nageswaragunupudi]

Next let us pay attention to field names. It is likely that some of the field names in some of our DBFs conflict with reserved words of the SQL database. It is very difficult for us to review every field name and change only those which conflict.

An easier way is to prefix all column names with a character or some characters while exporting so that none of them conflict with any keyword.

For example, if we prefix "F" to all field names:
FW_AdoImportFromDBF( oCn, "c:\myfolder\state.dbf", "states", "F" )
Then if the field names in state.dbf are "CODE","NAME", the column names created in states table are "ID","FCODE","FNAME".

Also if we use hungarian notation, we can easily identify a field variable.
if nVar, cVar, lVar denote numeric, character and logical variables, fName indicates a field variable.

Incidentally, this also avoids conflict with "ID".