In December 2017 I presented at Tech17 and published a blog outlining several options for loading Excel data into APEX applications (see Options For Loading Excel Data Into APEX Applications).
One of the findings from my investigation was that there were limited options for loading vanilla Excel files automatically into the Oracle database. Most solutions require pre-converting to CSV format, or manual intervention.
With the release of Application Express 19.1, Oracle has introduced and tweaked some wizards that upload file data and introduced a new API, APEX_DATA_PARSER, that simplifies loading Excel data into APEX and the database.
APEX 19.1 Application Express Changes
Create Application From a File Wizard
Figure 1 – Create an Application From a File
Figure 2 – Upload a File Screen
The new APEX_DATA_PARSER package implements a file parser, which supports XML, JSON, CSV and XLSX files.
In order to replicate the file upload and reporting that I have used to illustrate the APEX_DATA_PARSER API, you will need to complete the following steps:
The main function of the API is the PARSE() function.
It allows parsing of XML, XLSX, CSV or JSON files and returns a table with rows of the APEX_T_PARSER_ROW type, with the following structure:
LINE_NUMBER, COL001, COL002, COL003, … COL299, COL300
It allows a maximum of 300 columns all returned in VARCHAR2 format (maximum length 4000 bytes).
Parsing is done on-the-fly with no data written to tables or collections. It requires the file contents to be a BLOB. This can come from any of multiple sources e.g. a file upload (as in this blog), an existing table, from using DBMS_LOB package or from a web service.
By parsing the file, reporting of the data associated with the parsed file is possible.
In my examples below, I have parsed an Excel XLSX file.
Figure 3 – Report SQL Query using Parse()
In this case, using a Classic report, the column headers derive from the Excel file first row, with the Heading Type attribute for the report set to ‘None’.
Figure 4 – Report Output using Parse()
There are a number of parameters available to use with the PARSE function, depending on whether the file parsed is XLSX, CSV, JSON or XML.
As well as the file content (BLOB), at least one of P_FILENAME, P_FILE_TYPE or P_FILE_PROFILE must be specified. My example uses P_FILENAME. For XLSX files, another particularly relevant parameter is P_XLSX_SHEET_NAME. This allows the name of the worksheet to be parsed. If excluded, as in my example, the function will use the first worksheet found.
As well as the PARSE() function, so other functions are included in the package. Some of which, but not all, are outlined below.
The GET_FILE_PROFILE function can be used to obtain metadata about the last parsed file, and returns a CLOB in JSON format. Details of the file depend on the format of the file but may include column names, XLSX worksheet name or CSV delimiter
Figure 5 – Get File Profile SQL
The result of the SQL above returns XLSX-specific meta-data in JSON format.
Figure 6 – Get File Profile Return JSON
The DISCOVERER function acts as a shortcut alternative to using the GET_FILE_PROFILE function after PARSE(), combining both elements and returning a CLOB in JSON format.
Figure 7 – Discoverer SQL
Similarly, the result of the SQL above returns XLSX-specific meta-data in JSON format.
Figure 8 – Discoverer Return JSON
Perhaps DISCOVERER is better than GET_FILE_PROFILE since it returns details of original column formats, rather than as VARCHAR2() as returned by PARSE().
The GET_COLUMNS function returns details about the columns in the parsed file’s profile as a table with type APEX_T_PARSER_COLUMNS. The function is used in conjunction with DISCOVERER() or GET_FILE_PROFILE(), which first computes the profile before GET_COLUMNS() returns the list of columns. Again, this works better in conjunction with DISCOVERER() to return the original Excel data types.
Figure 9 – Get Columns using Discoverer SQL
The report below illustrates the returned list of columns based on the SQL above
Figure 10 – Get Columns using Discoverer Report
The GET_XLSX_WORKSHEETS function returns details of all the worksheets in an XLSX workbook as table with type APEX_T_PARSER_WORKSHEETS. In this case, there is no pre-requisite to obtain the profile of the parsed file.
Figure 11- Get Worksheets SQL
Figure 12 – Get Worksheets Report
More information about the all the functions available with the APEX_DATA_PARSER API, and the parameters used with them, is documented in the Oracle APEX 19.1 Release API Reference.
With release 19.1, APEX has improved the functionality for loading external data into APEX applications or the database, especially with the new APEX_DATA_PARSER API package.
The PARSE() function, in particular, allows Excel .XLSX data (amongst other formats) to be parsed into a table type on the fly. This can greatly simplify the process of loading Excel data.
Once the data is in the returned table, this opens up the data held within it to standard database functionality.
It is just a small step to INSERT the data into target application tables, or to use joins or PL/SQL loops to otherwise process or validate the data.
For more information, get in touch with our experts to find out more about our APEX Services or book a meeting...
Author: Philip Ratcliffe
Job Title: Oracle APEX Development Consultant
Bio: Philip is a Development Consultant at DSP-Explorer. Building on considerable experience in development including using Oracle PL/SQL and supporting Oracle EBS, Philip is employing APEX to provide quality, bespoke software solutions to a range of organisations.