export DBC/DBF to MSSQL or MYSQL

[astech]

Any body know how to do that, or any softwares can do that ?

thanks

 

[KDavie]

I think you can just use the upsizing wizard.

Tools\wizards\upsizing


-Kevin

 

[Mike Lewis]

Astech,

First, I assume that when you say MSSQL, you mean SQL Server?

As KDavie says, you can use the upsizing wizard, but that it not the ideal solution. The wizard supports SQL Server, but not MySQL. It also has many limitations: it does upsize all database objects correctly; it does not work on a re-usable basis (it is designed for one-off upsizing); and it is buggy.

For SQL Server, a better tool is Data Transformation Services, which you can access from the Enterprise Manager or the Import/Export shortcut on the Start menu. However, it is SQL Server-specific; it will not help you with MySQL.

For both databases, the ideal solution is to write your own upsizing in VFP, using SQL pass-through. It is not all that difficult. Essentially, you read the information from the VFP database and send it to the back end. Check the Help on remote acces for more details.

I have done several upsizing projects, and I have always found that it is more satisfatory to do it yourself than to to rely on one of the above tools.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[badukist]

Hi
You can use this program. I made it a long time ago to export our data into a Firebird database. It can be changed (one suggestion is Varchar for long VFP char columns) to suit your needs (column types mappings for MSSQL or MySQL)

PROCEDURE dbf2fb
PARAMETERS tcTable, tnConnHnd, tlAddData
*parameters:	tcTable		table name or full path
*				tnConnHnd	valid connection handle to Firebird database returned by SQLConnect()
*				tlAddData	transfer table data?
_t1=SECONDS()
LOCAL lcCreateTableSQL, lcCreateIndexSQL, lcDropTableSQL, lcInsertDataSQL, ;
		lcVFPTypes, lcFBTypes, lcOptions, lnFields, lnNDX
LOCAL ARRAY laVFPTypes[9], laFBTypes[9], laOptions[9]

USE (tcTable)

*types translation table
lcVFPTypes = 'C^D^L^M^N^F^I^B^Y^T'
lcFBTypes  = 'CHAR^DATE^SMALLINT^BLOB SUB_TYPE TEXT^NUMERIC^FLOAT^INTEGER^DOUBLE PRECISION^NUMERIC^TIMESTAMP'
lcOptions  = 'C^ ^ ^ ^N^N^ ^ ^N^ ^ '
lnFields = AFIELDS(laFlds)
ALINES(laVFPTypes,lcVFPTypes,.t.,'^')
ALINES(laFBTypes,lcFBTypes,.t.,'^')
ALINES(laOptions,lcOptions,.t.,'^')
tcTable = ALIAS()		
*build SQL statements
lcDropTableSQL = [DROP TABLE ]+tcTable				

SET TEXTMERGE on
SET TEXTMERGE TO memvar lcCreateTableSQL NOSHOW 

\\CREATE TABLE <<tcTable>> (
FOR i=1 TO lnFields
	IF i>1
		\\, 
	ENDIF
	lnFieldTypeRange = ASCAN(laVFPTypes, laFlds[i,2])
	IF !'getkey'$LOWER(laFlds[i,9])
		\\<<laFlds[i,1]>> <<laFBTypes[lnFieldTypeRange]>>
	ELSE
		\\<<laFlds[i,1]>> BIGINT 
	ENDIF 	
	IF laOptions[lnFieldTypeRange] = 'N'
		\\(<<laFlds[i,3]>>,<<laFlds[i,4]>>)
	ENDIF
	IF laOptions[lnFieldTypeRange] = 'C'
		\\(<<laFlds[i,3]>>)
	ENDIF
	IF laFlds[i,5] = .f. and !(laFlds[i,2] = 'D' OR laFlds[i,2]='T')
		\\ NOT NULL 
	ENDIF
NEXT
\\)
SET TEXTMERGE to
?SQLEXEC(tnConnHnd,lcDropTableSQL)
IF SQLEXEC(tnConnHnd,lcCreateTableSQL) < 0
	AERROR(laError)
	DISPLAY MEMORY LIKE laError
	?lcCreateTableSql
ENDIF
SQLCOMMIT(tnConnHnd)
*build and execute SQL statements to add data if third parameter is .T.
lcMemoConversion = ''
IF tlAddData=.t.
	z=0
	SET TEXTMERGE on
	SET TEXTMERGE TO memvar lcInsertDataSQL NOSHOW 
	\\INSERT INTO <<tcTable>> (
	FOR i=1 TO lnFields
		IF i>1
			\\, 
		ENDIF
		\\<<laFlds[i,1]>>
	NEXT
	\\) VALUES (
	FOR i=1 TO lnFields
		IF i>1
			\\, 
		ENDIF
		\\?m.<<laFlds[i,1]>>
	NEXT
	\\) 
	SET TEXTMERGE to
	SQLPREPARE(tnConnHnd,lcInsertDataSql)
	SCAN
		SCATTER MEMVAR 
		z=z+1
		FOR i=1 TO lnFields
			lcVarName = 'm.'+laFlds[i,1]
			DO case
			CASE TYPE(laFlds[i,1]) = 'L'	&&logical type, need conversion
				&lcVarName = IIF(EVALUATE(laFlds[i,1]) = .T.,1,0)
			CASE TYPE(laFlds[i,1]) = 'D' AND EMPTY(EVALUATE(laFlds[i,1]))	&&empty date, need conversion
				&lcVarName = '1900-01-01'
			CASE TYPE(laFlds[i,1]) = 'T' AND EMPTY(EVALUATE(laFlds[i,1]))	&&empty datetime, need conversion
				&lcVarName = '1900-01-01 00:00:00'
			CASE TYPE(laFlds[i,1]) = 'M' AND EMPTY(EVALUATE(laFlds[i,1]))	&&empty memo
				&lcVarName = ''
			ENDCASE
		NEXT
		IF SQLEXEC(tnConnHnd) < 1
			CLEAR
			AERROR(xx)
			DISPLAY MEMORY LIKE xx
			?'insert error'
			SUSPEND
		ENDIF
		IF MOD(z,1000) = 0
			SET MESSAGE to STR(z)
			SQLCOMMIT(tnConnHnd)
		endif
	ENDSCAN
ENDIF
CLEAR
?
?SECONDS()-_t1

*build and execute SQL statements to create indexes
lnNDX = ATAGINFO(laIndexes)
FOR i=1 TO lnNDX
	lcIndexName = ALIAS()+'_'+laIndexes[i,1]
	IF laIndexes[i,2] = 'PRIMARY'
		lcCreateIndexSQL = [ALTER TABLE ]+tcTable+[ ADD PRIMARY KEY (]+laIndexes[i,3]+[)]
	ELSE 
		lcCreateIndexSQL = [CREATE INDEX ]+tcTable+[_]+ALLTRIM(STR(I))+[ ON ]+tcTable+[ (]+LaIndexes[i,3]+[)]
	ENDIF
	?lcCreateIndexSQL
	
	SQLEXEC(tnConnHnd,lcCreateIndexSQL)
	SQLCOMMIT(tnConnHnd)
NEXT

RETURN

 

[tatin]

SEE

http://www.tek-tips.com/viewthread.cfm?qid=1040162&tmac=fav&val=2,1040162

 

[Eguy]

or look at ==> thread184-973130

Please let me know if the suggestion(s) I provide are helpful to you.
Sometimes you're the windshield... Sometimes you're the bug.

 

[Mike Lewis]

Astech,

You've had serveral suggestions. It would be helpful (and courteous) if you could let us know how useful these were. Or to at least acknowledge them.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)