Bulk data insert into mysql using excel VBA (source format csv)

'' To use this module you first need to download the mysql ODBC 3.51 driver and install.
'' Once Installation has been done you can use the below module in your VBA code.


const sTR_ServerName ="10.2.3.311"                                         '' edit the IP address
const sTR_DbName= "dbName"                                                  '' edit the DB name
const sTR_UserID="test"                                                              '' edit the mysql user ID
const sTR_PWD="pwd"                                                                '' edit the mysql pwd 

Sub ConOpen()
'' Create ADODB Connection object
Set AdodbCon = CreateObject("Adodb.Connection")

' Open ADODB connection using the above parameters
AdodbCon.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}" & _
";SERVER=" & sTR_ServerName & _
";DATABASE=" & sTR_DbName & _
";USER=" & sTR_UserID & _
";PASSWORD=" & sTR_PWD

'' Open adodb connection
AdodbCon.Open
End Sub

Sub Upload_To_DB(TblName)''''''''''''''''''' edit the table name

filePath="C:\Users\Desktop\Automation\Input_file\import.csv"                     '' edit the file path

'' Replacing \ slash to forward slash from the filepath
filePath = Replace(filePath, "\", "/")                 

If AdodbCon.State <> 1 Then
    MsgBox "Database connection error", vbOKOnly + vbInformation, "Connection Failed"
    Exit Sub
End If

' Importing csv file into mysql table
qRY_Sql = "LOAD DATA LOCAL INFILE '" & filePath & "' INTO TABLE " & TblName & " FIELDS TERMINATED BY ',' ENCLOSED BY '""' ESCAPED BY '' LINES TERMINATED BY '\r\n'"
AdodbCon.Execute qRY_Sql

End Sub