OraFAQ Forum: SQL & PL/SQL » PlSql open read cell in excel file!!

Home » SQL & PL/SQL » SQL & PL/SQL » PlSql open read cell in excel file!! (Forms [32 Bit] Version 9.0.2.9.0 , oracle JInitiator: 1.3.1.9, WebUtil 1.0.2(Beta), window , IE 8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PlSql open read cell in excel file!! [message #609290]

Wed, 05 March 2014 02:47

meteo
Messages: 89
Registered: April 2010
Location: beirut

Member

I am searching for how to open file excel , read cell and fetch data cell into a variable in a procedure using PLSQL.

is DBMS or UTL_FILE package useful for this subject? if yes any one can gave me an example or a link to get a vision on how to use one of these package with excel file??

thank you.

Report message to a moderator

Re: PlSql open read cell in excel file!! [message #609291 is a reply to message #609290]

Wed, 05 March 2014 02:50

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You cannot read an Excel spreadsheet with any Oracle database procedure. You would have to instruct Excel to write the data out as a text file with a defined format (CSV is often used) and then read that as an external table.

Report message to a moderator

Re: PlSql open read cell in excel file!! [message #609292 is a reply to message #609291]

Wed, 05 March 2014 02:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Actually it is possible to see an Excel spreadsheet as an Oracle table using HSODBC driver.
A complete setup and demonstration including common errors has been described by Tak Tang at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

Edit: now it is https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

[Updated on: Thu, 17 February 2022 10:30]

Report message to a moderator

Re: PlSql open read cell in excel file!! [message #609293 is a reply to message #609292]

Wed, 05 March 2014 03:09

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Great post, Michel. I've explained once how to connect to SQL Server via HSODBC once, and I've actually accessed an Excel file through ODBC as well. You can indeed reed all sheets within an excel file as tables but the setup is not straight forward if I remember it correctly.

Edit: This is my explanation on HSODBC.

MHE

[Updated on: Wed, 05 March 2014 03:17]

Report message to a moderator

Re: PlSql open read cell in excel file!! [message #609321 is a reply to message #609292]

Wed, 05 March 2014 09:06

meteo
Messages: 89
Registered: April 2010
Location: beirut

Member

I write the flowing procedure to read an excel file:

-- P58_READ_XLS_FILE_DBMS

AS

v_metar varchar2(200);

result INTEGER;

i binary_integer;

filename varchar2(255);

BEGIN

filename := 'C:\Climate_File\FILE_TXT_DBMS1.xls';

result := ORDExcelSB.CreateExcelApplication('');

result := ORDExcelSB.OpenExcelFile(filename, 'Sheet1');

--Excluding the header row and reading the first 5 row

FOR n in 6 .. 10 LOOP

dbms_output.put_line(to_char(n));

v_metar := ORDExcelSB.GetDataStr('A' || to_char(n));

dbms_output.put_line(V_metar);

END LOOP;

result := ORDExcelSB.ExitExcel();

EXCEPTION

WHEN OTHERS THEN

result := ORDExcelSB.ExitExcel();

RAISE;

END;

when I execute the following procedure I m getting the following output:

Creating Excel application...
Invoking Workbooks...
Opening Excel file C:\Climate_File\FILE_TXT_DBMS1.xls ...
Opening WorkBook
Invoking WorkSheets..
Invoking WorkSheet
Opened
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
COM-0002: Invalid Token or no interface for token
Closing workbook and quitting...
Closing workbook...
Closing workbooks...
PL/SQL procedure successfully completed.

don't know why this doesn't write the content of the FILE_TXT_DBMS1.xls' what dos this output mean ?? is there ay error?? thank you.

the content of FILE_TXT_DBMS1.xls' must be :

Aviation Digital Data Service (ADDS)

Output produced by METARs form (1447 UTC 05 March 2014)

found at http://aviati
OLBA 051400Z 03006KT 350V070 CAVOK 20/08 Q1014 NOSIG
OLBA 051300Z 33011KT CAVOK 19/11 Q1015 NOSIG
OLBA 051200Z 03007KT 340V070 CAVOK 21/09 Q1014 NOSIG
OLBA 051100Z 33010KT CAVOK 20/11 Q1015 NOSIG
OLBA 051000Z 32008KT 9999 SKC 20/10 Q1016
OLBA 050900Z 32009KT 8000 SKC 19/11 Q1017 NOSIG
OLBA 050800Z 30005KT 240V340 9999 SKC 19/10 Q1017
OLBA 050700Z 01004KT 340V050 CAVOK 18/09 Q1017 NOSIG
OLBA 050600Z 11003KT 070V190 CAVOK 17/04 Q1016 NOSIG
OLBA 050500Z 10005KT CAVOK 15/03 Q1016 NOSIG
OLBA 050400Z 09005KT CAVOK 14/04 Q1016 NOSIG
OLBA 050300Z 09005KT CAVOK 14/05 Q1016 NOSIG
OLBA 050200Z 09005KT CAVOK 15/05 Q1016 NOSIG

Report message to a moderator

Re: PlSql open read cell in excel file!! [message #609323 is a reply to message #609321]

Wed, 05 March 2014 09:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>filename := 'C:\Climate_File\FILE_TXT_DBMS1.xls';
please confirm that the file above resides on the DB Server system; not on some remote client system.

Report message to a moderator

Re: PlSql open read cell in excel file!! [message #609335 is a reply to message #609323]

Wed, 05 March 2014 11:52

meteo
Messages: 89
Registered: April 2010
Location: beirut

Member

thank you Mr. I appreciate your support

I found the problem it was because an error in the sheet name parameter

result := ORDExcelSB.OpenExcelFile(filename, 'Sheet1'); must be

result := ORDExcelSB.OpenExcelFile(filename, 'FILE_TXT_DBMS1');

again thank you a lot Mr and execute me if I bother you .

Report message to a moderator