Sample Code for Using getSheets Function

[ralmendaresa]

Good afternoon dear Marc, could you please give me an example of how to use the "getSheets" function to get the list of sheets of an Excel workbook?

[mbleron]

Hi,

It is a pipelined function so you have to call it from a cursor :

SQL> select column_value as sheet_name
  2  from table(ExcelTable.getSheets(ExcelTable.getFile('XL_DATA_DIR','multisheet.xlsx')))
  3  ;

SHEET_NAME
-------------------------------
Sheet2
Sheet1
Sheet3

[ralmendaresa]

Thank you very much, dear Marc. It already worked perfectly for me. Notice that now I have the problem that in an xlsx file in whose column there is data with custom format "hh:mm" when reading it the result gives me "31-DEC-99". As noted in the screenshot "TEST xlsx_COL2_format.jpg". I attach the xlsx file and the screenshots in case you could please help me know how to read the column correctly, carefully

TEST.xlsx

[mbleron]

That's another question, you should have opened a new issue.

Excel stores dates and times as a serial number internally, and ExcelTable can map that directly to the DATE data type (there's no support for TIME). Then you just have to format this date to your liking. Here's a working example :

select to_char(hora, 'HH24:MI') as hora
     , planta
     , evento
     , interruptor 
from table(
       ExcelTable.getRows(
         p_file  => ExcelTable.getFile('XL_DATA_DIR','TEST.xlsx')
       , p_sheet => 'Hoja1'
       , p_cols  => '"HORA" date NULL ON ERROR, "PLANTA" varchar2(256), "EVENTO" varchar2(2), "INTERRUPTOR" varchar2(3)'
       , p_range => 'B2'
       )
     )
where hora is not null
;