Insert excel Data Into oracle database using OO4O

Have you ever had a need to take and excel spreadsheet and load its data into an oracle table. I must say I had this very need a few years back and came upon a small DLL that saved my butt.Welcome to my OO4O tutorial. There is going to be a prerequisite to this tutorial.

1- You must have a version or oracle install on your desktop. Now I understand oracle is expensive I wouldn’t suggest you go buying an oracle database. On the contrary you can download a miniature version of an oracle database from
here. It pretty much has all the belts and whistles that a full version has but much less space. It’s great for testing and tinkering if you don’t want to spoil a production database.

2-You obviously must have excel installed in on your computer to get this to work. A prior knowledge of VBA (visual Basic for applications) is helpful but not mandatory.

3-A little motivation.
The first thing we need to do is download something oracle refers to as ODAC it stands for Oracle Data Access Components. You can download these components from this oracles website here.

Once you have downloaded this navigate to your C:\app\user\product\ 11.1.0\client_1\bin Warning depending on where you choose to install your oracle instance you might end up with a different location then mine but it should be pretty simple to find the dll. Something else to look out for is the “11.1.0\client_1\” in my path above. This can all be different by the time you try this. Don’t be alarmed

Ok we now have everything we need to get started let’s get into some code. First off lets create a simple table. I use sql developer to perform database queries is a free tool from oracle but this is not mandatory. You can use toad or even eclipse with a plugin to the database that’s up to you.

CREATE TABLE X_CEL (FIELD1 VARCHAR2(10),
                    FIELD2 VARCHAR2(10));

 

1-Open and excel workbook. Your screen should look like this.

(Note: To make images bigger just click the image.)

excel workbook

Now click on the developer tab.

exel developer tab

Your screen should now look like so

Basic button
visual basic button

Click on the visual basic button

visual basic
Visual Basic arrow

Ok hopefully if you are still following this tutorial and you are not falling to sleep on me. If you are falling to sleep get up jump around and come back when you have a little energy… Ok  your back lets move on.

Create a module like suchcreating modules

Now click on tools and references

Adding a reference
Adding a reference

You will get a pop up that looks like such. Click on the Browse button.choose your references

Once you click on the browse button you get to navigate to reference.So navigate to C:\app\user\product\ 11.1.0\client_1\bin and choose oip11.tlb otherwise known as the oracle inProc Server and continue to choose OraOLEDB11.tlb,  and OO4OADDIN.DLL

reference dll

Houston you are now ready to start talking to oracle!!!

Let’s create a sub

Sub InsertTable()

Dim sid As String

Dim UserName As String

Dim pass As String

sid = "XE"

UserName = "Miguel83"

pass = "XXXX" '-- your password i'm not telling you mine.

'this will stop the screen from flickering when the sub is running

Application.ScreenUpdating = False

' create the oracle connection objects.

Set OBJSESSION = CreateObject("OracleInProcServer.XOraSession")

Set OBJDATABASE = OBJSESSION.OpenDatabase(sid, UserName & "/" & pass, 0&)

'Use the object method and property to add a parameter.

OBJDATABASE.Parameters.Add "field1", "", 1

LROWS = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count

For lngrow = 1 To LROWS

    MyColumnA = Range("A" & CStr(lngrow)).Value

If MyColumnA = "" Then Exit For

With OBJDATABASE

.Parameters("field1").Value = MyColumnA

.ExecuteSQL "insert into miguel83.X_CEL (FIELD1)" & _

            "values(:FIELD1)"

End With

    Next

        Application.ScreenUpdating = True

End Sub

macro run image

Put some values in column A and let her run. You should end up with a column full of A’s on your oracle table.

oracle table.

Leave a Reply

Your email address will not be published. Required fields are marked *