Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Loading data from Excel to Oracle
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Loading data from Excel to Oracle
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. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Dr Sanjay" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Getting Excel Data from One Sheet to Another.... | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |