Need a stored procedure to read data from an excel sheet and insert into a table - using oracle 8i

You have to do this from Excel:

You are looking for information on the methods available for connecting  
MS-Excel 97 to Oracle using ODBC.  
   
Search Words:  
=============  
   
MICROSOFT  
MSQUERY  
QUERY
 
Solution Description:  
=====================  
   
There are two main methods available for connecting from MS-Excel to Oracle  
using ODBC. The first is via MS-Query and the second is using VBA. Both these  
methods require that you have a working Oracle ODBC datasource defined in the  
ODBC administrator.  
   
Connecting to Oracle using MS-Query:  
------------------------------------  
Pros: - Automated so no coding is involved  
Cons: - Formatting layout is limited to the SQL query's format  
      - Requires the use of a file DSN which is not yet creatable via the  
Oracle ODBC driver  
   
Note: To create a manual Oracle File DSN that points to an existing Oracle  
System DSN, use Notepad to create a file with a .dsn extension (eg.  
myoracle.dsn) that has the following two lines in it's document:  
   
  [ODBC]  
  DSN=<MyDSN> -- where <MyDSN> is the name of an existing System DSN  
   
This type of data import is performed via the Excel menu bar. Follow these  
steps:  
   
1) Click on the DATA option on the menu bar  
2) Click on the GET EXTERNAL DATA option on the menu dropdown list  
3) Click on the CREATE NEW QUERY option to launch MS-Query  
4) Connect to Oracle using a File DSN as described above  
5) A Query Wizard will appear to allow you to select/format data  
   
   
Connecting to Oracle using MS-VBA:  
----------------------------------  
Pros: - Formatting layout is completely customizable  
Cons: - Involves coding and a knowledge of MS-Basic  
   
This type of data import is performed via an Excel macro. Follow these steps:  
   
1) Click on the TOOLS option on the menu bar  
2) Click on the CUSTOMIZE option on the menu dropdown list  
3) Check the FORMS option in the options list  
4) Create a button on the spreadsheet using the "Forms" toolbar  
5) This will create a default macro named Buttonx_Click  
6) In the Visual Basic editor, go to the MENU->TOOLS->REFERNECES and check MS  
RDO 2.0  
7) Copy the following code in the Buttonx_Click macro to finish the sample:  
   
' Rdo Sample Code  
Sub Button1_Click()  
   
Dim RdoEnvironment As RdoEnvironment  
Dim RdoDatabase As rdoConnection  
Dim RdoRecordset As rdoResultset  
   
Dim connstr As String  
Dim sqlstmt As String  
   
connstr = "DSN=ORA32;UID=SCOTT;PWD=TIGER;"  
sqlstmt = "select * from emp"  
   
Set RdoEnvironment = rdoEnvironments(0)  
Set RdoDatabase = RdoEnvironment.OpenConnection("", rdDriverNoPrompt, False, _  
  connstr)  
Set RdoRecordset = RdoDatabase.OpenResultset(sqlstmt, _  
  rdOpenDynamic, rdConcurRowver)  
   
r = 10  
c = 1  
While Not RdoRecordset.EOF  
  ActiveSheet.Cells(r, c + 0) = RdoRecordset("EMPNO").Value  
  ActiveSheet.Cells(r, c + 1) = RdoRecordset("ENAME").Value  
  ActiveSheet.Cells(r, c + 2) = RdoRecordset("JOB").Value  
  ActiveSheet.Cells(r, c + 3) = RdoRecordset("MGR").Value  
  ActiveSheet.Cells(r, c + 4) = RdoRecordset("HIREDATE").Value  
  ActiveSheet.Cells(r, c + 5) = RdoRecordset("SAL").Value  
  ActiveSheet.Cells(r, c + 6) = RdoRecordset("COMM").Value  
  ActiveSheet.Cells(r, c + 7) = RdoRecordset("DEPTNO").Value  
  RdoRecordset.MoveNext  
  r = r + 1  
Wend  
   
End Sub  
   
   
-- or --  
   
   
' MS-Query API Sample Code  
Sub Button1_Click()  
   
Dim connstr As String  
Dim sqlstmt As String  
   
connstr = "ODBC;DSN=ORA32;UID=SCOTT;PWD=TIGER;"  
sqlstmt = "select * from emp"  
   
