Demo on how to use the data parser package on excel sheets

data_parser.sql

-- list files:
select * from apex_application_temp_files

-- read sheets:
select * from table(
    apex_data_parser.get_xlsx_worksheets(
        p_content => (select BLOB_CONTENT from apex_application_temp_files where id = :P5_SELECTED_FILE )
    )
)
where :P5_SELECTED_FILE is not null

-- discover sheets (dynamic content region):
declare
    l_file_row apex_application_temp_files%rowtype;
begin
    if :P5_SELECTED_FILE is null or :P5_SELECTED_SHEET is null then 
        return 'please select a file';
    end if;

    select * into l_file_row from apex_application_temp_files where id = :P5_SELECTED_FILE;

    return '<pre>' || APEX_DATA_PARSER.DISCOVER (
        p_content         => l_file_row.BLOB_CONTENT
    ,   p_file_name       => l_file_row.filename
    ,   p_xlsx_sheet_name => :P5_SELECTED_SHEET
    ) || '</pre>';
end;

-- get columns:
select * from table(
    apex_data_parser.get_columns(
       
        apex_data_parser.discover(
            p_content         => (select BLOB_CONTENT from apex_application_temp_files where id = :P5_SELECTED_FILE)
        ,   p_file_name       => (select filename from apex_application_temp_files where id = :P5_SELECTED_FILE)
        ,   p_xlsx_sheet_name => :P5_SELECTED_SHEET
        ) 
        
    )
) 
where :P5_SELECTED_FILE is not null
  and :P5_SELECTED_SHEET is not null
  
  
-- parse:
select line_number
     , col001
     , col002
     , col003
     , col004
     , col005
     , col006
     , col007
     , col008
     , col009
     , col010
     -- , col300
  from table(
    apex_data_parser.parse(
        p_content         => (select BLOB_CONTENT from apex_application_temp_files where id = :P5_SELECTED_FILE)
    ,   p_file_name       => (select filename from apex_application_temp_files where id = :P5_SELECTED_FILE)
    ,   p_xlsx_sheet_name => :P5_SELECTED_SHEET
    )
) 
where :P5_SELECTED_FILE is not null
  and :P5_SELECTED_SHEET is not null