Solved

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

Posted on 2004-03-31
19
8,028 Views
Last Modified: 2007-12-19
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

Regards
Hart
0
Comment
Question by:hart
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +5
19 Comments
 
LVL 11

Author Comment

by:hart
ID: 10722178
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 :-)

Regards
Hart
0
 
LVL 48

Expert Comment

by:schwertner
ID: 10722215
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
0
 
LVL 11

Author Comment

by:hart
ID: 10722233
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:riazpk
ID: 10722262
Algo keep visiting :

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

A Question on the Same Subject.
0
 
LVL 11

Author Comment

by:hart
ID: 10722474
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
0
 
LVL 48

Expert Comment

by:schwertner
ID: 10722704
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"
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 10722797
check out http://www.oraxcel.com
It might help you. I believe it is quite helpful.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 10722802
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10723153
@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?
0
 
LVL 11

Author Comment

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

Regards
Hart
0
 
LVL 11

Author Comment

by:hart
ID: 10723268
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
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10723269
yes, but sqlloader is strictly working on the text file not binary files.

0
 
LVL 48

Expert Comment

by:schwertner
ID: 10723414
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.
0
 
LVL 2

Accepted Solution

by:
aabbas earned 500 total points
ID: 10723600
hart:
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.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10723630
@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.
0
 
LVL 2

Expert Comment

by:aabbas
ID: 10723631
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.
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 10723800
0
 
LVL 2

Expert Comment

by:alligatorsql
ID: 10725321
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
0
 
LVL 11

Author Comment

by:hart
ID: 10730283
thank you all...

Regards
Hart
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question