Importing Data from Excel to Oracle database

decci_7

New Member
Joined
Jul 17, 2006
Messages
22
General Information about the script

-- It fetches the data from excel sheet
-- The script checks the employee table on basis of empid field so as to prevent duplicate data in the table.
-- If the empid from excel already exists in the database, it overwrites/updates the information in the employee table for that particular record.
-- If the empid do not exist, it adds/appends a new record in the employee table.

Code:
Sub enterdata()

'Create and set login information variables

'Set Variables for Upload
Dim empid As String
Dim name As String
Dim role As String
Dim temp As String
Dim search As String

'Create and Set Session / Create Dynaset = Column Names
Dim Session As Object
Set Session = CreateObject("OracleInProcServer.XOraSession")
Dim OraDatabase As Object
Set OraDatabase = Session.OpenDatabase("database", "user/pass", 0&)
Dim oradynaset As Object
Set oradynaset = OraDatabase.DBCreateDynaset("SELECT employee_id,first_name,application_role FROM employee", 0&)
    
Range("A2").Select
  
        
    Do Until Selection.Value = ""
        empid = Selection.Value
        Dim oradynaset1 As Object
        Set oradynaset1 = OraDatabase.DBCreateDynaset("SELECT employee_id,first_name,application_role FROM employee where empid = '" + empid + "'", 0&)
        name = Selection.Offset(0, 1).Value
        role = Selection.Offset(0, 2).Value
      
        
        If (empid = oradynaset1.fields("empid").Value) Then
            oradynaset1.Edit
            oradynaset1.fields("employee_id").Value = empid
            oradynaset1.fields("first_name").Value = name
            oradynaset1.fields("application_role").Value = role
            oradynaset1.Update
            Selection.Offset(1, 0).Select
        Else
        oradynaset.AddNew
        oradynaset.fields("employee_id").Value = empid
        oradynaset.fields("first_name").Value = name
        oradynaset.fields("application_role").Value = role
        oradynaset.Update
        Selection.Offset(1, 0).Select
        End If
    Loop
End Sub
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,113,992
Messages
5,545,375
Members
410,679
Latest member
rolandbianco
Top