With ActiveSheet.QueryTables.Add(Connection:=connstr,  
Destination:=Range("A10"))ActiveSheet.QueryTables(1).Sql = sqlstmt  
ActiveSheet.QueryTables(1).BackgroundQuery = False  
ActiveSheet.QueryTables(1).Refresh  
   
End With

schwertner : did u read my question sir ??

i can't create a dsn on the file
"PS: Also i cannot create a dsn on the excel sheet as this may differ w.r.t user."

is there some sysytem procedure which helps me to read data directly from the file [no dsn ]

Regards
Hart

Algo keep visiting :

http://www.experts-exchange.com/Databases/Oracle/Q_20938386.html

A Question on the Same Subject.

ok thanks so far but let me more clear, just tell me if this can be done or not...

if heterogenous service has to be used, then how do i go about it, is there any documentation on this part

if i can't create a dsn, can i use utl_file.fopen() and do something with it.

please give some snippets or codes that will read through an excel sheet and insert data.

thats all i am asking for. has no one ever done this ???

Regards
Hart

This is the only known way to do this.
The docs you need can be downloaded from the OTN in regard to the Oracle version you use.
The explanation of UTL_FILE is in the OTN doc "Supplied PL/SQL packages"

check out http://www.oraxcel.com
It might help you. I believe it is quite helpful.

also check out http://www.experts-exchange.com/Databases/Oracle/Q_10116702.html

@hart:

except for plsql which is limited in handling proprietary format, do you know JAVA?

we do this loading part from excel to oracle using JAVA...


is this acceptable solution?

does anyone know about sql*loader and how to use it, i found out that even that can be used...

Regards
Hart

the basic requirement was this...

1. user uploads an excel sheet...
2. it gets renamed to an uniqueid from th db..
3. now what i have to do is read this file insert into the database.
4. i am a coldfusion developer and i know how to read the data from the xls file using cf, but then i require a dsn for it.
5. the dsn can't be set as the files are dynamic..
6. so i thought if theres a way i could do this process using a stored procedure which takes in the file name and path and just runs a loop on it and inserts it into the db would be great.
7. i could also consider java, if java can run a loop on the file with out a dsn.
8. eventually what i would do is call a class file pass two parameters to it and the rest will be done by the java class.
9. i hope java doesn't require dsn for doing this


So here i am hoping for a concrete solution

Regards
Hart

yes, but sqlloader is strictly working on the text file not binary files.

The normal way if you use SQL*Loader is:
1. Create the relevant Oracle tables
2. Save the Excel sheet as CVS file.
3. Using the CVS file as input to SQL*Loader to place the Excel data in the Oracle table.

This method is the most used. You can find here more examples how to manage the SQL*Loader to do this.

@Hart:

seriously agree with aabbas.

I meant to recommend oracle intermedia to you in the first place, but I am afraid that it's too advanced for you to tackle ...

I don't mean to be rude, but for most of times, the poster here ask for just quick and dirty, one time solution...

Oracle intermedia allows you to load all types of documents to Oracle database,
such as MS word, excel, pdf , html, and provide a means to convert them into other formats....
and it also faciliate the mining and analysis of those documents.

OR if you get the Excel files of same columns (identical column names/types), and you intend not to use interMedia, you can create a utility, which will be scheduled to work on each upload (or may be periodically) to convert Excel file to CSV file (Comma Separated Values) and load the rows in certain table using SQL*Loader.

When you talk about bulk data loading, nothing is as easy as Oracle SQL*Loader.

Hope, it will through some light for you towards SOLUTION.

refer to this:

http://www.experts-exchange.com/Databases/Oracle/Q_20921053.html

Hello,

Just some questions: Do you have installed Excel on the system, where your database ist installed ?
Reading the excel file directly from a stored procedure is possoble with UTL_FILE ... that is true ... but what happens then ?
You get binary coded characters into a PL/SQL var. This Excel file is encrypted, so that you can not access XLS directly without EXCEL
itselfs ... do you know what I mean.
So ... If you have installed EXCEL on your database server system (for example an NT) - You are able to write a DLL extent function
to read the excel file and transport the cell values to you stored procedure. We have several examples to do that.
So, if you need help in creating such a DLL - pls let me know.

Hope that helps?

Best regards
Manfred Peter