Re: here is a way! Conversion between FoxPro .DBF and MySql Database

[Bill Kruchas]

Well I was looking a while ago for something to do this, but decided to write my own foxpro program to build a "mysqldump" compatible file to move the data, this seemed the most straight forward way for me (because of my experience). So here it is, hopefully it will save someone some time, if you have any questions feel free to email me.

NOTE: Well I previewed the post and the program looses the indentation, if anyone needs a better copy please email me and I'll send it to you.

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)