2002-07-25 19:53

'By: "cincoclavos" <cincoclavos@radar.com.ar>Sat, 20 Jul 2002 00:02:10 -0000
'a program to convert DBF into MySQL database, if you
'reformatted the code is posible convert DBF into CVS file.
' Oscar

'This program is feeware. Written by Francesco Guarnieri 2001
'(Guarnieri@Mclink.it).
'It was compiled with RQBasic (by William Yu). The author has no
'responsability for
'improper use of this program.

'Dbf2MySql is a console command to convert a .dbf file to a .sql file.
'The generated .sql file can be used to import data in to a MySql
'Database using the command: MySql.exe < SqlFile.sql

'The Memo fields are not converted.

'To generate the SqlFile type:

'Dbf2MySql <DatabaseName> <TableName> <DbfFile.dbf> <SqlFile.Sql> -<Options>

'Options:

'-c    create table
'-a    append records
'-d    drop table
'-cad  Drop Create and Append

'example:

'Dbf2Mysql Test MyTable MyTable.dbf MyFile.Sql -ca

'generate a file called MyFile.Sql that create a table Mytable into
'database Test using the command:

'Mysql < MyFile.Sql

'You can edit your MyFile.Sql for adding further commands ....


     $OPTIMIZE ON
     $APPTYPE console
     $TYPECHECK ON
     $INCLUDE "RapidQ.inc"
     $OPTION icon "c:\rq\DbfMySql.ico"
     DIM Db AS STRING
     DIM Tb AS STRING
     DIM Dbf AS STRING
     DIM Sql AS STRING
     DIM DbfFile AS QFILESTREAM
     DIM SqlFile AS QFILESTREAM
     DIM Str AS STRING
     DIM Opt AS STRING
     DIM C AS INTEGER
     DIM D AS INTEGER
     DIM NumFs AS INTEGER
     DIM LenFs AS INTEGER
     DIM Rec AS INTEGER


     PRINT "Dbf to MySql Converter ---- Written By Dr.Francesco Guarnieri"
     PRINT "           Version 1.1 ---- Free Distribution (2001)"
     PRINT

     IF COMMAND$(1)="" OR COMMAND$(1)="/?" OR COMMAND$(1)="?" THEN
      PRINT "Command Line Usage:"
      PRINT "Dbf2MySql <MySqlDB> <MySqlTB> <InputFile.dbf> <OutputFile.Sql> -<Option>"
      PRINT
      PRINT "                      Options -c    [Default] Create Table"
      PRINT "                              -ca   Create Table and Append Records"
      PRINT "                              -a    Append Records to Table"
      PRINT "                              -d    Drop Table"
      PRINT "                              -dca  Drop, Create Table Append Records"
      PRINT
      PRINT "Command: Mysql < OutputFile.sql"

      GOTO fine
     ELSE
      Db = LCASE$(COMMAND$(1))
      Tb = LCASE$(COMMAND$(2))
      Dbf = LCASE$(COMMAND$(3))
      Sql = LCASE$(COMMAND$(4))
      Opt = LCASE$(COMMAND$(5))
     END IF

'Verifica Comando
     IF FIELD$(Dbf,".",2) <> "dbf" THEN
      PRINT "Error: Invalid Parameters"
      GOTO Fine
     ELSE
      IF LEFT$(Sql,1)="-" THEN
       Opt = Sql
       Sql=FIELD$(Dbf,".",1)+ ".Sql"
      END IF
      IF Sql="" THEN
       Sql=FIELD$(Dbf,".",1)+ ".Sql"
      END IF
     END IF

     IF Opt="" THEN
      Opt="-c"
     END IF

     DbfFile.OPEN(Dbf,fmOpenRead)

     DIM ColNam(1 TO 255) AS STRING*10
     DIM ColLen(1 TO 255) AS INTEGER
     DIM ColTyp(1 TO 255) AS STRING*1
     DIM ColDec(1 TO 255) AS INTEGER


     C=1
     LenFs=0
     Rec = 0

     Str = DbfFile.ReadbinStr(32)

