"Export Dbase into MYSQL
Posted by: Gary Tripodi
Date: July 26, 2015 10:23PM
Date: July 26, 2015 10:23PM
I just spent quite a bit of time creating a SUB that would maintain field names and most types/formatting. This uses ADODB connections to gather and create database in mysql and loop through DBF resultset inserting values into the MYSQL database. As it is this will need modification on any date values within strVals. Works good for me so far. Heres the script
Sub ExportDBFtoMYSQL
'=================ADODB Constants==============
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adOpenKeyset = 1
Const adLockReadOnly = 1
Const adOpenForwardOnly = 0
'============MYSQL SERVER VALUES============
MYSQLSVR = "localhost"
MYSQLUSERNAME = "root"
MYSQLPWD = "mypwd"
MYSQLDB = "mrp"
MYSQLUSERNAME = "root"
MYSQLPORT = "3306"
'==================DBF VALUES=================
DBFPath = CurrentDocument.Path
DBFName = "parts"
DBFKey = "IN_HOUSE_S"
'Connect to DBF=================================
Set DBFconn=CreateObject("ADODB.Connection")
Set DBFRS = CreateObject("ADODB.Recordset")
DBFconn.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & DBFPath
DBFstrSQL = "SELECT * FROM " & DBFName
DBFRS.Open DBFstrSQL,DBFconn, 0,3
'BUILD MYSQL Col String==============================
i = 0
Do Until i = DBFRS.Fields.Count
If DBFRS.Fields(i).Type = 2 Then
StrCol = StrCol & DBFRS.Fields(i).Name &" SMALLINT(" & DBFRS.Fields(i).DefinedSize & ") DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 5 Then
StrCol = StrCol & DBFRS.Fields(i).Name &" Numeric(10,4) DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 200 Then
StrCol = StrCol & DBFRS.Fields(i).Name & " VARCHAR(" & DBFRS.Fields(i).DefinedSize & ") DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 11 Then
StrCol = StrCol & DBFRS.Fields(i).Name & " BIT(1) DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 133 Then
StrCol = StrCol & DBFRS.Fields(i).Name & " DATE DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 201 Then
StrCol = StrCol & DBFRS.Fields(i).Name & " MEDIUMTEXT, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
i=i+1
Loop
'Fix MYSQL RSCOL string==================================================
strRSCols = Left(strRSCols,Len(strRSCols)-1) & ")"
'Connect To MYSQL====================================
Set MYSQLConn = CreateObject("ADODB.Connection")
Set MYSQLRS = CreateObject("ADODB.Recordset")
MYSQLstrSQL = "SELECT * FROM " & DBFName
StrMYSQLConn ="Driver=MySQL ODBC 5.3 ANSI Driver;" &_
"SERVER=" & MYSQLSVR &";" &_
"UID=" & MYSQLUSERNAME &";" &_
"PASSWORD=" & MYSQLPWD &";" &_
"DATABASE=" & MYSQLDB & ";" &_
"PORT=" & MYSQLPORT
MYSQLConn.Open StrMYSQLConn
'BUILD MYSQL Create Table String=====================
MYSQLStrExe = "CREATE TABLE " & DBFName & " (" & StrCol & _
"PRIMARY KEY " & DBFName & "_idx1 (" & DBFKey & ")" &_
") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
'EXECUTE MYSQL Create Table====================
MYSQLconn.Execute MYSQLStrExe
'OPEN New MYSQL Table=====================
MYSQLRS.Open MYSQLstrSQL,MYSQLConn, 0,3
'Enter The Data=====================================
If DBFRS.EOF = False Then
SelInto = "INSERT IGNORE INTO " & MYSQLDB & "." & DBFName & "("
i = 0
Do Until DBFRS.EOF = True
strVals = ""
Do Until i = DBFRS.Fields.Count
strVals = strVals & |"| & DBFRS.Fields(i).Value & |"| & ","
i = i + 1
Loop
i = 0
'Print DBFRS.Fields("recid").Value
strVals = Left(strVals,Len(strVals)-1) & ")"
'Print strVals
'Print SelInto & strRSCols & " values" & "(" & strVals
MYSQLconn.Execute SelInto & strRSCols & " values" & "(" & strVals
DBFRS.MoveNext
Loop
End If
MYSQLRS.Close
Set MYSQLRS = Nothing
MYSQLConn.Close
Set MYSQLConn = Nothing
DBFRS.Close
Set DBFRS = Nothing
DBFconn.close
Set DBFconn = Nothing
End Sub
Sub ExportDBFtoMYSQL
'=================ADODB Constants==============
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adOpenKeyset = 1
Const adLockReadOnly = 1
Const adOpenForwardOnly = 0
'============MYSQL SERVER VALUES============
MYSQLSVR = "localhost"
MYSQLUSERNAME = "root"
MYSQLPWD = "mypwd"
MYSQLDB = "mrp"
MYSQLUSERNAME = "root"
MYSQLPORT = "3306"
'==================DBF VALUES=================
DBFPath = CurrentDocument.Path
DBFName = "parts"
DBFKey = "IN_HOUSE_S"
'Connect to DBF=================================
Set DBFconn=CreateObject("ADODB.Connection")
Set DBFRS = CreateObject("ADODB.Recordset")
DBFconn.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & DBFPath
DBFstrSQL = "SELECT * FROM " & DBFName
DBFRS.Open DBFstrSQL,DBFconn, 0,3
'BUILD MYSQL Col String==============================
i = 0
Do Until i = DBFRS.Fields.Count
If DBFRS.Fields(i).Type = 2 Then
StrCol = StrCol & DBFRS.Fields(i).Name &" SMALLINT(" & DBFRS.Fields(i).DefinedSize & ") DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 5 Then
StrCol = StrCol & DBFRS.Fields(i).Name &" Numeric(10,4) DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 200 Then
StrCol = StrCol & DBFRS.Fields(i).Name & " VARCHAR(" & DBFRS.Fields(i).DefinedSize & ") DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 11 Then
StrCol = StrCol & DBFRS.Fields(i).Name & " BIT(1) DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 133 Then
StrCol = StrCol & DBFRS.Fields(i).Name & " DATE DEFAULT NULL, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
If DBFRS.Fields(i).Type = 201 Then
StrCol = StrCol & DBFRS.Fields(i).Name & " MEDIUMTEXT, "
strRSCols = strRSCols & "`" & DBFRS.Fields(i).Name & "`" & ","
End If
i=i+1
Loop
'Fix MYSQL RSCOL string==================================================
strRSCols = Left(strRSCols,Len(strRSCols)-1) & ")"
'Connect To MYSQL====================================
Set MYSQLConn = CreateObject("ADODB.Connection")
Set MYSQLRS = CreateObject("ADODB.Recordset")
MYSQLstrSQL = "SELECT * FROM " & DBFName
StrMYSQLConn ="Driver=MySQL ODBC 5.3 ANSI Driver;" &_
"SERVER=" & MYSQLSVR &";" &_
"UID=" & MYSQLUSERNAME &";" &_
"PASSWORD=" & MYSQLPWD &";" &_
"DATABASE=" & MYSQLDB & ";" &_
"PORT=" & MYSQLPORT
MYSQLConn.Open StrMYSQLConn
'BUILD MYSQL Create Table String=====================
MYSQLStrExe = "CREATE TABLE " & DBFName & " (" & StrCol & _
"PRIMARY KEY " & DBFName & "_idx1 (" & DBFKey & ")" &_
") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
'EXECUTE MYSQL Create Table====================
MYSQLconn.Execute MYSQLStrExe
'OPEN New MYSQL Table=====================
MYSQLRS.Open MYSQLstrSQL,MYSQLConn, 0,3
'Enter The Data=====================================
If DBFRS.EOF = False Then
SelInto = "INSERT IGNORE INTO " & MYSQLDB & "." & DBFName & "("
i = 0
Do Until DBFRS.EOF = True
strVals = ""
Do Until i = DBFRS.Fields.Count
strVals = strVals & |"| & DBFRS.Fields(i).Value & |"| & ","
i = i + 1
Loop
i = 0
'Print DBFRS.Fields("recid").Value
strVals = Left(strVals,Len(strVals)-1) & ")"
'Print strVals
'Print SelInto & strRSCols & " values" & "(" & strVals
MYSQLconn.Execute SelInto & strRSCols & " values" & "(" & strVals
DBFRS.MoveNext
Loop
End If
MYSQLRS.Close
Set MYSQLRS = Nothing
MYSQLConn.Close
Set MYSQLConn = Nothing
DBFRS.Close
Set DBFRS = Nothing
DBFconn.close
Set DBFconn = Nothing
End Sub
Subject
Views
Written By
Posted
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.