Re: here is a way! Conversion between FoxPro .DBF and MySql Database
Hello harsha
The first lines describe the way to run the program.
The rest are the program itself which will need to be cut and pasted into a foxpro program or "prg".
First, start foxpro.
Second, in the command window in foxpro type in the program name followed by the "stand alone" dbf file name without the extension (after the period).
Third, from the machine where you have MySql installed, from a command prompt,
type in the following command, replacing the "user", "password", and dbf-filename-no-ext.sql" with the values that are correct for you. (They are certainly different from the example, at least I hope so)
mysql -u user -ppassword database < dbf-filename-no-ext.sql
For your info this was supplied as a help for people that can program in foxpro and know mysql and how to restore a dump. This was never intended for "end users".
If you need further assitance let me know and I can certainly help off line.
Thanks Bill Kruchas
harsha kiran Wrote:
-------------------------------------------------------
> hi everyone i m having a foxpro application and i
> want to convert foxpro dbf files into mysql db
> files. I got this program which will do the above
> task.But i dont know how to use it, if it works or
> not.can any one help me in achieveing my task.
>
> The way to use it is:
> in foxpro
>
> do programname with dbf-filename-no-ext
>
> it will read the structure and create the
> mysqldump file
>
> to import into mySql
>
> mysql -u user -ppassword database <
> dbf-filename-no-ext.sql
>
> and bingo it's in there
>
>
>
> **start of program**
>
> PARAMETERS mdbf
>
> mdbffile = mdbf + ".dbf"
> * where you want mysqldump files to go
> mexportfile = "c:\sumcd\sql\" + mdbf + ".sql"
>
> * where dbf files are
> SET DEFAULT TO c:\sumcd
>
> IF !FILE(mdbffile)
> messagebox("not a valid file")
> CANCEL
> ENDIF
>
> set century on
> set hours to 24
>
> mjunk = 0
>
> *
> * open/create file to hold sql commands
> * target is a linux box so only lf "chr(10)" at
> end of line
> *
>
> *delete sql dump file if exists (important)
> DELETE FILE &mexportfile
>
> IF FILE(mexportfile) && Does file exist?
> mimporthandle = FOPEN(mexportfile,11) && If so,
> open write only
> ELSE
> mimporthandle = FCREATE(mexportfile) && If not,
> create it
> ENDIF
>
> IF mimporthandle < 1
> messagebox("could not open output file")
> CANCEL
> ENDIF
>
> use &mdbf
> set safety off
> *remove deleted records
> pack
> go top
>
> *
> * setup array to hold field attributes
> *
>
> mfield_cnt = AFIELDS(marray) && Create array
>
> *
> * Create text to build create table commands
> *
>
> mtext = "DROP TABLE IF EXISTS `" + mdbf + "`;" +
> chr(10)
> mjunk = FWRITE(mimporthandle, mtext)
>
> mtext = "CREATE TABLE IF NOT EXISTS `" + mdbf +
> "`(" + chr(10)
> mjunk = FWRITE(mimporthandle, mtext)
>
>
> FOR nCount = 1 TO mfield_cnt
> mtext = ""
> mtext = "`" + marray(nCount,1) + "` " && field
> name
>
> DO CASE
> CASE marray(nCount,2) = "C"
> mtext = mtext + "varchar(" +
> alltrim(str(marray(nCount,3))) + ") "
> CASE INLIST(marray(nCount,2), 'N', 'F', 'B')
> mtext = mtext + "decimal(" +
> alltrim(str(marray(nCount,3))) + "," +
> alltrim(str(marray(nCount,4))) + ") "
> CASE marray(nCount,2) = "D"
> mtext = mtext + "date "
> CASE marray(nCount,2) = "L"
> mtext = mtext + "tinyint(1) "
> CASE marray(nCount,2) = "I"
> mtext = mtext + "int(" +
> alltrim(str(marray(nCount,3))) + ") "
> CASE marray(nCount,2) = "T"
> mtext = mtext + "datetime "
> CASE marray(nCount,2) = "M"
> mtext = mtext + "text "
> OTHERWISE
> mtext = mtext + "unknown data type "
> ENDCASE
>
> if !marray(nCount, 5)
> mtext = mtext + "NOT NULL "
> endif
>
> if nCount < mfield_cnt
> mtext = mtext + "default '" + marray(nCount,9) +
> "'," + chr(10)
> else
> mtext = mtext + "default '" + marray(nCount,9) +
> "'" + chr(10)
> endif
>
> mjunk = FWRITE(mimporthandle, mtext)
>
> ENDFOR
>
> *
> * final line for create table section
> *
>
> mtext = ")TYPE=MyISAM COMMENT='" + mdbf + "';" +
> chr(10) + chr(10)
> mjunk = FWRITE(mimporthandle, mtext)
>
> go top
>
> *
> * start data loading section
> *
>
> do while !eof()
>
> *
> *beginning of line
> *
> mtext = "INSERT INTO " + mdbf + " VALUES ("
>
> FOR nCount = 1 TO mfield_cnt
>
> *
> *build values
> *
>
> if nCount > 1 && don't put a comma in the first
> time
> mtext = mtext + ", "
> endif
>
> DO CASE
> CASE marray(nCount,2) = "C" && characters
> if !isnull(&marray(nCount,1))
> mtext = mtext + "'" +
> strtran(alltrim(&marray(nCount,1)), "'", "\'") +
> "'"
> else
> mtext = mtext + "'NULL'"
> endif
> CASE INLIST(marray(nCount,2), 'N', 'F', 'B') &&
> numeric,float,double
> if !isnull(&marray(nCount,1))
> mlen = marray(nCount,3)
> mdec = marray(nCount,4)
> mtext = mtext + "'" +
> alltrim(str(&marray(nCount,1),mlen,mdec)) + "'"
> else
> mtext = mtext + "'NULL'"
> endif
> CASE marray(nCount,2) = "I" && Integer
> if !isnull(&marray(nCount,1))
> mtext = mtext + "'" +
> alltrim(str(&marray(nCount,1))) + "'"
> else
> mtext = mtext + "'NULL'"
> endif
> CASE marray(nCount,2) = "D" && date
> if !isnull(&marray(nCount,1))
> mtext = mtext + "'" +
> substr(dtos(&marray(nCount,1)), 1, 4)
> mtext = mtext + "-" +
> substr(dtos(&marray(nCount,1)), 5, 2)
> mtext = mtext + "-" +
> substr(dtos(&marray(nCount,1)), 7, 2) + "'"
> else
> mtext = mtext + "'NULL'"
> endif
> CASE marray(nCount,2) = "T" && date time
> if !isnull(&marray(nCount,1))
> mtext = mtext + "'" +
> substr(dtos(&marray(nCount,1)), 1, 4)
> mtext = mtext + "-" +
> substr(dtos(&marray(nCount,1)), 5, 2)
> mtext = mtext + "-" +
> substr(dtos(&marray(nCount,1)), 7, 2)
> mtext = mtext + " " +
> substr(ttoc(&marray(nCount,1)), 12, 8) + "'"
> else
> mtext = mtext + "'NULL'"
> endif
> CASE marray(nCount,2) = "L" && logical
> if !isnull(&marray(nCount,1))
> if (&marray(nCount,1))
> mtext = mtext + "'1'"
> else
> mtext = mtext + "'0'"
> endif
> else
> mtext = mtext + "'NULL'"
> endif
> CASE marray(nCount,2) = "M" && memo
> if !isnull(&marray(nCount,1))
> if memlines(&marray(nCount,1)) = 0
> mtext = mtext + "''"
> else
> STORE MEMLINES(&marray(nCount,1)) TO gnNumLines
> mtext = mtext + "'"
> FOR gnCount = 1 TO gnNumLines
> mtext = mtext +
> strtran(alltrim(MLINE(&marray(nCount,1),
> gnCount)), "'", "\'")
> NEXT
> mtext = mtext + "'"
> endif
> else
> mtext = mtext + "'NULL'"
> endif
> OTHERWISE
> mtext = mtext + "unknown data type "
> ENDCASE
> if isnull(mtext)
> set step on
> endif
> *
> *end of data load line
> *
> ENDFOR
>
> mtext = mtext + ");" + chr(10)
> mjunk = FWRITE(mimporthandle, mtext)
> skip
>
> enddo
>
> use
> fclose(mimporthandle)