• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8629
  • Last Modified:

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

I need a stored procedure that will help me to read an excel sheet on the hard disk.
then i need to insert this data into a table..

i am using oracle 8i, i have heard this is possible, but don't have any clue how to work upon this.

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

Experts Please Help ....

Awaiting Eagerly

  • 6
  • 3
  • 3
  • +5
1 Solution
hartAuthor Commented:
Also what i was thinking was to send the path and file name of the file as in parameters to the stored procedure and rest should be done by the sp..

can this be done :-)

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:  
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:  
  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  
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, _  
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  
  r = r + 1  
End Sub  
-- or --  
' MS-Query API Sample Code  
Sub Button1_Click()  
Dim connstr As String  
Dim sqlstmt As String  
sqlstmt = "select * from emp"  
With ActiveSheet.QueryTables.Add(Connection:=connstr,  
Destination:=Range("A10"))ActiveSheet.QueryTables(1).Sql = sqlstmt  
ActiveSheet.QueryTables(1).BackgroundQuery = False  
End With
hartAuthor Commented:
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 ]

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Algo keep visiting :


A Question on the Same Subject.
hartAuthor Commented:
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 ???

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.

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?
hartAuthor Commented:
does anyone know about sql*loader and how to use it, i found out that even that can be used...

hartAuthor Commented:
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

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.
After reading your true requirement, the only thing that hits my mind is "Oracle interMedia".

User will upload Excel file, and the file will actually loaded into the database. This way, you will not need to rename the file as unique-id, rather a primary column for the uploaded files' table will solve the purpose.

Oracle interMedia has a lot of other powerful features. Just hang to it and you will find a true managable and scalable solution.

Best of luck.

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.

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
hartAuthor Commented:
thank you all...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 3
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now