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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,141,937
Messages
5,709,423
Members
421,636
Latest member
kelseyacheson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top