Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure: Fetch Excel data through oracle
Stored Procedure: Fetch Excel data through oracle [message #210643] Thu, 21 December 2006 11:55 Go to next message
sudkool
Messages: 12
Registered: May 2006
Junior Member
HI,
I have this new requirment, where I need to read records from Excel sheet and then compare some of those values to Oracle tables values and design report in BO. Before I used to do it through BO.

I was just wondering, is there any way I can read data from Excel sheet (multi tab or single tab) and compare it with oracle 9i table data or store excel data into a cursor and then compare it?

Please advice.

Thank you in advance.
Re: Stored Procedure: Fetch Excel data through oracle [message #210682 is a reply to message #210643] Thu, 21 December 2006 14:35 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Save Excel data in a CSV file; load it into Oracle database using SQL*Loader or use it as an external table. Once it is in Oracle table, compare it to existing data.
Re: Stored Procedure: Fetch Excel data through oracle [message #210694 is a reply to message #210643] Thu, 21 December 2006 16:51 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
to access the excel xls file directly using oracle forms it is easy using OLE2. The only way that I can think of to access the data in standard sql would be to be on a windows database server where you have an ODBC link and a database link to the excel table.
Re: Stored Procedure: Fetch Excel data through oracle [message #210731 is a reply to message #210694] Fri, 22 December 2006 01:52 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
This is not easy nor straightforward, but I agree with Littlefoot: use a CSV and call that from Oracle. You will avoid a lot of troubles. But Bill B has also a point: An ODBC database link (HSODBC) is the only way to get to the Excel file (xls) directly. This means however that the name of the file should be fix. If you link to the file directly you can access each work sheet as a different table. You could use Access as an intermediate layer if the ODBC sampling generates flawed table descriptions.

MHE
Previous Topic: Sum of last 3 transactions
Next Topic: help with sql query using months_between
Goto Forum:
  


Current Time: Mon Dec 05 18:52:21 CST 2016

Total time taken to generate the page: 0.10663 seconds