Re: here is a way! Conversion between FoxPro .DBF and MySql Database
Posted by: naresh kumar
Date: July 12, 2007 07:13AM

Bill Kruchas Wrote:
-------------------------------------------------------
> 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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: here is a way! Conversion between FoxPro .DBF and MySql Database
8855
July 12, 2007 07:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.