'Ciclo per prelevare i dati sul campo
     DO
      Str = DbfFile.ReadStr(10)
      IF ASC(LEFT$(Str,1))= 13 THEN
       EXIT DO
      END IF
      ColNam(c) = FIELD$(str,CHR$(0),1)
      DO
       Str = DbfFile.ReadBinStr(1)
      LOOP UNTIL ASC(str)>0
      ColTyp(c) = Str
      Str = DbfFile.ReadBinStr(4)
      ColLen(c) = ASC(DbfFile.ReadBinStr(1))
      ColDec(c) = ASC(DbfFile.ReadBinStr(1))
      Str = DbfFile.ReadBinStr(14)
      PRINT ColNam(c) ," ", ColTyp(c)," ", ColLen(c), LEFT$("   ",(4-LEN(STR$(ColLen(c))))),ColDec(c)
      LenFs=LenFs+ColLen(c)
      c=c+1
     LOOP UNTIL dbffile.eof
     PRINT
     NumFs = C-1

     PRINT "Total Fields: ", NumFs
     PRINT "Toltal Fields Lenght: ", LenFs
     PRINT

     SqlFile.OPEN(Sql,fmCreate)
     SqlFile.WriteLine("Use " + Db + ";")



     IF INSTR(0,Opt,"d")>0 THEN
'Drop Table
      SqlFile.WriteLine("DROP TABLE " + Tb + ";")

      PRINT "Adding Command to Drop Table ..."
     END IF

     IF INSTR(0,Opt,"c")=0 THEN GOTO Append
' Create Table

     C = 1

     SqlFile.WriteLine("CREATE TABLE " + Tb + " (")

     DO
      SqlFile.WriteStr(ColNam(c),LEN(ColNam(c)))

      SELECT CASE Coltyp(c)
      CASE  "C", "G"
       SqlFile.WriteStr(" VARCHAR(" + STR$(Collen(C)) + ") DEFAULT NULL",23+LEN(STR$(Collen(c))))
      CASE "D"
       SqlFile.WriteStr(" DATETIME DEFAULT NULL",22)
      CASE "L"
       SqlFile.WriteStr(" CHAR(1) DEFAULT NULL ",21)
      CASE "N" , "F"
       SqlFile.WriteStr(" DECIMAL(",9)
       Str= STR$(Collen(C)) + "," + STR$(Coldec(C))
       SqlFile.Writestr(Str + ") DEFAULT NULL",14+LEN(Str))
      CASE "M"
       SqlFile.WriteStr(" TEXT",5)
      END SELECT
      IF C < NumFs THEN
       SqlFile.writeline(",")
      ELSE
       SqlFile.writeline(");")
      END IF
      C = C+1
     LOOP UNTIL c=NumFs + 1


     PRINT "Adding Command to Create Table ....."
Append:

     IF INSTR(0,Opt,"a") THEN
      PRINT "Adding Commands to Append Records ......"
      DbfFile.position = DbfFile.Position - 8
      D = CSRLIN
      PCOPY 0,1
      DO
       SqlFile.WriteStr("INSERT INTO " + Tb + " VALUES ('",22+LEN(tb))
       FOR C = 1 TO NumFs
        Str = REPLACESUBSTR$(dbffile.readbinstr(ColLen(c)),"'"," ")
        Str = REPLACESUBSTR$(str,","," ")
        Str = REPLACESUBSTR$(str,CHR$(0)," ")
        SqlFile.WriteStr(Str,collen(c))
        IF c<numfs THEN
         SqlFile.writestr("','",3)
        ELSE
         SqlFile.writestr("'",1)
        END IF
       NEXT C
       SqlFile.WriteLine(");")
       str=dbffile.readbinstr(1)
       LOCATE 24,45
       PRINT "Working: ",INT(DbfFile.Position/DbfFile.Size *100),"%"
       Rec = Rec + 1
      LOOP UNTIL dbfFile.eof
      PCOPY 1,0
      LOCATE D
      PRINT "Done ......(" + STR$(Rec) + " records)"
     END IF

     dbffile.CLOSE
     SqlFile.CLOSE

Fine:

2002-07-25 19:53