The Oracle APEX blog is your source for APEX news, technical tips and strategic direction
-
Application Express
August 3, 2018
Easy XLSX Parser: Just with SQL and PL/SQL
Uploading XLSX files is a very common requirement these days. Application Express at this time only supports uploading CSV files out of the box. The community provides quite a few blog postings and also plug-ins; most of these store the data into a collection, which makes it hard to parse an XLSX outside of an APEX context, e.g. in the background using a scheduler job.
This blog posting shows a method to parse XLSX files with only SQL and PL/SQL. The actual work will be done by a table function, thus no collections and no APEX session is required. Since a table function returns rows and columns, one can easily insert the results into a table, apply some transformation logic, push it into the background with a scheduler job or just display.
The XLSX format is based on XML: an XSLX file is actually a ZIP file (you can easily verify that by renaming to .zip and opening it) containing several XML files. To parse the data, we first have to extract the contents, look up the right XML files and parse these. For the latter task, the XMLTABLE SQL function comes in very handy.
The following example code creates an XLSX_PARSER package. It uses the APEX_ZIP package to unpack the XLSX file and the XMLTABLE SQL function in order to parse the XML. The PARSE function is implemented as a table function which returns the first 50 columns ("A" to "AX"). If you need more columns, you can easily extend the code accordingly; the upper limit is 1000 columns.
The package provides the following functions:
FUNCTION GET_WORKSHEETS RETURNS APEX_T_VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_XLSX_CONTENT BLOB IN DEFAULT P_XLSX_NAME VARCHAR2 IN DEFAULT FUNCTION PARSE RETURNS XLSX_TAB_T Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_XLSX_NAME VARCHAR2 IN DEFAULT P_XLSX_CONTENT BLOB IN DEFAULT P_WORKSHEET_NAME VARCHAR2 IN DEFAULT P_MAX_ROWS NUMBER IN DEFAULT
You can pass either the XLSX content as a BLOB or the name of an uploaded file. In the latter case, the package will look up the file name in the APEX_APPLICATION_TEMP_FILES table. A typical call sequence is as follows:
- Look up the worksheets contained in the XSLX file with XLSX_PARSER.GET_WORKSHEETS
- Retrieve the Worksheet data with XLSX_PARSER.PARSE.
Let's try this out. Create an APEX application with an empty page, then add the following elements to that page.
- Add a Region of Static HTML type.
- Add a File Browse item named PX_XLSX_FILE. Choose Table APEX_APPLICATION_TEMP_FILES as the Storage Type and keep the files until the End Of Session.
- Add a Button to Upload the file. Choose Submit Page as the button action.
Your page should now look as follows:
You can already upload a file, but nothing will happen. Next, we'll want to show a select list to pick one of the worksheets contained in the XLSX file.
- Add a Select List item named PX_WORKSHEET.
- Use SQL Query as the List Of Values Type and provide the following SQL query:
select column_value d, column_value r from table( xlsx_parser.get_worksheets( p_xlsx_name => :PX_XLSX_FILE ) )
- Set Display Extra Values to No, Display Null Value to Yes and use - Choose - as the Null Display Value.
- Finally add a Server-Side condition to only display the select list when the PX_XLSX_FILE item IS NOT NULL (when a file has actually been uploaded)
Now run your page again. After you have uploaded an XSLX file, your page should look as follows:
So we can now pick a worksheet. So far, so good. Time to actually do the job and extract data from the XLSX file. For now, we just want to display the data as a classic report. So create a Classic Report region and use the following SQL Query.
select * from table(
xlsx_parser.parse(
p_xlsx_name => :PX_XLSX_FILE,
p_worksheet_name => :PX_WORKSHEET ) );
Add the PX_WORKSHEET item to the Classic Reports Page Items to Submit attribute. Then add a Dynamic Action in order to refresh the report when a worksheet has been chosen in the Select List item.
- The dynamic action should fire on the Change event of the PX_WORKSHEET item
- As the TRUE action, choose Refresh of the Classic Report region you just have created.
You then should be able to do the following steps:
- Run the page and upload an XLSX file
- Pick a worksheet from the select list
- The Classic Report refreshes and shows the first 50 columns of worksheet data
And that's it ... the nice thing is that there are no limits to process the worksheet data ...
- You can simply create a table ...
create table worksheet_data as select col02 as first_name, col03 as last_name, col05 as country from table( xlsx_parser.parse( p_xlsx_name => :PX_XLSX_FILE, p_worksheet_name => :PX_WORKSHEET ) ); - You can apply a few transformations ...
insert into worksheet_data( select cast( col02 as varchar2(200) ) as first_name, cast( col03 as varchar2(200) ) as last_name, case col04 when 'Female' then 'F' when 'Male' then 'M' end as gender, cast( col05 as varchar2(200) ) as country, to_number( to_char( sysdate, 'YYYY') ) - to_number( col06 ) as birth_year from table( xlsx_parser.parse( p_xlsx_name => :PX_XLSX_FILE, p_worksheet_name => :PX_WORKSHEET ) ) where line# != 1) - You can also create a DBMS_SCHEDULER job to push the task into the background. Note that this job does not have access to APEX session state - so you need to store the BLOB into your own table, then.
Try the sample code out - once the table function returns data, there are no limits for further processing any more. You can do with the data whatever you want: from just displaying over loading into a table to executing complex processing in the background: Everything is possible.