Well, it's not the BEST way to do it, but there's no reason you can't simply open a database connection to the Excel file and then open another connection to the Oracle DB and create your own (pretty simple) SQL statements to read one row from the sheet and insert it into the Oracle DB.
Has the advantage that you can reformat data, if needed, on the fly. (For example, you will get a date from Access as
3/13/2008 and if Oracle needs it as
2008-03-13 you can easily reformat it using VBScript code.)
You should be able to write a "Tool" in VBS that would do all of this automatically: Read a row from the spreadsheet, inspect the datatype of each field, transform each field as needed. Or maybe you read the schema of the Oracle table and drive the transformation to match that schema.
To treat an Excel spreadsheet *AS* a JET database, see here:
http://www.carlprothman.net/Default....orMicrosoftJet
Scroll down to "You can also open an Excel Spreadsheet using the JET OLE DB Provider"
So you can write something like this:
Code:
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("relative/path/to/xxx.xls") & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes""" "
SQL = "SELECT * FROM [Sheet1$]" ' yes, the square brackets and the $ are needed
Set RS = conn.Execute( SQL )
Do Until RS.EOF
... read one record ...
... tranform fields as needed...
... write one record to Oracle DB ...
RS.MoveNext
Loop
%>
Excel Data To Oracle
