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
7,801 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
  • 6
  • 3
  • 3
  • +5
19 Comments
 
LVL 11

Author Comment

by:hart
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
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
 
LVL 13

Expert Comment

by:riazpk
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
check out http://www.oraxcel.com
It might help you. I believe it is quite helpful.
0
 
LVL 13

Expert Comment

by:anand_2000v
Comment Utility
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
@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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Author Comment

by:hart
Comment Utility
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
Comment Utility
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
Comment Utility
yes, but sqlloader is strictly working on the text file not binary files.

0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
0
 
LVL 2

Expert Comment

by:alligatorsql
Comment Utility
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
Comment Utility
thank you all...

Regards
Hart
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now