Wrox Programmer Forums
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 2nd, 2009, 01:08 PM
Friend of Wrox
Join Date: Oct 2004
Posts: 198
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via MSN to itHighway
Default Excel Data To Oracle

Need your help in uploading excel data to a table in oracle db.We cannot install any server component so pls give some suggestions.
Old January 2nd, 2009, 08:33 PM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

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:

Scroll down to "You can also open an Excel Spreadsheet using the JET OLE DB Provider"

So you can write something like this:
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 ...

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to pull excel data into oracle tables yogeshyl Oracle 1 January 9th, 2008 12:38 PM
How to Read Oracle Data without Oracle being insta badrinarayanang Oracle 1 October 6th, 2005 06:34 AM
How to coonect Excel to Oracle amjad_mahmood Excel VBA 2 August 4th, 2005 11:38 PM
Oracle to Excel Hadleyb Excel VBA 1 April 20th, 2005 11:26 AM
Excel to Oracle through ASP anandham Classic ASP Components 0 February 8th, 2005 07:41 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.