Loading data from Excel to Oracle

[Dr Sanjay]

Hi All,
I would like to load an Excel spreadsheet directly into a single table in
Oracle. Both the spreadsheet and the table have the same ordering of columns
and have compatable data.

I am aware that a way to achieve this would be to convert the .xls file into
a .csv file and then use sql loader. However, I am interested in doing this
in a quicker/easier way directly from Excel, eg. by adding in some
functionality and clicking on a new button for loading into the database.

We are all able to read from a database in Excel by importing external data.
Surely there is a way to change that data and write it back to the database
from Excel?
kind regards

 

[galimi]

Dr. Sanjay,

Following is some code that should help:

Public Function LoadSheetToDB(sSheetName As String, sTableName As String) As
Boolean

On Error GoTo Err_LoadSheetToDB

Dim rs As New ADODB.Recordset
Dim lStartRow As Long
Dim lEndRow As Long
Dim lStartCol As Long
Dim lEndCol As Long

Dim iRowIndex As Long
Dim iColIndex As Long

sShtName = sSheetName

With Sheets(sSheetName)
lStartCol = 1
lEndCol = .Range("Header").Columns.Count
lStartRow = 1
lEndRow = .Range("Data").Rows.Count
End With


rs.Open "SELECT * from " & sTableName, GetDBConnection, adOpenStatic,
adLockOptimistic
'MsgBox rs("cusip_id")

For iRowIndex = lStartRow To lEndRow
rs.AddNew
For iColIndex = lStartCol To lEndCol
With Sheets(sSheetName)
Debug.Print .Range("Header").Cells(1, iColIndex).Value
rs(.Range("Header").Cells(1, iColIndex).Value) =
..Range("Data").Cells(iRowIndex, iColIndex).Value
End With
Next

rs.Update
Next
rs.Close
LoadSheetToDB = True

Exit_LoadSheetToDB:
Set rs = Nothing
Exit Function

Err_LoadSheetToDB:
LoadSheetToDB = False
modErrorFunctions.WritetoErrorLogADOErrors "LoadSheetToDB",
GetDBConnection.Errors, MOD_NAME
Resume Exit_LoadSheetToDB
End Function

The key elements in this are the .AddNew & .Update methods. Everything else
will have to be modified to fit your circumstance.