0

I have a project where I have to send data from an Excel spreadsheet to an Oracle database.

Here is the code I am using but it is not working, I am getting errors in the strSQL statements at the second string statement. I am trying to use an Excel range name to Insert or Update into an Oracle database.

Const hostName = "host"
Const portNo = "port"
Const srvSID = "SID"
Const usrID = "username"
Const usrPwd = "password"

Sub ADOInsert()
Dim cnAdo As ADODB.Connection
Dim strConnString As String
Dim strSQL As String
Dim ws1 As Excel.Worksheet
'set the worksheet:
Set ws1 = ThisWorkbook.Worksheets("IMPDATA")
Dim Imprange As Range
Set Imprange = ws1.Range("a2:d6")

    strDriver = "Driver={Microsoft ODBC for Oracle};"
    strParams = "CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + hostName + ")(PORT=" + portNo + "))(CONNECT_DATA=(SID=" + srvSID + ")));"
    strUser = "UID=" + usrID + ";PWD=" + usrPwd + ";"

    strConnString = strDriver + strParams + strUser


    Set cnAdo = New ADODB.Connection
    cnAdo.Open strConnString

    '/ this is hardcoded for number of rows and columns.
    For lngRow = 1 To 10
        strSQL = "Insert Into TESTDATA1"'/ 
        strSQL = strSQL & "'" & Range(Imprange, 1).Value & "', "   '/ REQUEST_NUMBER
        strSQL = strSQL & "'" & Range(Imprange, 2).Value & "', "   '/ CID
        strSQL = strSQL & "'" & Range(Imprange, 3).Value & "', "   '/ SERVER_TYPE
        strSQL = strSQL & "'" & Range(Imprange, 4).Value & "' "    '/ COST
        cnAdo.Execute strSQL
    Next
    cnAdo.Close
    Set cnAdo = Nothing
End Sub
TallTed
  • 9,069
  • 2
  • 22
  • 37
hforte
  • 1
  • 3
  • 4
    Welcome to SO! We're more here to help with errors you're getting, and certainly not here to write for you. I suggest searching online for a guide and following it. We can help with any troubles you run into along the way. Please do also pass through this [Question Checklist](https://meta.stackoverflow.com/questions/260648/stack-overflow-question-checklist), which will help your questions get more valuable answers and attention. – ti7 Jan 28 '17 at 23:01

1 Answers1

2

The simplest way to go is to convert the Excel file to the .csv format, and then you can use the SQL loader creating your own control file, or else you can create an external table pointing to your .csv file. Otherwise you can parse your .csv file programmatically and load each row on your table using ODBC, JDBC, and so on.

TallTed
  • 9,069
  • 2
  • 22
  • 37
Saxon
  • 739
  • 3
  • 6