Hi,
We are on 20.1 and using APEX_DATA_PARSER package to upload Excel files. Some of the excel files have about 100K-120K rows and files are about about 7-9MB in size and about 18-20 columns.
I have a page built along the lines described by Carsten in this blog.
https://blogs.oracle.com/apex/easy-xlsx-parser:-just-with-sql-and-plsql
When I click the Upload button after choosing an Excel file, it takes about 1-2 minutes to display the Worksheet LOV. And after I choose the worksheet from the LOV, it takes another 1-2 minutes to display the contents in worksheet data. During the 1-2 minutes the busy icon disappears and the users could potentially click around and complicate the problem.
I have 2 questions.
Is there anything I can do to reduce the time it takes to display the worksheets and to fetch the data.
Why is it taking the 1-2 minutes to display the list of worksheet - is it parsing the spreadsheet in this step as well?
I am using the following query to display the contents of the spreadsheet as a classic report.
select p.line_number,col001, col002, .. col015
from apex_application_temp_files f,
table(apex_data_parser.parse(
p_content => f.blob_content,
p_add_headers_row => 'Y',
p_skip_rows => 1, -- Titles
p_xlsx_sheet_name => :P3_XLSX_SHEET,
p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
p_file_name => f.filename )
) p
where f.name = :P3_XLSX_FILE
and p.col001 is not null
and p.line_number > 1
Regards,
Venkat