Read an Excel xlsx with PL/SQL
At the OTN SQL and PLSQL forum I promised to publish some code I use for a project I’ still working on. This code allows you to select the content from an Excel document
select * from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ) ) );
SHEET_NR SHEET_NAME ROW_NR COL_NR CELL CEL STRING_VAL NUMBER_VAL DATE_VAL
---------- ---------- ------ ------ ----- --- ---------- ---------- --------------------------
1 Mijn naam 1 1 A1 N 11
1 Mijn naam 1 2 B1 N 12
1 Mijn naam 1 3 C1 N 13
1 Mijn naam 2 1 A2 N 21
1 Mijn naam 2 2 B2 N 22
1 Mijn naam 2 3 C2 N 23
1 Mijn naam 3 1 A3 N 31
1 Mijn naam 3 2 B3 N 32
1 Mijn naam 3 3 C3 N 33
1 Mijn naam 4 4 D4 S D4
1 Mijn naam 6 2 B6 D
1 Mijn naam 7 2 B7 D
1 Mijn naam 8 2 B8 D
1 Mijn naam 9 2 B9 D
1 Mijn naam 10 2 B10 D
2 Sheet3 2 2 B2 S Test
2 Sheet3 3 3 C3 D 19-JAN-2013 20:17:00
2 Sheet3 4 1 A4 S Anton
18 rows selected.
So here it is
Anton
** Changelog:
** 2013-02-18 – Ralph Bieber
Handle cell type “str” to prevent ORA-06502
if cell content is a string calculated by formula,
then cell type is “str” instead of “s” and value is inside tag
** 2013-02-19 – Ralph Bieber
Add column formula in tp_one_cell record, to show, if value is calculated by formula
** 20-02-2013 – Anton Scheffer
Handle cell types ‘inlineStr’ and ‘e’ to prevent ORA-06502
** 2013-03-19 – Anton Scheffer
Support for formatted and empty strings
Handle columns per row to prevent ORA-31186: Document contains too many nodes
Excellent, but when I try *xls file, I cannot read * xls file. How I read *xls file in PL-SQL? is it possible?
Thanks