Loading Excel into APEX 19.1

Loading Excel Into APEX Applications Update

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

• As with 18.1, initially the wizard takes you through the data load process; to upload a CSV, XLSX, XML or JSON file, or to copy and paste data. Then once data has loaded into a new table, there is the option to continue to the Create Application wizard or not.
• In 19.1 the data load element provides a new facility to drag/upload an Excel XLSX file, rather than needing to pre-convert to CSV format.

Figure 2 – Upload a File Screen

SQL Workshop

• The Data Workshop Load Data utility is now the same configuration as the data load element incorporated into the Create Application wizard.

Page Data Loading Wizard

• No significant change in 19.1 – the uploaded file, or copied and pasted data, still needs to be comma separated or tab delimited.

APEX_DATA_PARSER API

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:

1. Create an application page.
2. Add a File Browse page item.
3. Add a button to submit the page.
   • Either the file will be sent to the server in APEX_APPLICATION_TEMP_FILES table, or
   • Add a process to insert the details of the file into a custom table, e.g. UPLOADED_FILES (the option I took).
4. Add a classic report to query details of the file using the APEX_DATA_PARSER API

PARSE Function

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.

GET_FILE_PROFILE function

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

DISCOVERER Function

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().

GET_COLUMNS Function

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

GET_XLSX_WORKSHEETS Function

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.

Summary

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.